Nirav's Diary

SQL Server Mirroring with Certificate

By nirav on May 25th, 2014

SQL Server Mirroring with Certificate.

Step1 : Take a backup of Database and then backup of log as well, of the database which you want mirrored.
On Principal Server

USE [master]
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\SQL\AdventureWorks2012.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\SQL\AdventureWorks2012.TRN'

Step2 : Copy this both database and log backup from principal server to mirroring server and restore it in specific drive.

On Mirroring Server

USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM  DISK = N'D:\SQL\AdventureWorks2012.bak' WITH  FILE = 1,  NORECOVERY

RESTORE LOG [AdventureWorks2012] FROM  DISK = N'D:\SQL\AdventureWorks2012.TRN' WITH  FILE = 1,  NORECOVERY

Step3 : Create Master Key, Certificate, Endpoint for Mirroring and backup of certificate on the principal server. Copy certificate backup to mirroring server.

On Principal Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

USE master;
CREATE CERTIFICATE Principal_Server_Certificate
   WITH SUBJECT = 'Principal Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Principal_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Principal_Server_Certificate TO FILE = 'D:\Principal_Server_Certificate.cer';
GO

Step4 : Create Master Key, Certificate, Endpoint for Mirroring and backup of certificate on the mirroring server. Copy certificate backup to principal server.

On Mirrored Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

USE master;
CREATE CERTIFICATE Mirroring_Server_Certificate
   WITH SUBJECT = 'Mirroring Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Mirroring_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Mirroring_Server_Certificate TO FILE = 'D:\Mirroring_Server_Certificate.cer';
GO

On Principal Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

CREATE CERTIFICATE Mirroring_Server_Certificate
   WITH SUBJECT = 'Mirroring Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Mirroring_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Mirroring_Server_Certificate TO FILE = 'D:\Mirroring_Server_Certificate.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mir];
GO

On Mirroring Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

CREATE CERTIFICATE Principal_Server_Certificate
   WITH SUBJECT = 'Principal Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Principal_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Principal_Server_Certificate TO FILE = 'D:\Principal_Server_Certificate.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mir];
GO

On Mirroring Server

ALTER DATABASE AdventureWorks2012
    SET PARTNER = 'TCP://PrincipalServerName.DomainName:7024';
GO

On Principal Server

ALTER DATABASE AdventureWorks2012
    SET PARTNER = 'TCP://MirringServer.DomainName:7024';
GO

To Failover Partner

ALTER DATABASE AdventureWorks2012
    SET PARTNER FAILOVER;

To Stop the mirroring.

--Run below script on Principal server
ALTER DATABASE AdventureWorks2012
    SET PARTNER OFF;
--Run below script on Mirroring server
Restore Database AdventureWorks2012 With NoRecovery

Notes:
1) Windows user must have an access to both server sharing folder.
2) Port number 7024 must me exceptional port number in firewall.