SQL Server Always On Availability Group 如何加入新的複本 (Add New Replica)

2024-01-29

筆記 SQL Server 如何設定 Always On Availability Group (可用性群組) 如何加入新的複本，來增加更多次要複本，強化容錯移轉的韌性。

SQL Server Logo

說明

已經依據 SQL Server Always On Availability Group 可用性群組安裝筆記 完成 AG 的設定，設定的當下也有兩個複本在服務，保持高可用的狀態。

一段時間後想要加入新的複本該如何進行？一個方式是拆掉原本的可用性群組 (Availabilit Group) 重新建立並且包含三個節點，但這樣會因為 Listener 的移除，造成服務暫時中斷。

本文要介紹的是第二個方式，使用 GUI 的方式加入新的複本，同時也說明實務處理上碰到的問題以及對應的處理方式。

第一步先前往 AG 的主要複本，從 GUI 介面找到「加入複本」。

接著要連結可用性群組上其他的複本。

再來就可以加入新的複本，可用性模式請選「同步認可」，經實測不會影響作用中的主要複本的寫入。

在 Listener 的設定上，選擇不要建立 (因為本來就已經有 Listner 😅)

接下來如同加入可用性資料庫的方式，依序等待從主要複本將資料庫備份與還原到新的次要複本。

而從次要複本的資料庫伺服器去檢視，會發現所有的資料庫都停留在「正在還原」的狀態。

而從可用性群組的 Dashboard 去檢視，會觀察到資料庫都處於「未進行同步處理」的狀態，理想應該是要再完成新複本的加入後都變成「已同步處理」。

作業到這邊，已經為新的次要複本還原好所有可用性群組資料庫，但因為資料庫都處在「未進行同步處理」的狀態，必須設法解決。

按照常理推想，應該只要在新的複本的可用性資料庫，使用進行資料庫移動就可以，但無論怎麼嘗試都會發生以下錯誤訊息：

「資料庫 "DatabaseName" 的遠端副本並未向前復原至包含在資料庫記錄本機副本中的時間點。 (Microsoft SQL Server, 錯誤: 1412)」

而經過一番努力 💦 發現只要手動將主要複本的資料庫備份並且再次還原到新的次要複本上，就可以加入可用性資料庫成功。

作業順序如下：

  1. Backup Database From Primary Replica
DECLARE @dbname VARCHAR(500) = 'DatabaseName';
DECLARE @BackupPath NVARCHAR(500) = N'\\Backupfile\Sharefolder\' + @dbname + '.bak';

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 
    'BACKUP DATABASE ' + QUOTENAME(@dbname) + 
    ' TO DISK = ''' + @BackupPath + ''' ' +
    ' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N''Fullbackup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10';

EXEC sp_executesql @SQL;

GO
  1. Restore Database to New Replica
DECLARE @DBNAME varchar(500) = 'DatabaseName';
DECLARE @BackupPath NVARCHAR(500) = N'\\Backupfile\Sharefolder\' + @DBNAME + '.bak';

USE [master]

-- Set the database to SINGLE_USER mode
DECLARE @SqlCmd NVARCHAR(MAX);
SET @SqlCmd = N'ALTER DATABASE ' + QUOTENAME(@DBNAME) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE';
-- EXEC(@SqlCmd);

-- Restore the database
SET @SqlCmd = N'RESTORE DATABASE ' + QUOTENAME(@DBNAME) + ' FROM DISK = @BackupPath WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5, NORECOVERY';
EXEC sp_executesql @SqlCmd, N'@BackupPath NVARCHAR(500)', @BackupPath;

-- Set the database back to MULTI_USER mode
SET @SqlCmd = N'ALTER DATABASE ' + QUOTENAME(@DBNAME) + ' SET MULTI_USER';
  1. Setting Database join to AG In New Replica
GO
declare @dbname varchar(500) = 'DatabaseName';
declare @agname varchar(500) = 'AvailabilityGroupName';
use master
-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier 
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes 

if (serverproperty('IsHadrEnabled') = 1)
	and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
	and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = @agname
	select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
	while @conn <> 1 and @count > 0
	begin
		set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
		if @conn = 1
		begin
			-- exit loop when the replica is connected, or if the query cannot find the replica status
			break
		end
		waitfor delay '00:00:10'
		set @count = @count - 1
	end
end
end try
begin catch
	-- If the wait loop fails, do not stop execution of the alter database statement
end catch

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'ALTER DATABASE ' + @dbname + ' SET HADR AVAILABILITY GROUP = ' + @agname;

EXEC sp_executesql @SQL;

GO

當然要為每一個可用性群組中的資料庫都做這樣的動作太繁瑣，在 資料處理愛好者俱樂部 以及直接撰寫 Python Script 的協助，最後將所有的備份以及還原動作構成數支 tsql.script 檔案，搭配 sqlcmd 的方式批次執行解決加入新複本的需求。

sqlcmd -S primaryReplicaserver.sdwh.dev -d master -E -i database1_backup.sql
sqlcmd -S newReplicaserver.sdwh.dev -d master -E -i database1_restore.sql

以上就是處理 Availability Group 加入新複本的方式，流程當中應該還有許多可以精進的地方，例如明明已經透過精靈引導還原一次，但為什麼最後還需要手動批次的再次還原，才能夠解決資料庫 「未進行同步處理」的狀態的問題，就留待後續持續精進，改善本筆記的作法 😀

相關連結

SQL Server Integrated Service 初探

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記

#Database #SQLServer