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.