Setup SQL Server 2012 AlwaysOn Availability Groups using Certificate

Setup SQL 2012 AlwaysOn Availability Groups using Certificate

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]

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