Setting Up SQL Server 2008 R2 Database Mirroring with Certificate step by step in a Workgroup

Article
09/07/2013

LAB VM

Principal Server: SQL2008R2M1

Mirroring Server: SQL2008R2M2

Witness Server: SQL2008R2M3

[Step by Step]

1.Create Database Master key, certificate and endpoint on Principal Instance(SQL2008R2M1)

  USE master
 GO
 SELECT * FROM sys.symmetric_keys
 GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='P@ssw0rd';
GO

CREATE CERTIFICATE DBM_PRIN_Cert
WITH SUBJECT = 'DB Mirroring Principal Server Certificate',
EXPIRY_DATE = '12/31/2033'
GO

SELECT * FROM sys.certificates;
GO

CREATE ENDPOINT DBM_CERT_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE DBM_PRIN_Cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

BACKUP CERTIFICATE DBM_PRIN_Cert
TO FILE = 'C:\Temp\DBM_PRIN_Cert.cer'
GO

2.Create Database Master key, certificate and endpoint on Mirroring Instance(SQL2008R2M2)

  USE master
 GO
 SELECT * FROM sys.symmetric_keys
 GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='P@ssw0rd';
GO

CREATE CERTIFICATE DBM_MIRR_Cert
WITH SUBJECT = 'DB Mirroring Mirroring Server Certificate',
EXPIRY_DATE = '12/31/2033'
GO

SELECT * FROM sys.certificates;
GO

CREATE ENDPOINT DBM_CERT_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE DBM_MIRR_Cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

BACKUP CERTIFICATE DBM_MIRR_Cert
TO FILE = 'C:\Temp\DBM_MIRR_Cert.cer'
GO

3.Create Database Master key, certificate and endpoint on Witness Instance(SQL2008R2M3)

  USE master
 GO
 SELECT * FROM sys.symmetric_keys
 GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='P@ssw0rd';
GO

CREATE CERTIFICATE DBM_WITT_Cert
WITH SUBJECT = 'DB Mirroring Witness Server Certificate',
EXPIRY_DATE = '12/31/2033'
GO

SELECT * FROM sys.certificates;
GO

CREATE ENDPOINT DBM_CERT_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE DBM_WITT_Cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

BACKUP CERTIFICATE DBM_WITT_Cert
TO FILE = 'C:\Temp\DBM_WITT_Cert.cer'
GO

4.Copy Certificate backup file to other SQL Server

Copy DBM_PRIN_Cert.cer to Mirroring Server and Witness Server

Copy DBM_MIRR_Cert.cer to Principal Server and Witness Server

Copy DBM_WITT_Cert.cer to Principal Server and Mirroring Server

5.Create login, user and associate certificate with user on Principal Instance(SQL2008R2M1)

  CREATE LOGIN DBM_MIRR_login WITH PASSWORD = 'P@ssw0rd'
 , CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
 GO
 CREATE USER DBM_MIRR_User FOR LOGIN DBM_MIRR_login;
 GO
 CREATE CERTIFICATE DBM_MIRR_Cert
 AUTHORIZATION DBM_MIRR_User
 FROM FILE = 'c:\Temp\DBM_MIRR_Cert.cer'
 GO

GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [DBM_MIRR_login]
GO

CREATE LOGIN DBM_WITT_login WITH PASSWORD = 'P@ssw0rd'
, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO
CREATE USER DBM_WITT_User FOR LOGIN DBM_WITT_login;
GO
CREATE CERTIFICATE DBM_WITT_Cert
AUTHORIZATION DBM_WITT_User
FROM FILE = 'c:\Temp\DBM_WITT_Cert.cer'
GO

GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [DBM_WITT_login]
GO

6.Create login, user and associate certificate with user on Mirror Instance(SQL2008R2M2)

  CREATE LOGIN DBM_PRIN_login WITH PASSWORD = 'P@ssw0rd'
 , CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
 GO
 CREATE USER DBM_PRIN_User FOR LOGIN DBM_PRIN_login;
 GO
 CREATE CERTIFICATE DBM_PRIN_Cert
 AUTHORIZATION DBM_PRIN_User
 FROM FILE = 'c:\Temp\DBM_PRIN_Cert.cer'
 GO

GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [DBM_PRIN_login]
GO

GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [DBM_WITT_login]
GO

7.Create login, user and associate certificate with user on Witness Instance(SQL2008R2M3)

  CREATE LOGIN DBM_PRIN_login WITH PASSWORD = 'P@ssw0rd'
 , CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
 GO
 CREATE USER DBM_PRIN_User FOR LOGIN DBM_PRIN_login;
 GO
 CREATE CERTIFICATE DBM_PRIN_Cert
 AUTHORIZATION DBM_PRIN_User
 FROM FILE = 'c:\Temp\DBM_PRIN_Cert.cer'
 GO

CREATE LOGIN DBM_MIRR_login WITH PASSWORD = 'P@ssw0rd'
, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO
CREATE USER DBM_MIRR_User FOR LOGIN DBM_MIRR_login;
GO
CREATE CERTIFICATE DBM_MIRR_Cert
AUTHORIZATION DBM_MIRR_User
FROM FILE = 'c:\Temp\DBM_MIRR_Cert.cer'
GO

 
 GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [DBM_PRIN_login]
 GO 
 GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [DBM_MIRR_login]
 GO

8.Prepare Mirroring Database

(1)Backup Principal Database on Principal Instance

  USE master
 GO
 BACKUP DATABASE MyDB
 TO DISK = 'C:\Temp\MyDB_FullBackup.bak'
 GO
 BACKUP LOG MyDB
 TO DISK = 'C:\Temp\MyDB_LogBackup.trn'
 GO

(2)Restore Mirroring Database on Mirroring Instance

  USE master
 GO
 RESTORE DATABASE MyDB
 FROM DISK = 'C:\Temp\MyDB_FullBackup.bak'
 WITH NORECOVERY
 GO
 RESTORE LOG MyDB
 FROM DISK = 'C:\Temp\MyDB_LogBackup.trn'
 WITH NORECOVERY
 GO

9.Enable Database Mirroring

(1)Set Partner on Mirroring Instance (SQL2008R2M2)

  ALTER DATABASE MyDB
 SET PARTNER = 'TCP://SQL2K8R2M1:5022'

(2)Set Partner and Witness on Principal Instance (SQL2008R2M1)

  ALTER DATABASE MyDB
 SET PARTNER = 'TCP://SQL2K8R2M2:5022'

ALTER DATABASE MyDB
SET WITNESS = 'TCP://SQL2K8R2M3:5022'

10.Database Mirroring Status

[Reference]

Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms186384.aspx

Step-by-step
guide to configure Database Mirroring between SQL Server Instances in a
Workgroup

https://blogs.msdn.com/b/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

Comments

Anonymous
November 25, 2013
Great step by step summary, however you script in step 7 is missing Grant end point access for principle server7.Create login, user and associate certificate with user on Witness Instance(SQL2008R2M3)GRANT CONNECT On ENDPOINT::[DBM_CERT_Endpoint] TO [DBM_PRIN_login]GO
Anonymous
December 13, 2013
thanks Varut, I will add the missing Grant to Step 7.
Anonymous
March 12, 2014
Awesome post mate, made the whole process ten times easier. Cheers.

Share via

Setting Up SQL Server 2008 R2 Database Mirroring with Certificate step by step in a Workgroup

Comments

Additional resources