Setup SQL Server 2012 Always On Availability Groups using Certificate 使用憑證設定SQL Server Always On Availability Groups

Setup SQL Server 2012 Always On Availability Groups using Certificate

使用憑證設定SQL Server Always On Availability Groups

from my MSDN blog January 30, 2013

1.如果SQL Server的啟動帳戶使用本機帳戶或虛擬帳戶Virtual Account(本機受管理的服務帳戶Managed Local Accounts)，若要在這種環境下設定AlwaysOn，則必須使用憑證Certificate來設定。

安裝SQL Server 2012時SQL Server服務的預設啟動帳戶就是Virtual Account，也就是NT Service\MSSQLSERVER

以下範例：2個Instance都是使用NT Service\MSSQLSERVER

2.使用憑證Certificate建立端點Endpoint與相關登入帳戶與權限設定

(1)Create encryption key, certificate and end-points on Principal Instance

USE [master]
GO
SELECT * FROM sys.symmetric_keys;
GO
-- Create Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO

CREATE CERTIFICATE AG_HOST_SQL2012A1_PRIN_Cert
WITH SUBJECT = 'Always On HOST_PRIN Certificate',
EXPIRY_DATE = '12/31/2033';
GO

SELECT * FROM sys.certificates;
GO

CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE AG_HOST_SQL2012A1_PRIN_Cert
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

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

 

(2)Create encryption key, certificate and end-points on Mirror Instance

USE [master]
GO

SELECT * FROM sys.symmetric_keys;
GO

-- Create Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO

CREATE CERTIFICATE AG_HOST_SQL2012A1_MIRR1_Cert
WITH SUBJECT = 'Always On HOST_MIRR1 Certificate',
EXPIRY_DATE = '12/31/2033';
GO

SELECT * FROM sys.certificates;
GO

CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE AG_HOST_SQL2012A1_MIRR1_Cert
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

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

(3)複製憑證

-- copy SQL2012A1 C:\Temp\AG_HOST_SQL2012A1_MIRR1_Cert.cer to SQL2012A2
-- copy SQL2012A2 C:\Temp\AG_HOST_SQL2012A1_PRIN_Cert.cer to SQL2012A1

(4)Create login, user and associate certificate with user on Principal Instance

CREATE LOGIN AG_SQL2012A2 WITH PASSWORD = 'P@ssw0rd'
, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO
CREATE USER AG_SQL2012A2 FOR LOGIN AG_SQL2012A2;
GO
CREATE CERTIFICATE AG_HOST_SQL2012A1_MIRR1_Cert
AUTHORIZATION AG_SQL2012A2
FROM FILE = 'c:\Temp\AG_HOST_SQL2012A1_MIRR1_Cert.cer'
GO
GRANT CONNECT On ENDPOINT::[Hadr_endpoint] TO [AG_SQL2012A2]
GO

(5)Create login, user and associate certificate with user on Mirror Instance

CREATE LOGIN AG_SQL2012A1 WITH PASSWORD = 'P@ssw0rd'
, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO
CREATE USER AG_SQL2012A1 FOR LOGIN AG_SQL2012A1;
GO
CREATE CERTIFICATE AG_HOST_SQL2012A1_PRIN_Cert
AUTHORIZATION AG_SQL2012A1
FROM FILE = 'c:\Temp\AG_HOST_SQL2012A1_PRIN_Cert.cer'
GO
GRANT CONNECT On ENDPOINT::[Hadr_endpoint] TO [AG_SQL2012A1]
GO

3.將主要Instance上的MyDB資料庫做備份(a Full Backup and a Log Backup)並指定NO RECOVERY 還原到第二個Instance。

-- 第1個也就是主要的Instance SQL2012A1

BACKUP DATABASE [MyDB] TO DISK = N'C:\Temp\MyDB.bak'
GO
BACKUP LOG [MyDB] TO DISK = N'C:\Temp\MyDB.trn'
GO

-- 第2個Instance SQL2012A2

RESTORE DATABASE [MyDB] FROM DISK = N'C:\Temp\MyDB.bak'
WITH NORECOVERY
GO
RESTORE LOG [MyDB] FROM DISK = N'C:\Temp\MyDB.trn'
WITH NORECOVERY
GO

4.New Availability Group Wizard 啟動[新增可用性群組精靈]，詳細步驟請參考這篇 Quick Setup a SQL Server 2012 AlwaysOn Availability Groups

指定複本時檢查一下端點的設定

5.在選取初始資料同步處理時，選擇[僅聯結](Join Only)

6.驗證時會略過很多項目

7.完成時也會略過很多項目

8.接著可以進行新增可用性群組接聽程式。請參考這篇 Quick Setup a SQL Server 2012 AlwaysOn Availability Groups  的步驟7.新增可用性群組接聽程式

[Reference]

Database Mirroring - Use Certificates for Outbound Connections

guide to configure Database Mirroring between SQL Server Instances in a Workgroup