/*============================================================================
	File: 1 - Set up Database Mirroring.sql 

	SQL Server Versions: 2012
------------------------------------------------------------------------------
	Copyright (C) 2013 Jonathan Kehayias, 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.
============================================================================*/ 

:CONNECT sql2k12-svr1
-- Is it in FULL recovery model?
SELECT recovery_model_desc
FROM sys.databases
WHERE name = 'CreditReporting';
GO

:CONNECT sql2k12-svr1
-- If not, set it
ALTER DATABASE CreditReporting
SET RECOVERY FULL;
GO

-- ** Disable SQL Agent Jobs related to this principal database (such as log backups) **

:CONNECT sql2k12-svr1
-- Full backup
BACKUP DATABASE CreditReporting
TO DISK = 
  '\\SQL2K12-SVR1\Backup\CreditReport.bak'
WITH INIT, COMPRESSION;
GO

-- Transaction log backup
BACKUP LOG  CreditReporting
TO DISK = 
  '\\SQL2K12-SVR1\Backup\CreditReport_1.trn'
WITH INIT, COMPRESSION;
GO

-- Mirror Partner
:CONNECT sql2k12-svr2
SELECT @@SERVERNAME;
GO

-- Restore backup
RESTORE DATABASE CreditReporting
FROM DISK = 
  '\\sql2k12-svr1\Backup\CreditReport.bak'
WITH NORECOVERY;
  
-- Transaction log restore
RESTORE LOG  CreditReporting
FROM DISK = 
  '\\sql2k12-svr1\Backup\CreditReport_1.trn'
WITH NORECOVERY;
GO

-- OK!  The file paths are different.  NOT IDEAL!!!!
-- Lets try again using WITH MOVE
:CONNECT sql2k12-svr2
USE [master]
RESTORE DATABASE [CreditReporting] 
FROM  DISK = N'\\sql2k12-svr1\backup\CreditReport.bak' 
WITH  MOVE N'CreditData' TO N'C:\SQLskills\MSSQL11.MSSQLSERVER\MSSQL\DATA\CreditData.mdf',  
	  MOVE N'CreditLog' TO N'C:\SQLskills\MSSQL11.MSSQLSERVER\MSSQL\DATA\CreditLog.ldf',  
NORECOVERY;
GO

RESTORE LOG [CreditReporting] 
FROM  DISK = N'\\sql2k12-svr1\backup\CreditReport_1.trn' 
WITH NORECOVERY;
GO




-- Principal Endpoint
:CONNECT sql2k12-svr1

CREATE ENDPOINT [Mirroring] 
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, 
ENCRYPTION = REQUIRED ALGORITHM AES);
GO
-- DEFAULT encryption algorithm pre-2012 was RC4!

-- Consider using 

ALTER ENDPOINT [Mirroring] STATE = STARTED;
GO

-- Principal Endpoint
SELECT name, state_desc, role_desc
FROM sys.database_mirroring_endpoints;
GO

-- Mirror Endpoint
:CONNECT sql2k12-svr2

-- DROP ENDPOINT [Mirroring]
CREATE ENDPOINT [Mirroring] 
AS TCP (LISTENER_PORT = 5022)
	FOR DATA_MIRRORING (ROLE = PARTNER, 
	ENCRYPTION = REQUIRED ALGORITHM AES);
GO

ALTER ENDPOINT [Mirroring] STATE = STARTED;
GO

-- Mirror Endpoint
SELECT name, state_desc, role_desc
FROM sys.database_mirroring_endpoints;

GO

-- Witness
:CONNECT sql2k12-svr3

CREATE ENDPOINT [Mirroring] 
AS TCP (LISTENER_PORT = 5022)
	FOR DATA_MIRRORING
(ROLE = WITNESS, 
ENCRYPTION = REQUIRED ALGORITHM AES);
GO

ALTER ENDPOINT [Mirroring] STATE = STARTED;
GO

SELECT name, state_desc, role_desc
FROM sys.database_mirroring_endpoints;
GO

-- Enable mirroring from MIRROR
:CONNECT sql2k12-svr2

ALTER DATABASE [CreditReporting] 
SET PARTNER = N'TCP://sql2k12-svr1.SQLskillsdemos.com:5022';
GO


-- Enable next from Principal
:CONNECT sql2k12-svr1

ALTER DATABASE [CreditReporting] 
SET PARTNER = N'TCP://sql2k12-svr2.SQLskillsdemos.com:5022';
GO

-- What Happened!  Need Endpoint permissions for service account when using Service SIDs

:CONNECT sql2k12-svr1
GRANT CONNECT ON ENDPOINT::[Mirroring] 
TO [SQLSKILLSDEMOS\SQLServiceAcct]
GO

:CONNECT sql2k12-svr2
GRANT CONNECT ON ENDPOINT::[Mirroring] 
TO [SQLSKILLSDEMOS\SQLServiceAcct]
GO

:CONNECT sql2k12-svr3

GRANT CONNECT ON ENDPOINT::[Mirroring] 
TO [SQLSKILLSDEMOS\SQLServiceAcct]
GO

-- Now enable next from Principal
:CONNECT sql2k12-svr1

ALTER DATABASE [CreditReporting] 
SET PARTNER = N'TCP://sql2k12-svr2.SQLskillsdemos.com:5022';
GO

-- Add witness
:CONNECT sql2k12-svr1

ALTER DATABASE [CreditReporting] 
SET WITNESS = N'TCP://sql2k12-svr3.SQLskillsdemos.com:5022';
GO


:CONNECT sql2k12-svr1
-- Let's look at the status
SELECT 
	-- Suspended / Disconnected / Snchronizing
	-- Pending Failover / Synchronized /
	-- Unsynchronized / NULL
	mirroring_state_desc, 
	-- Principal or Mirror
	mirroring_role_desc,
	-- Off (Async) / Full (Sync)
	mirroring_safety_level_desc,
	mirroring_partner_name, 
	mirroring_witness_name,
	-- Connected / Disconnected
	mirroring_witness_state_desc,
	-- LSN of latest transaction log record 
	-- guaranteed to be hardened on disk on both
	-- partners (used after failover as point of
	-- reconciliation)
	mirroring_failover_lsn, 
	-- Seconds to wait for a reply from partner
	-- or witness before deeming "unavailable"
	mirroring_connection_timeout
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
GO

-- Enable SQL Agent Jobs related to this principal database (such as log backups)

-- What about external objects (logins/linked-servers/packages)?  
-- We'll cover in a separate set of scripts.

-- ***
-- What if it didn't work?

-- 1) Check that the SQL Server agent accounts have correct endpoint permissions (check SQL error log for signs)

:CONNECT sql2k12-svr1
SELECT  @@SERVERNAME ServerName,
		e.endpoint_id,
		p.permission_name,
		suser_name(p.grantee_principal_id),
		e.name,
		p.state_desc
FROM sys.server_permissions p
INNER JOIN sys.endpoints e ON
	p.major_id = e.endpoint_id
WHERE protocol_desc = 'TCP';
GO
:CONNECT sql2k12-svr2
SELECT  @@SERVERNAME ServerName,  
		e.endpoint_id,
		p.permission_name,
		suser_name(p.grantee_principal_id),
		e.name,
		p.state_desc
FROM sys.server_permissions p
INNER JOIN sys.endpoints e ON
	p.major_id = e.endpoint_id
WHERE protocol_desc = 'TCP';
GO
:CONNECT sql2k12-svr3
SELECT  @@SERVERNAME ServerName,  
		e.endpoint_id,
		p.permission_name,
		suser_name(p.grantee_principal_id),
		e.name,
		p.state_desc
FROM sys.server_permissions p
INNER JOIN sys.endpoints e ON
	p.major_id = e.endpoint_id
WHERE protocol_desc = 'TCP';
GO

-- 2) For cross domains or non-domain accounts, do the logins exist on the other partner 
--    and do they have CONNECT permission? 

-- 3) For certificate based DBM, check the deployment steps per "Using Certificates for Database Mirroring"

-- 4) Are the endpoints started on the partners?

:CONNECT sql2k12-svr1
SELECT @@SERVERNAME ServerName, name, state_desc
FROM sys.database_mirroring_endpoints;
GO
:CONNECT sql2k12-svr2
SELECT @@SERVERNAME ServerName, name, state_desc
FROM sys.database_mirroring_endpoints;
GO
:CONNECT sql2k12-svr3
SELECT @@SERVERNAME ServerName, name, state_desc
FROM sys.database_mirroring_endpoints;
GO

-- 5) What ports are being used?  Are they open on the firewall?

:CONNECT sql2k12-svr1
SELECT @@SERVERNAME ServerName, port
FROM sys.tcp_endpoints
WHERE name = 'Mirroring';
GO
:CONNECT sql2k12-svr2
SELECT @@SERVERNAME ServerName, port
FROM sys.tcp_endpoints
WHERE name = 'Mirroring';
GO
:CONNECT sql2k12-svr3
SELECT @@SERVERNAME ServerName, port
FROM sys.tcp_endpoints
WHERE name = 'Mirroring';
GO

-- 6) Is anyone else using the port? Are firewall ports unblocked?
-- Check netstat -abn

-- 7) Are the roles configured correctly per instance?
:CONNECT sql2k12-svr1
SELECT @@SERVERNAME ServerName, role_desc
FROM sys.database_mirroring_endpoints;
GO
:CONNECT sql2k12-svr2
SELECT @@SERVERNAME ServerName, role_desc
FROM sys.database_mirroring_endpoints;
GO
:CONNECT sql2k12-svr3
SELECT @@SERVERNAME ServerName, role_desc
FROM sys.database_mirroring_endpoints;
GO

-- 8) Have you tried the fully qualified domain name? 
--    Does it unambiguously identify the instance and port? 
--    TCP :// < system-address> : < port> 

-- 9) Were all log backups created after the last full backup applied with NO RECOVERY?

-- 10) Is the file layout path the same?  
--     If different, did you use the MOVE option?

-- 11) Was the first ALTER DATABASE SET PARTNER run on the mirror? (it must be)  
--     And was the second ALTER DATABASE on the principal server?

-- Other errors?
-- 1412 or 1478 - you didn't apply tran log or additional tran log backup was taken
-- 1416 - mirror not in NORECOVERY
-- 1418 - The server network address "%.*ls" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
-- 1456 - The ALTER DATABASE command could not be sent to the remote server instance '%.*ls'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.








