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
GORESTORE 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'