/*============================================================================
  File:     MirroringCredit.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.
============================================================================*/

---- Begin cleanup...
--
--USE master
--GO
--
--select * from sys.database_mirroring_endpoints
--select * from sys.endpoints
--select * from sys.tcp_endpoints
--GO
--
--:CONNECT SQLIDW15HA\SQLDev01
--
--ALTER DATABASE MirroringCredit
--    SET PARTNER OFF
--GO
--
--DROP ENDPOINT Mirroring
--go
--
--DROP DATABASE MirroringCredit
--go
--
--:CONNECT SQLIDW15HA\SQLDev02
--
--DROP ENDPOINT Mirroring
--GO
--
--ALTER DATABASE MirroringCredit
--    SET PARTNER OFF
--GO
--
--DROP DATABASE MirroringCredit
--go
--
--:CONNECT SQLIDW15HA\SQLExpress
--
--DROP ENDPOINT Mirroring
--go
--
---- End cleanup...


:CONNECT SQLIDW15HA\SQLDev01

RESTORE DATABASE MirroringCredit
          FROM DISK = 'C:\SQLTools\CreditBackup80.BAK'
WITH MOVE N'CreditData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MirroringCreditData.mdf'
        , MOVE N'CreditLog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MirroringCreditLog.ldf'
        , REPLACE
GO

USE MirroringCredit
GO

sp_dbcmptlevel MirroringCredit, 90
GO

ALTER DATABASE MirroringCredit
	SET RECOVERY FULL
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

-- Doing these next two steps just to get a backup of the database and the log
-- performed. Won't keep these. Will do one more right before setting up mirroring.
--BACKUP DATABASE MirroringCredit
--TO DISK = 'C:\SQLTools\MirroringCredit.bak'
--WITH INIT
--go
--
--BACKUP LOG MirroringCredit 
--TO DISK = 'C:\SQLTools\MirroringCredit.bak'
--WITH INIT
--go
 
CREATE ENDPOINT Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5091) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT SQLIDW15HA\SQLDev02

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

:CONNECT SQLIDW15HA\SQLExpress

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

:CONNECT SQLIDW15HA\SQLDev01

BACKUP DATABASE MirroringCredit
TO DISK = 'C:\SQLTools\MirroringCredit.bak'
WITH INIT
GO

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

:CONNECT SQLIDW15HA\SQLDev02

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

:CONNECT SQLIDW15HA\SQLDev01
SELECT  DATABASEPROPERTYEX(N'MirroringCredit', 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'MirroringCredit')
go

:CONNECT SQLIDW15HA\SQLDev02
SELECT  DATABASEPROPERTYEX(N'MirroringCredit', 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'MirroringCredit')
go

:CONNECT SQLIDW15HA\SQLDev02

ALTER DATABASE MirroringCredit
    SET PARTNER = 'TCP://SQLIDW15HA:5091'
--	SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5091'
GO
:CONNECT SQLIDW15HA\SQLDev01

ALTER DATABASE MirroringCredit
	SET PARTNER = 'TCP://SQLIDW15HA:5092'
--    SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5092'
GO

ALTER DATABASE MirroringCredit
	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'MirroringCredit')

---- failover when in sync
--ALTER DATABASE MirroringCredit
--	SET PARTNER FAILOVER
--go
--
---- force failover with potential for data loss
--ALTER DATABASE MirroringCredit
--	SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS 
