Friday, January 22, 2016

MSSQL Mirroring Made Easy

I got a task to mirror our databases to a secondary site that we can use for failover when required. MSSQL database mirroring is quite easy , you have to identify
your goals to get maximum benefits from it.We target Asynchronous mirror to avoid any db lock for transaction confirmation . Because our system has quite good traffic and we
don't want to wait our users for application response.

Following are step-by-step guide to deploy asynchronous mirroring using TSQL, it is an inspiration from "Suhas De"  post on database mirroring. I am not using witness server as
it make no sense to use in asynchronous setup.

You can find more details on database mirroring on following link
https://msdn.microsoft.com/en-us/library/ms189852.aspxhttps://msdn.microsoft.com/en-us/library/ms189852.aspx

I will write about failover process and to enable jobs to monitor database mirroring.
Enjoy!


--===================================================================================
--Step 1: Create encryption key, certificate and end-points on Principal Instance
-- Drop or rename if conflict arises
--===================================================================================
/* Execute this against the Principal Instance */
 

USE MASTER 
 GO 
--Drop master key; 
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%') 
BEGIN
   CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Password!' --place your password here 

END
GO 

-- drop certificate PRINCIPAL_cert 
 IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = 'PRINCIPAL_cert'
 BEGIN
   CREATE
CERTIFICATE PRINCIPAL_cert
      
WITH SUBJECT = 'PRINCIPAL certificate',
      
START_DATE = '01/19/2016' -- as recent date as possible 

 END 
 GO

  
IF NOT EXISTS (SELECT * FROM sys.database_mirroring_endpoints WHERE name='MirrorEndpoint')BEGIN
  
-- drop ENDPOINT MirrorEndpoint
  
CREATE ENDPOINT MirrorEndpoint
       STATE
= STARTED
      
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
      
FOR DATABASE_MIRRORING
      
(
          
AUTHENTICATION = CERTIFICATE PRINCIPAL_cert,
          
ENCRYPTION = REQUIRED ALGORITHM RC4,
          
ROLE = ALL
       )
END
  
GO

 -- Create Certificate on Local drive 
BACKUP CERTIFICATE PRINCIPAL_cert
    
TO FILE = '\Principal_server_path\PRINCIPAL_cert.cer'   

-- place local drive path accordingly. Once created copy it to Mirror Server as well. This will be use in step-3 --===================================================================================
--Step 2: Create encryption key, certificate and end-points on Mirror Instance
-- Drop or rename if conflict arises
--===================================================================================
/* Execute this against the Mirror Instance */ 

 USE MASTER 
 GO 
--drop master key 
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%') 
BEGIN
   CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Password!' -- place your password here 

 END
GO--drop CERTIFICATE MIRROR_cert 


 IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = 'MIRROR_cert'
 BEGIN
   CREATE
CERTIFICATE MIRROR_cert
      
WITH SUBJECT = 'MIRROR certificate',
      
START_DATE = '01/19/2016' -- as recent date as possible 

END 
GO 
--drop ENDPOINT MirrorEndpoint
  
IF NOT EXISTS (SELECT * FROM sys.database_mirroring_endpoints WHERE name='MirrorEndpoint'

 BEGIN
   CREATE ENDPOINT
MirrorEndpoint
       STATE
= STARTED
      
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
      
FOR DATABASE_MIRRORING
      
(
          
AUTHENTICATION = CERTIFICATE MIRROR_cert,
          
ENCRYPTION = REQUIRED ALGORITHM RC4,
          
ROLE = ALL
       )
END
GO 

-- Create Certificate on Local drive 
BACKUP CERTIFICATE MIRROR_cert
    
TO FILE = '\Mirror_server_path\MIRROR_cert.cer'; -- Once Createad copy it to Principal server as well. This will be use in step-4  

GO
--=====================================================================================
--Step 3: Create login, user and associate certificate with user on Principal Instance
--======================================================================================
USE MASTER 

 GO 
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
--drop LOGIN HOST_MIRR_login
--drop USER HOST_MIRR_user 

 CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!' 
 GO 
 CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login
GO 

 CREATE CERTIFICATE MIRROR_cert
    
AUTHORIZATION HOST_MIRR_user
    
FROM FILE = '\Principal_server_path\MIRROR_cert.cer'  

GO 
GRANT CONNECT ON ENDPOINT::MirrorEndpoint TO [HOST_MIRR_login]
GO 

--===================================================================================
--Step 4: Create login, user and associate certificate with user on Mirror Instance
--===================================================================================
/*
*  Execute this against the Mirror Instance.
USE MASTER 

GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
-- drop LOGIN HOST_PRIN_login
 

CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!' 
 GO 
CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login
GO 

 CREATE CERTIFICATE PRINCIPAL_cert
    
AUTHORIZATION HOST_PRIN_user
    
FROM FILE = '\Mirror_server_path\PRINCIPAL_cert.cer'
    
GO 

   GRANT CONNECT ON ENDPOINT::MirrorEndpoint TO [HOST_PRIN_login]
GO
--=================================================================================================
--Step 5: Create the Mirrored Database on the Mirror Server using backups from the Principal Server
--=================================================================================================
/*
*  Execute this against the Principal Instance.
*/
USE MASTERGOALTER DATABASE Mirror_Dbt SET recovery FULL

 BACKUP DATABASE Mirror_Dbt
    
TO DISK = '\Principal_server_path\Mrr_Dbt_FullBackup.bak' WITH init -- set path accordingly 

GO 
 BACKUP LOG Mirror_Dbt
    
TO DISK = '\Principal_server_path\Mrr_Dbt_LogBackup.trn' -- set path accordingly 

GO 
 /*
*  Copy Mrr_Dbt_FullBackup.bak and Mrr_Dbt_LogBackup.trn to the
*  Mirror Server.
*  Execute Following against the Mirror Instance.
*/
USE MASTERGORESTORE DATABASE Mirror_Dbt
    
FROM DISK = '\Mirror_server_path\\Mrr_Dbt_FullBackup.bak' -- set path accordingly
    
WITH NORECOVERY
GO
RESTORE LOG Mirror_Dbt
    
FROM DISK = '\Mirror_server_path\\Mrr_Dbt_LogBackup.trn' -- set path accordingly
    
WITH NORECOVERY
GO
 

--===================================================================================
--Step 6: Setup Mirroring
--===================================================================================

/*
*  Following steps will setup mirroring
*  Execute this against the Mirror Instance.
*/
ALTER DATABASE Mirror_Dbt
    
SET PARTNER = 'TCP://<<your mirror server name here>>:5022' -- <<your mirror server name here>> = Fully qualified name or Static IP Address 

GO 
 /*
*  Execute this against the Principal Instance.
*/
ALTER DATABASE Mirror_Dbt
      
SET PARTNER = 'TCP://<<your mirror server name here>>:5023' -- <<your mirror server name here>> = Fully qualified name or Static IP Address 

GO 
 /*
You can set Full Safety or High Performance mode as per your needs
*/

-- ALTER DATABASE Mirror_Dbt SET SAFETY FULL; -- will set synchronous mirroring 

 ALTER DATABASE Mirror_Dbt SET SAFETY OFF; -- will set asynchronous mirroring , require enterprise edition 
GO 
--=================================================================
-- Verify Mirroring State
--=================================================================
-- use it on Principal and Mirror server accordingly
 

SELECT  databases.name AS DatabaseName,
      
database_mirroring.mirroring_state,
      
database_mirroring.mirroring_state_desc,
      
database_mirroring.mirroring_role_desc,
      
database_mirroring.mirroring_safety_level,
      
database_mirroring.mirroring_safety_level_desc,
      
database_mirroring.mirroring_safety_sequence FROM sys.database_mirroring    INNER JOIN sys.databases ON databases.database_id=database_mirroring.database_id WHERE databases.name='Mirror_Dbt'