SQL Server Always On Availability Group 可用性群組安裝筆記

2021-07-05

筆記 SQL Server 如何設定 Always On Availability Group (可用性群組)

說明

前置作業

需要準備兩台資料庫伺服器 (SQL Server 2012+，版號相同)
需要一組網域帳戶作為 Service Account
需要 Active Directory 網域環境
兩台資料庫伺服器需要建置為同 Windows Cluster (容錯叢集) 環境
作業過程中會需要網域管理者的權限新增電腦物件（建立叢集 Cluster, 建立 Availability Group Listener

準備資料庫伺服器

安裝 SQL Server 的步驟

另需要使用 SQL Server 組態管理員 將 SQL Server 及 SQL Server Agent 的登入帳號調整為所準備的網域帳戶，第二台資料庫伺服器亦須使用同樣的網域帳戶做相同的設定。

啟用服務的 Always On 可用性群組

⚠️ 需調整為網域帳戶登入身分，才能夠正常啟用。

建置容錯叢集 (Build Windows Server Failover Cluster)

Windows Server Failover Cluster 叢集設定實驗筆記 (WSFC)

如果不具有權限，可以請 AD 管理者使用較高權限的帳戶登入伺服器，接著使用 WSFC 的精靈進行叢集的建置。

叢集切換

確認目前使用的叢集所使用的資料庫伺服器以及如何由叢集進行切換。

建置可用性群組

選擇可用性資料庫

輸入可用性群組名稱，慣用命名方式是尾墜為 AG，例如 MyDatabaseAG。叢集的類型使用 Windows Server Failover Cluster。

在 SQL Server 2022 之後，可以設定重複使用系統資料庫，後續的文章再詳談這個功能的使用方式。

資料庫的復原模式必須為完整復原模式，且已經進行一次完整備份，才能夠作為可用性群組所使用的資料庫。

接著進行複本的加入，自動容錯移轉必須要勾選，勾選後可用性模式最選擇為「同步認可」。

端點預設使用 5022 port，要注意伺服器之間的防火牆是否允許。

備份喜好路徑按照預設方式設定，使用「慣用次要」。

UNC 路徑被需要讓 SQL Server Service 所使用的登入身分能夠有權限存取，才能作為自動備份與還原資料庫的中介資料夾。

建置可用性群組 Listener

需要為 Listener 指定一組 IP，此 Listener 所代表的就是該可用性群組。因此在應用程式的連線字串，應使用 Listener 的名稱作為資料庫伺服器名稱連線，當 Availability Group 的 Failover 發生時，應用程式才能對應連結到目前 Active 的資料庫伺服器。

需要有建立電腦物件的權限，可以請 AD 管理人員協助建立 AG Listener 電腦物件，再授權本機伺服器 Name$ 具有完整控制權限，作業完成後可以再移除此完整控制權限授權。

完成以上的精靈引導設定後，就會開始建立可用性群組，並且進行複本的同步，視資料庫的大小而定，這個過程可能會花費一些時間。

建置後可能會有各種問題，例如資料庫之間的同步有問題，容錯移轉無法正常，在正式完成前，反覆重建可用性群組是很有可能的除錯過程 😅

在建置的過程同樣會需要有網域管理者建立電腦物件的權限，可以預先與網域管理者溝通，先建立好 AG Listener 物件，並且對 WSFC 的帳號 CLUSTERNAME$ (注意是叢集名稱，而非各別節點)，進行完全控制的授權。

此外要注意複本伺服器之間的防火牆 (5022 port) 要開啟，最簡單的測試方式就是互相使用 SSMS 連線到對方的資料庫伺服器，確保可以連線成功。

WSFC 叢集無法使 DNS 名稱為 '' 的網路名稱資源上線。此 DNS 名稱可能已被占用或與現有的名稱服務發生衝突

以上錯誤出現於沒有正確建立 AG Listener 物件，並且對 WSFC 的帳號 CLUSTERNAME$ 進行完全控制的授權。

錯誤刪除可用性群組的處理方式

如果直接用「容錯移轉叢集管理員」從角色將可用性群組刪除，會出現例外狀況，造成無法重新建立可用性群組 😥

首先要檢查是否有連線占用 model 資料庫，如果有，可以先將連線中斷，再進行刪除可用性群組的動作。

SELECT session_id, login_name, host_name, program_name, status, database_id
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('model');

KILL ID

接著要使用 regedit 刪除以下的註冊表中所出現的可用性群組名稱，再重新建立可用性群組。

HKEY_LOCAL_MACHINE\Cluster\HADRAgNameToIDMap

無法取得資料庫 'model' 的獨佔鎖定。請稍後再重新作業。

以上錯誤出現於建立可用性群組時，有連線占用 model 資料庫，可以先將連線中斷 (不正確刪除可用性群組造成)，再進行建立可用性群組的動作。

可用性群組 '' 已經存在。此錯誤可能是之前失敗的 CREATE AVAILABILITY GROUP 或 DROP AVAILABILITY GROUP 作業所造成。

以上錯誤出現於不正確刪除可用性群組造成，需要透過 regedit 刪除註冊表中的可用性群組名稱，再重新建立可用性群組。

驗證可用性群組容錯移轉

由儀錶板 (Dashboard) 可以看到目前可用性群組使用中的資料庫伺服器(主要執行個體)，並且可以進行容錯移轉，以將主要執行個體做切換。

同步 SQL Server Agent 的作業排程

簡略說明，使用 Create Script 的方式搬遷最快。

要記得所有的複本執行都要加入 fn_hadr_is_primary_replica 的檢查，確保作用該排程的是主要複本 (因為只有主要複本能夠提供寫入等資料異動，次要複本僅能作為讀取)。

IF sys.fn_hadr_is_primary_replica('DatabaseName') = 1
	exec DatabaseName.dbo.usp_StoredProcedureName
ELSE
	SELECT '不是主要執行複本'

如果是 SSIS 的封裝執行 job，可以再之前加了一個檢查用途的 job，確保只有主要複本才會執行：

IF sys.fn_hadr_is_primary_replica('DatabaseName') = 1
BEGIN
    PRINT '確認為主要複本'
END
ELSE
BEGIN
    PRINT '執行失敗，目前負責執行排程的的複本，並非主要複本'
    RAISERROR('目前負責執行排程的的複本，並非主要複本', 16, 1);
END