/*============================================================================
  File:     DB2Mirror.sql

  Summary:  Setup the database for Mirroring.

  Date:     July 2005

  SQL Server Version: 9.00.1187.07 (IDW15)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- Initial Database Setup to create the original database to mirror
-- Note: this restore is not necessary to for mirroring, only for original
-- setup.

RESTORE DATABASE $(DB2Mirror)
          FROM DISK = 'C:\SQLTools\CreditBackup80.BAK'
WITH MOVE N'CreditData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB2MirrorData.mdf'
        , MOVE N'CreditLog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB2MirrorLog.ldf'
        , REPLACE
GO

USE $(DB2Mirror)
GO

sp_dbcmptlevel $(DB2Mirror), 90
GO

-- Backups are made to a local SQLTools directory. Comment out the 
-- following line and make sure you are in SQLCMD mode:
-- !!mkdir C:\SQLTools
go

:SETVAR PrincipalServer SQLIDW15HA\SQLDev01
:SETVAR MirrorServer SQLIDW15HA\SQLDev02
:SETVAR WitnessServer SQLIDW15HA\SQLExpress
:SETVAR DB2Mirror CreditMirroring

---- Begin cleanup...

USE master
GO

select * from sys.database_mirroring_endpoints
select * from sys.endpoints
select * from sys.tcp_endpoints
GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(DB2Mirror)
    SET PARTNER OFF
GO

DROP ENDPOINT Mirroring
go

DROP DATABASE $(DB2Mirror)
go

:CONNECT $(MirrorServer)

DROP ENDPOINT Mirroring
GO

ALTER DATABASE $(DB2Mirror)
    SET PARTNER OFF
GO

DROP DATABASE $(DB2Mirror)
go

:CONNECT $(WitnessServer)

DROP ENDPOINT Mirroring
go

---- End cleanup...


:CONNECT $(PrincipalServer)

-- Mirroring ONLY supports the FULL Recovery Model
ALTER DATABASE $(DB2Mirror)
	SET RECOVERY FULL
go

USE $(DB2Mirror)
go

CREATE TABLE TestTable
(
	TestTableID		int			IDENTITY,
	RowInserted		datetime	CONSTRAINT TestTableRowInsertedDFLT	DEFAULT getdate(),
	Junk			char(1000)	CONSTRAINT TestTableJunkDFLT	DEFAULT 'This is junk.'
)
go

SET NOCOUNT ON
go

DECLARE @StopTime	datetime
SELECT @StopTime = dateadd(ss, 10, getdate())
WHILE getdate() < @StopTime
	INSERT TestTable DEFAULT VALUES
go
 
CREATE ENDPOINT Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5091) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT $(MirrorServer)

CREATE ENDPOINT Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5092) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT $(WitnessServer)

CREATE ENDPOINT Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5090) 
    FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO

:CONNECT $(PrincipalServer)

BACKUP DATABASE $(DB2Mirror)
TO DISK = 'C:\SQLTools\$(DB2Mirror).bak'
WITH INIT
GO

USE $(DB2Mirror)
go

SET NOCOUNT ON
go

DECLARE @StopTime	datetime
SELECT @StopTime = dateadd(ss, 10, getdate())
WHILE getdate() < @StopTime
	INSERT TestTable DEFAULT VALUES
go

:CONNECT $(MirrorServer)

RESTORE DATABASE $(DB2Mirror)
          FROM DISK = 'C:\SQLTools\$(DB2Mirror).bak'
WITH MOVE N'CreditData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\$(DB2Mirror)Data.mdf'
        , MOVE N'CreditLog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\$(DB2Mirror)Log.ldf'
        , NORECOVERY, REPLACE
GO

:CONNECT $(PrincipalServer)
SELECT  DATABASEPROPERTYEX(N'$(DB2Mirror)', N'Status') -- Returns ONLINE
SELECT	db_name(sd.[database_id]) AS [Database Name], 
		sd.mirroring_guid, 
		sd.mirroring_state, 
		sd.mirroring_state_desc, 
		sd.mirroring_partner_name, 
		sd.mirroring_witness_name, 
		sd.mirroring_witness_state, 
		sd.mirroring_witness_state_desc, 
		sd.mirroring_role, 
		sd.mirroring_role_desc, 
		sd.mirroring_role_sequence, 
		sd.mirroring_safety_level, 
		sd.mirroring_safety_level_desc, 
		sd.mirroring_safety_sequence, 
		sd.mirroring_failover_lsn 
FROM	sys.database_mirroring AS sd
WHERE	sd.[database_id] = db_id(N'$(DB2Mirror)')
go

:CONNECT $(MirrorServer)
SELECT  DATABASEPROPERTYEX(N'$(DB2Mirror)', N'Status') -- Returns RESTORING
SELECT	db_name(sd.[database_id]) AS [Database Name], 
		sd.mirroring_guid, 
		sd.mirroring_state, 
		sd.mirroring_state_desc, 
		sd.mirroring_partner_name, 
		sd.mirroring_witness_name, 
		sd.mirroring_witness_state, 
		sd.mirroring_witness_state_desc, 
		sd.mirroring_role, 
		sd.mirroring_role_desc, 
		sd.mirroring_role_sequence, 
		sd.mirroring_safety_level, 
		sd.mirroring_safety_level_desc, 
		sd.mirroring_safety_sequence, 
		sd.mirroring_failover_lsn 
FROM	sys.database_mirroring AS sd
WHERE	sd.[database_id] = db_id(N'$(DB2Mirror)')
go

:CONNECT $(MirrorServer)

ALTER DATABASE $(DB2Mirror)
    SET PARTNER = 'TCP://SQLIDW15HA:5091'
--	SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5091'
GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(DB2Mirror)
	SET PARTNER = 'TCP://SQLIDW15HA:5092'
--    SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5092'
GO

ALTER DATABASE $(DB2Mirror)
	SET WITNESS = 'TCP://SQLIDW15HA:5090'
--	SET WITNESS = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5090'
GO

SELECT	db_name(sd.[database_id]) AS [Database Name], 
		sd.mirroring_guid, 
		sd.mirroring_state, 
		sd.mirroring_state_desc, 
		sd.mirroring_partner_name, 
		sd.mirroring_witness_name, 
		sd.mirroring_witness_state, 
		sd.mirroring_witness_state_desc, 
		sd.mirroring_role, 
		sd.mirroring_role_desc, 
		sd.mirroring_role_sequence, 
		sd.mirroring_safety_level, 
		sd.mirroring_safety_level_desc, 
		sd.mirroring_safety_sequence, 
		sd.mirroring_failover_lsn 
FROM	sys.database_mirroring AS sd
WHERE	sd.[database_id] = db_id(N'$(DB2Mirror)')

---- failover when in sync
--ALTER DATABASE $(DB2Mirror)
--	SET PARTNER FAILOVER
--go
--
---- force failover with potential for data loss
--ALTER DATABASE $(DB2Mirror)
--	SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS 
