/ SQL Mirroring between two stand-alone servers using certificates

SQL Mirroring between two stand-alone servers using certificates

Mirroring is usually done between two servers inside the same network where both servers are a part of a domain and have a trusted authentication mechanism provided by Active Directory. But what if this is not the case? What if you have your mirroring partners in separate networks or in separate data centers even, where there is no trusted authentication possible?
There is still hope, and the solution is to use SQL Server certificates. Here are the scripts you need to set up mirroring between a PRINCIPAL and a MIRROR server using certificates.

PLEASE NOTE: you can not simply copy and paste these scripts in to SQL Server Management Studio, press F5 and expect everything to work. You need to run each individual statement by itself, and some times you will need to run scripts on the mirror before progressing on the principal (i.e you need to create and do a backup of the certificate on the mirror before importing it to the principal, obviously)











































--> This is the setup of the PRINCIPAL (master) in a mirroring environment
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
--> Create a name not using "master" or "principal", remember: the roles can
--  switch but the certificates remain the same.
CREATE CERTIFICATE SQLSrv1_mirroring_cert
   WITH SUBJECT = 'SQLSrv1 certificate',
      START_DATE = '2009-01-01 00:00:00',
      EXPIRY_DATE = '2030-01-01 00:00:00'
GO
CREATE ENDPOINT Mirroring_Endpoint
   STATE = STARTED
   AS TCP (
      LISTENER_PORT = 7024,
      LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE SQLSrv1_mirroring_cert,
      ENCRYPTION = REQUIRED ALGORITHM AES,
      ROLE = ALL
   )
GO
--> Backup certificate and transfer to SQLSrv2
BACKUP CERTIFICATE SQLSrv1_mirroring_cert TO FILE = 'C:\SQLSrv1_mirroring_cert.cer';
GO
--> Create a login for the other server process
CREATE LOGIN SQLSrv2_mirroring_login WITH PASSWORD = 'password';
GO
--> Create a user for the new login
CREATE USER SQLSrv2_mirroring_user FOR LOGIN SQLSrv2_mirroring_login;
GO
--> Associate the certificate with the user.
CREATE CERTIFICATE SQLSrv2_cert
   AUTHORIZATION SQLSrv2_mirroring_user
   FROM FILE = 'C:\SQLSrv2_mirroring_cert.cer'
GO
--> Grant the user access to the endpoint
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLSrv2_mirroring_login]
GO
--> Create a FULL BACKUP of each database to be mirrored
BACKUP DATABASE mojo TO DISK = 'D:\SQLbackup\mojo_20090211.bak'
--> Restore these databases to the MIRROR using NORECOVERY
--> Set up partnering between the mirror and the principal (use a fully qualified name or an IP address)
--> Set up partnering between the principal and the mirror (use a fully qualified name or an IP address)
ALTER DATABASE mojo SET PARTNER ='tcp://sqlsrv2:7024'
GO






































--> This is the setup of the MIRROR (slave) in a mirroring environment
USE master
GO
--> Make sure a master key is set
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
-->
CREATE CERTIFICATE SQLSrv2_mirroring_cert
   WITH SUBJECT = 'SQLSrv2 certificate',
      START_DATE = '2009-01-01 00:00:00',
      EXPIRY_DATE = '2030-01-01 00:00:00'
GO
CREATE ENDPOINT Mirroring_Endpoint
   STATE = STARTED
   AS TCP (
      LISTENER_PORT = 7024,
      LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE SQLSrv2_mirroring_cert,
      ENCRYPTION = REQUIRED ALGORITHM AES,
      ROLE = ALL
   )
GO
--> Backup certificate and transfer to SQLSrv1
BACKUP CERTIFICATE SQLSrv2_mirroring_cert TO FILE = 'C:\SQLSrv2_mirroring_cert.cer';
GO
--> Create a login for the other server process
CREATE LOGIN SQLSrv1_mirroring_login WITH PASSWORD = 'password'
GO
--> Create a user for the new login
CREATE USER SQLSrv1_mirroring_user FOR LOGIN SQLSrv1_mirroring_login;
GO
--> Associate the certificate with the user.
CREATE CERTIFICATE SQLSrv1_mirroring_cert
   AUTHORIZATION SQLSrv1_mirroring_user
   FROM FILE = 'C:\SQLSrv1_mirroring_cert.cer'
GO
--> Grant the user access to the endpoint
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLSrv1_mirroring_login]
GO
--> Copy a backup of the database(s) to be mirrored
--> Restore them using NORECOVERY
RESTORE DATABASE mojo
   FROM DISK='d:\SQLBackups\mojo_20090211.bak'
   WITH NORECOVERY
GO
--> Set up partnering between the mirror and the principal (use a fully qualified name or an IP address)
ALTER DATABASE mojo SET PARTNER ='tcp://sqlsrv1:7024'
GO