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

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

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

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

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