/*============================================================================
	File: 2 - DBM Failures and Failovers.sql 

	SQL Server Versions:2008 R2
------------------------------------------------------------------------------
	Copyright (C) 2012 Joe Sack, SQLskills.com
	All rights reserved. 

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

	You may alter this code for your own *non-commercial* purposes. You may
	republish altered code as long as you give due credit. 

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

-- 1) Connect to all three servers in Object Explorer

-- 2) Validate which server we're on (connect to sql2k12-svr1)
-- 3) Validate the state of the mirror
:CONNECT sql2k12-svr1
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
GO

-- 4) Shut down the mirror server service
:CONNECT sql2k12-svr2
SHUTDOWN WITH NOWAIT
GO

:CONNECT sql2k12-svr1
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
GO

-- Still accessible though
:CONNECT sql2k12-svr1
SELECT *
FROM CreditReporting.dbo.category;
GO

-- 5) Bring up the mirror service
:CONNECT sql2k12-svr1
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
GO

-- 6) Bring down the witness service
:CONNECT sql2k12-svr3
SHUTDOWN WITH NOWAIT
GO

:CONNECT sql2k12-svr1
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

SELECT *
FROM CreditReporting.dbo.category;
GO

-- 6) Bring down the mirror server
:CONNECT sql2k12-svr2
SHUTDOWN WITH NOWAIT
GO

:CONNECT sql2k12-svr1
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
GO

-- Will this work?
:CONNECT sql2k12-svr1
SELECT *
FROM CreditReporting.dbo.category;
GO

-- 7) Bring up the witness server 
-- ** (go to Node, SSMS may not respond) **
:CONNECT sql2k12-svr1
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

SELECT *
FROM CreditReporting.dbo.category;
GO


-- 8) Bring up mirror server

-- 9) Switch to mirror connection **
SELECT @@SERVERNAME;
GO

-- 10) Bring down the principal server 
:CONNECT sql2k12-svr1
SHUTDOWN WITH NOWAIT
GO

:CONNECT sql2k12-svr2
SELECT @@SERVERNAME
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
GO

:CONNECT sql2k12-svr2
SELECT *
FROM CreditReporting.dbo.category;
GO

-- 10) Bring up the former principal
:CONNECT sql2k12-svr1
SELECT @@SERVERNAME
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
GO

-- 11) Remove the witness from the picture
:CONNECT sql2k12-svr1
SELECT @@SERVERNAME;

ALTER DATABASE [CreditReporting] 
SET WITNESS OFF;
GO

:CONNECT sql2k12-svr1
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
GO

-- 12) Shut down the mirror
:CONNECT sql2k12-svr1
SHUTDOWN WITH NOWAIT
GO

:CONNECT sql2k12-svr2
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

SELECT *
FROM CreditReporting.dbo.category;
GO


-- 13) Bring back the mirror, bring down principal
:CONNECT sql2k12-svr2
SHUTDOWN WITH NOWAIT
GO

-- Will this work?
:CONNECT sql2k12-svr1
SELECT @@SERVERNAME
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

SELECT *
FROM CreditReporting.dbo.category;
GO

-- 14) Let's say we don't know if the former
--     principal is coming back and we force service
:CONNECT sql2k12-svr1

ALTER DATABASE CreditReporting
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

GO

-- Wait 45 seconds to recover
:CONNECT sql2k12-svr1
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
GO

-- Might need to wait a few seconds to recover
:CONNECT sql2k12-svr1
SELECT *
FROM CreditReporting.dbo.category;
GO


-- 15) Bring up the prior principal
--     Connect to the prior principal
--     If the old principal couldn't see the
--	   mirror - we risk split-brain!!
:CONNECT sql2k12-svr2
SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
GO

-- ** Choice is to try to salvage change first
-- or resume (but may have data loss!!!) **

-- 16) Resume mirroring session from principal
:CONNECT sql2k12-svr2
SELECT @@SERVERNAME;

ALTER DATABASE CreditReporting
SET PARTNER RESUME;

SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

GO


-- 17) Add back witness
:CONNECT sql2k12-svr1
ALTER DATABASE [CreditReporting] 
SET WITNESS = N'TCP://sql2k12-svr3.SQLskillsdemos.com:5022';

SELECT 
	mirroring_state_desc, 
	mirroring_role_desc,
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	mirroring_witness_state_desc,
	mirroring_failover_lsn, 
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

GO