Setting Up SQL Server Database Mirroring with Certificate step by step in a Workgroup 一步一步使用憑證設定SQL Server資料庫鏡像

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

一步一步使用憑證設定SQL Server資料庫鏡像

from my MSDN blog - September 7, 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 AES,
ROLE = ALL
)
GO
sys.database_mirroring_endpoints (Transact-SQL)
https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-mirroring-endpoints-transact-sql?view=sql-server-ver16
The RC4 algorithm is only supported for backward compatibility. New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100. (Not recommended.) 
Use a newer algorithm such as one of the AES algorithms instead. In SQL Server 2012 (11.x) and higher, material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.
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 AES,
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 AES,
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)

Setting Up SQL Server 2008 R2 Database Mirroring step by step