在工作群組叢集環境中建立 AlwaysOn 可用性群
大家好，繼 SQL Server 2016 新功能搶先看系列最新力作【在工作群組叢集環境中建立 AlwaysOn 可用性群】新功能！
本文章為您詳細講解，分為 10 大重點主題：
- 摘要
- 學習目標
- 前置作業
- 建立容錯移轉叢集
- SQL Server組態設定
- 建立AlwaysOn可用性群組的前置作業
- 建立可用性群組資料庫
- 建立可用性群組
- 手動初始化次要複本
- 結論
👉 SQL Server 2016 180天試用版：https://www.microsoft.com/zh-tw/evalcenter/evaluate-sql-server-2016
摘要
AlwaysOn 可用性群組（Availability Group）自從 SQL Server 2012 推出，目的在於改善過去使用資料庫鏡像（Database Mirroring）的諸多不便，並對資料庫層級的高可用性進行大幅強化，歷經兩個版本的改良，AlwaysOn 可用性群組已經是許多企業愛用的高可用性與災害復原解決方案之一。
AlwaysOn 可用性群組建構在 Windows Server 容錯移轉叢集（Windows Server Failover Cluster；WSFC）技術之上，以往的 Windows Server 容錯移轉叢集必須先將伺服器加入至 Active Directory 網域，再透過叢集管理員將成員伺服器加入至容錯移轉叢集，在雲端環境或多網域（multi-domain）環境，相依於 Active Directory 網域將使得部署和管理叢集更加複雜。Windows Server 2016（截至本文撰寫為止，Windows Server 2016 尚在 Technical Preview 5 階段），已經可以不需依賴 Active Directory 網域就可以建立叢集。因此本文將介紹 SQL Server 2016 如何在 Window Server 2016 的工作群組叢集（Workgroup Clusters）環境中建立 AlwaysOn 可用性群組。
學習目標
本文將介紹在將 3 台 Windows Server 2016 工作群組的伺服器中建立工作群組叢集，並安裝 SQL Server 2016 及建立 AlwaysOn 可用性群組。
前置作業
請在 Server Manager > Local Server 點選安裝作業系統後產生隨機電腦名稱，接著 在System Properties 視窗的 Computer Name 頁籤點選 Change 修改電腦名稱及 DNS suffix，本文以 sql2016-1、sql2016-2 和 sql2016-3 做為示範所用之電腦名稱，並以 sqlpass.tw 作為 DNS suffix，設定完畢後重新開機讓變更生效。
重新開機後在每一台成員伺服器中新增 Failover Clustering 功能。
為了讓成員伺服器之間可以透過完整電腦名稱進行解析，因此需在每一台伺服器中手動修改【C:\Windows\System32\drivers\etc\hosts】檔案，相關設定如下圖所示。
執行下列 PowerShell 指令碼讓建立容錯移轉叢集的使用者擁有和內建系統管理員一樣的權限。
new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
執行結果如下圖所示：
建立容錯移轉叢集
建立容錯移轉叢集的方式有二，首先是透過 PowerShell 來建立，指令碼如下：
new-cluster -name sqlcluster -Node sql2016-1,sql2016-2,sql2016-3 -StaticAddress 10.0.0.100 -NoStorage –VAdministrativeAccessPoint DNS
執行結果如下圖所示：
使用 Failover Cluster Manager 可以看到透過上述指令碼所建立的容錯移轉叢集。
當然您也可以透過 Failover Cluster Manager 來建立容錯移轉叢集，點選 Create Cluster，瀏覽或自行輸入成員伺服器的電腦名稱後按 Add，所有伺服器都加入後按 Next。
在驗證叢集步驟，本文先選擇 No 來略過驗證叢集，正式環境建議還是選擇 Yes 會比較保險。
輸入叢集名稱及所使用的 IP 位址後按 Next。
確認相關設定無誤後按 Next。
最後按 Finish來建立容錯移轉叢集。
為了方便在任何一台成員伺服器管理其他台 SQL Server 執行個體，在每一台伺服器使用下列 PowerShell 指令碼來建立防火牆規則，允許遠端伺服器透過預設的 TCP 1433 Port 來存取資料庫引擎。
New-NetFirewallRule -DisplayName "SQL Server Database Engine" -Direction Inbound -Program "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -RemoteAddress LocalSubnet -Action Allow
執行結果如下圖所示：
SQL Server組態設定
在 SQL Server Configuration Manager 點選 SQL Server Network Configuration > Protocols for MSSQLSERVER > TCP/IP 選項，確認是否啟用 SQL Server 資料庫引擎的 TCP/IP 通訊協定。
一切準備就緒後就可以啟動 SQL Server 資料庫引擎的 AlwaysOn Availability Groups 功能，和 Active Directory 網域環境中一樣，可以在這個視窗中看到伺服器所在的叢集名稱，設定完畢後重新啟用 SQL Server 資料庫服務。
建立AlwaysOn可用性群組的前置作業
因為少了 Active Directory 網域服務來做安全性及驗證的工作，在工作群組叢集環境下建立 AlwaysOn 可用性群組還需要建立資料庫主要金鑰（Database Master Key），以及 SQL Server 之間端點溝通所需的憑證（Certificate）與端點（Endpoint）。
在任何一台伺服器開啟 SSMS，啟用 SQLCMD 功能後執行下列 T-SQL 指令碼：
:connect sql2016-1
USE master
GO
-- 建立資料庫主要金鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Initial0'
GO
-- 建立憑證
CREATE CERTIFICATE AlwaysOnAGCertNode1
WITH SUBJECT = 'AlwaysOn AG Cert Node 1',
START_DATE = '20160101'
GO
-- 備份憑證
BACKUP CERTIFICATE AlwaysOnAGCertNode1
TO FILE = 'c:\temp\AlwaysOnAGCertNode1.cert'
GO
--建立可用性群組端點並使用憑證進行驗證
CREATE ENDPOINT AG_Endpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE AlwaysOnAGCertNode1,
ROLE = ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO
:connect sql2016-2
USE master
GO
-- 建立資料庫主要金鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Initial0'
GO
-- 建立憑證
CREATE CERTIFICATE AlwaysOnAGCertNode2
WITH SUBJECT = 'AlwaysOn AG Cert Node 2',
START_DATE = '20160101'
GO
-- 備份憑證
BACKUP CERTIFICATE AlwaysOnAGCertNode2
TO FILE = 'c:\temp\AlwaysOnAGCertNode2.cert'
GO
--建立可用性群組端點並使用憑證進行驗證
CREATE ENDPOINT AG_Endpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE AlwaysOnAGCertNode2,
ROLE = ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO
:connect sql2016-3
USE master
GO
-- 建立資料庫主要金鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Initial0'
GO
-- 建立憑證
CREATE CERTIFICATE AlwaysOnAGCertNode3
WITH SUBJECT = 'AlwaysOn AG Cert Node 3',
START_DATE = '20160101'
GO
-- 備份憑證
BACKUP CERTIFICATE AlwaysOnAGCertNode3
TO FILE = 'c:\temp\AlwaysOnAGCertNode3.cert'
GO
--建立可用性群組端點並使用憑證進行驗證
CREATE ENDPOINT AG_Endpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE AlwaysOnAGCertNode3,
ROLE = ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO
備份的憑證會放在各個成員伺服器中的 c:\temp 資料夾，請將各自的憑證複製到其他台伺服器之中，以利後續匯入至每一成員伺服器所使用。
在任何一台伺服器開啟 SSMS，啟用 SQLCMD 功能後執行下列 T-SQL 指令碼，用來匯入其他伺服器的憑證並允許相對應的使用者存取可用性群組的端點。
:connect sql2016-1
-- 建立登入
CREATE LOGIN l2 WITH PASSWORD = 'Initial0'
GO
CREATE LOGIN l3 WITH PASSWORD = 'Initial0'
GO
-- 建立使用者
CREATE USER u2 FOR LOGIN l2
GO
CREATE USER u3 FOR LOGIN l3
GO
-- 匯入其他節點的憑證
CREATE CERTIFICATE AlwaysOnAGCertNode2
AUTHORIZATION u2
FROM FILE = 'c:\temp\AlwaysOnAGCertNode2.cert'
GO
CREATE CERTIFICATE AlwaysOnAGCertNode3
AUTHORIZATION u3
FROM FILE = 'c:\temp\AlwaysOnAGCertNode3.cert'
GO
-- 允許登入存取AG的端點
GRANT CONNECT ON ENDPOINT::AG_Endpoint TO l2
GO
GRANT CONNECT ON ENDPOINT::AG_Endpoint TO l3
GO
:connect sql2016-2
-- 建立登入
CREATE LOGIN l1 WITH PASSWORD = 'Initial0'
GO
CREATE LOGIN l3 WITH PASSWORD = 'Initial0'
GO
-- 建立使用者
CREATE USER u1 FOR LOGIN l1
GO
CREATE USER u3 FOR LOGIN l3
GO
-- 匯入其他節點的憑證
CREATE CERTIFICATE AlwaysOnAGCertNode1
AUTHORIZATION u1
FROM FILE = 'c:\temp\AlwaysOnAGCertNode1.cert'
GO
CREATE CERTIFICATE AlwaysOnAGCertNode3
AUTHORIZATION u3
FROM FILE = 'c:\temp\AlwaysOnAGCertNode3.cert'
GO
-- 允許登入存取AG的端點
GRANT CONNECT ON ENDPOINT::AG_Endpoint TO l1
GO
GRANT CONNECT ON ENDPOINT::AG_Endpoint TO l3
GO
:connect sql2016-3
-- 建立登入
CREATE LOGIN l2 WITH PASSWORD = 'Initial0'
GO
CREATE LOGIN l1 WITH PASSWORD = 'Initial0'
GO
-- 建立使用者
CREATE USER u2 FOR LOGIN l2
GO
CREATE USER u1 FOR LOGIN l1
GO
-- 匯入其他節點的憑證
CREATE CERTIFICATE AlwaysOnAGCertNode2
AUTHORIZATION u2
FROM FILE = 'c:\temp\AlwaysOnAGCertNode2.cert'
GO
CREATE CERTIFICATE AlwaysOnAGCertNode1
AUTHORIZATION u1
FROM FILE = 'c:\temp\AlwaysOnAGCertNode1.cert'
GO
-- 允許登入存取AG的端點
GRANT CONNECT ON ENDPOINT::AG_Endpoint TO l2
GO
GRANT CONNECT ON ENDPOINT::AG_Endpoint TO l1
GO
建立可用性群組資料庫
前置作業完成後接下來就是建立 AlwaysOn 可用性群組所要保護的使用者資料庫，在任何一台成員伺服器中啟用 SQLCMD 並執行下列 T-SQL 指令碼，就會連線到 SQL2016-1 建立資料庫及測試用的資料表，並進行完整資料庫備份，如此一來就達成將資料庫加入可用性群組的必要條件（完整復原模式與執行過完整資料庫備份）。
:connect sql2016-1
USE master
GO
-- 建立資料庫
CREATE DATABASE MyDB
GO
-- 切換資料庫
USE MyDB
GO
-- 建立資料表
CREATE TABLE t1
(
c1 int,
c2 int
)
GO
-- 進行資料庫完整備份
USE master
GO
BACKUP DATABASE MyDB TO DISK = 'c:\temp\MyDBFull.bak'
GO
建立可用性群組
以下列 T-SQL 指令碼建立 AlwaysOn 可用性群組及可用性群組接聽程式。
:Connect SQL2016-1
IF (SELECT state FROM sys.endpoints WHERE name = N'AG_Endpoint') <> 0
BEGIN
ALTER ENDPOINT [AG_Endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[AG_Endpoint] TO [NT Service\MSSQLSERVER]
GO
:Connect SQL2016-1
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect SQL2016-2
IF (SELECT state FROM sys.endpoints WHERE name = N'AG_Endpoint') <> 0
BEGIN
ALTER ENDPOINT [AG_Endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[AG_Endpoint] TO [NT Service\MSSQLSERVER]
GO
:Connect SQL2016-2
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect SQL2016-3
IF (SELECT state FROM sys.endpoints WHERE name = N'AG_Endpoint') <> 0
BEGIN
ALTER ENDPOINT [AG_Endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[AG_Endpoint] TO [NT Service\MSSQLSERVER]
GO
:Connect SQL2016-3
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect SQL2016-1
USE [master]
GO
CREATE AVAILABILITY GROUP [MyAG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)
FOR DATABASE [MyDB]
REPLICA ON N'SQL2016-1' WITH (ENDPOINT_URL = N'TCP://sql2016-1.sqlpass.tw:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'SQL2016-2' WITH (ENDPOINT_URL = N'TCP://sql2016-2.sqlpass.tw:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'SQL2016-3' WITH (ENDPOINT_URL = N'TCP://sql2016-3.sqlpass.tw:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO
:Connect SQL2016-1
USE [master]
GO
ALTER AVAILABILITY GROUP [MyAG]
ADD LISTENER N'AGL' (
WITH IP
((N'10.0.0.110', N'255.255.255.0')
)
, PORT=1433);
GO
:Connect SQL2016-2
ALTER AVAILABILITY GROUP [MyAG] JOIN;
GO
:Connect SQL2016-3
ALTER AVAILABILITY GROUP [MyAG] JOIN;
GO
GO
由於上一個步驟我們是將要加入可用性群組的資料庫建立在 SQL2016-1 伺服器，且將可用性群組的主要複本設定為 SQL2016-1，因此執行完上述指令碼後從 Object Explorer 可以看到 SQL2016-2 和 SQL2016-3 尚未存在可用性群組資料庫 MyDB，且可用性群組資料庫節點下可看到 MyDB 出現驚嘆號。
手動初始化次要複本
若您使用 AlwaysOn 可用性群組精靈建立可用性群組，預設選項是會幫您初始化次要複本，亦即對主要複本的資料庫進行完整備份及交易記錄備份，放在指定的共享路徑之中，次要複本再去共享路徑將資料庫還原。由於本文示範的是略過初始化因此最後一個步驟需要自行在次要複本將資料庫還原，使得可用性資料庫可以順利加入可用性群組。
在還原資料庫前必須先將 SQL2016-1的C:\temp 路徑中的完整資料庫備份媒體集（MyDBFull.bak）複製到 SQL2016-2 和 SQL2016-3 的相同路徑，啟用 SQLCMD 模式後在任何一台成員伺服器中執行下列 T-SQL 指令碼：
:connect sql2016-2
RESTORE DATABASE MyDB FROM DISK = 'c:\temp\MyDBFull.bak' WITH NORECOVERY
GO
-- 將資料庫加入至可用性群組
ALTER DATABASE MyDB SET HADR AVAILABILITY GROUP = MyAG
GO
:connect sql2016-3
RESTORE DATABASE MyDB FROM DISK = 'c:\temp\MyDBFull.bak' WITH NORECOVERY
GO
-- 將資料庫加入至可用性群組
ALTER DATABASE MyDB SET HADR AVAILABILITY GROUP = MyAG
GO
完成後從可用性群組儀表板查看 AlwaysOn 可用性群組的狀態，可以看到如下圖所示的結果。
結論
經由本文的介紹，相信您已經學會如何在沒有 Active Directory 環境下建立工作群組叢集，以及在工作群組叢集環境中建立 SQL Server AlwaysOn 可用性群組，對於混合網域環境和工作群組環境或是雲端環境部署可用性群組時將更加便利。