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