設定 Always On 可用性群組中的複寫散發資料庫

文章
04/06/2023

適用於：SQL Server

本文說明如何在 Always On 可用性群組 (AG) 中設定 SQL Server 複寫散發資料庫。

SQL Server 2017 CU6 和 SQL Server 2016 SP2-CU3 透過下列機制引進 AG 中複寫散發資料庫的支援：

散發資料庫 AG 需要有接聽程式。發行者新增散發者時，會使用接聽程式名稱作為散發者名稱。
所建立的複寫作業是將接聽程式名稱當成散發者名稱。複寫快照集、記錄讀取器與在散發伺服器上建立的散發代理程式 (推播訂用帳戶) 作業會再散發 DB 之 AG 的所有次要複本上建立。

注意

推播訂用帳戶的散發代理程式作業會在訂閱者伺服器上建立而非在散發伺服器上建立。

新的作業會監視散發資料庫的狀態 (AG 中的主要或次要)，以及根據散發資料庫狀態來停用或啟用複寫作業。

根據下面所述的步驟在 AG 中設定散發資料庫之後，可以在散發資料庫 AG 容錯移轉之前和之後正確地執行複寫組態和執行階段作業。

支援的案例

設定要包含在 AG 中的散發資料庫。
在 AG 容錯移轉之前和之後設定複寫 (例如發行集和訂閱)。
在容錯移轉之前和之後運作的複寫作業。
在散發資料庫位於 AG 時移除散發者和發行者的複寫。
新增或移除現有散發資料庫 AG 中的節點。
散發者可以有多個散發資料庫。每個散發資料庫都可以在自己的 AG 中，而且不可以在任何 AG 中。多個散發資料庫可以共用 AG。
發行者和散發者需要位在不同的 SQL Server 執行個體上。
如果裝載散發資料庫之可用性群組的接聽程式設定為使用非預設連接埠，則必須為接聽程式和非預設連接埠設定別名。

限制或排除

不支援本機散發者 (發行者伺服器也是散發者)。發行者和散發者必須是不同的 SQL Server 執行個體上。這些執行個體可以裝載在相同的節點集上。不支援本機散發者的原因如下：
- 如果在本機設定散發者，您無法使用可用性群組接聽程式將流量路由至散發者，而這會導致複寫代理程式在容錯移轉之後失敗。
- 如果設定了本機散發者，然後散發者可用性群組容錯移轉至原始次要複本，則散發者的發行者連線會從本機變更為遠端，而這會導致複寫預存程序和代理程式失敗。
不支援 Oracle 發行者。
合併式複寫不受到支援。
不支援與立即或已排入佇列更新訂閱者的異動複寫。
SQL Server 2019 (15.x) CU 17 之前的版本不支援對等複寫
所有裝載散發資料庫複本的 SQL Server 2017 執行個體都必須是 SQL Server 2017 CU 6 或更新版本。
所有裝載散發資料庫複本的 SQL Server 2016 執行個體都必須是 SQL Server 2016 SP2-CU3 或更新版本。
所有裝載散發資料庫複本的 SQL Server 執行個體都必須是相同的版本，但進行升級時的短時間範圍期間除外。
散發資料庫必須處於完整復原模式。
若要復原，以及允許交易記錄截斷，請設定完整和交易記錄備份。
散發資料庫 AG 必須設定接聽程式。
散發資料庫 AG 中的次要複本可以同步或非同步。建議並偏好使用同步模式。
不支援雙向異動複寫。
當散發資料庫新增至可用性群組時，SSMS 不會將散發資料庫顯示為同步處理中/已同步處理。

注意

在次要複本上執行任何複寫預存程序 (例如：sp_dropdistpublisher、sp_dropdistributiondb、sp_dropdistributor、sp_adddistributiondb、sp_adddistpublisher) 之前，請確定複本已完全同步。
散發資料庫 AG 中的所有次要複本應該都可供讀取。如果無法讀取次要複本，即無法存取特定次要複本上 SQL Server Management Studio 中的散發者屬性，但複寫會繼續正常運作。
散發資料庫 AG 中的所有節點都需要使用相同的網域帳戶來執行 SQL Server Agent，而且此網域帳戶需要具有每個節點的相同權限。
如果使用 Proxy 帳戶執行任何複寫代理程式，Proxy 帳戶需要存在於散發資料庫 AG 中的每個節點，並且具有每個節點的相同權限。
變更所有參與散發資料庫 AG 的複本中的散發者或散發資料庫屬性。
在所有參與散發資料庫 AG 的複本中，透過 msdb 預存程序或 SQL Server Management Studio 進行複寫作業變更。
如果使用任何代理程式的自訂設定檔，則必須使用 sp_add_agent_profile 程序在所有次要複本手動建立。設定檔在所有複本都必須具有相同標識碼。
在發行者上設定散發者需要使用指令碼來完成。無法使用複寫精靈。支援將複寫精靈和屬性工作表用於其他用途。
設定散發資料庫的 AG 只能透過指令碼完成。
在 AG 中設定散發資料庫需要是新的複寫組態。不支援將現有散發資料庫切換為 AG。而且從散發資料庫中取出 AG 之後，就無法再當成有效的散發資料庫運作，應該予以卸除。

組態結構

本文中的範例使用下列伺服器名稱和設定。

DIST1、DIST2、DIST3 是散發者伺服器；
PUB 是發行者伺服器；
形成散發資料庫 AG 之後，接聽程式名稱是 DISTLISTENER；
DIST1 會是散發資料庫 AG 的初始主要複本。

設定散發者、散發資料庫和發行者

此範例會設定新的散發者和發行者，並在 AG 中放置散發資料庫。

散發者工作流程

使用 sp_adddistributor @@servername，將 DIST1、DIST2、DIST3 設定為散發者。透過 @password，指定 distributor_admin 的密碼。 DIST1、DIST2 和 DIST3 的 @password 應該相同。
使用 sp_adddistributiondb 在 DIST1 上建立散發資料庫。散發資料庫的名稱是 distribution。將 distribution 資料庫的復原模式從簡單變更為完整。
使用 DIST1、DIST2 和 DIST3 上的複本，建立 distribution 資料庫的 AG。最好所有複本都同步。設定可供讀取或允許讀取的次要複本。此時，散發資料庫是 AG、DIST1 是主要複本，而 DIST2 和 DIST3 是次要複本。
針對 AG，設定名為 DISTLISTENER 的接聽程式。
若要復原，以及允許交易記錄截斷，請設定完整和交易記錄備份。

在 DIST2 和 DIST3 上，執行：

EXEC sys.sp_adddistributiondb @database = 'distribution';

若要在 DIST1 上將 PUB 新增為發行者，請執行：
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
@working_directory 的值應該是與 DIST1、DIST2 和 DIST3 無關的網路路徑。

在 DIST2 和 DIST3 上，如果複本可讀取為次要複本，請執行：

EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';

如果複本無法讀取為次要複本，請執行容錯移轉，讓複本變成主要複本，然後執行

EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';

@working_directory 的值應該與上一個步驟相同。

發行者工作流程

若要將 distribution 資料庫 AG 接聽程式新增為散發者，請在 PUB 上執行：

EXEC sys.sp_adddistributor @distributor = 'DISTLISTENER', @password = '<distributor_admin password>' ;

@password 的值應該是在散發者工作流程中設定散發者時所指定的值。

移除散發者和發行者

此範例會在散發資料庫位於 AG 時移除發行者和散發者。

發行者工作流程

在 PUB 上，卸除此發行者的所有訂閱和發行集，然後呼叫 sp_dropdistributor。

散發者工作流程

在此範例中，DIST1 是 distribution 資料庫 AG 的目前主要複本。 DIST2 和 DIST3 是次要複本。

在 DIST2 和 DIST3 上，執行：

EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;

在 DIST1 上，執行：

EXEC sys.sp_dropdistpublisher @publisher = 'PUB';

刪除 AG。
在 DIST2 和 DIST3 上，使用復原還原資料庫，以將 distribution 資料庫變更為 read_write 模式。
```
RESTORE DATABASE [distribution] WITH RECOVERY, KEEP_REPLICATION;
```

若要卸除 distribution 資料庫，以及保留快照集目錄，請執行：

EXEC sys.sp_dropdistributiondb @database = 'distribution' , @former_ag_secondary = 1;

此程序會移除這個複本上的所有懸空作業。

若要卸除 DIST1 上的 distribution 資料庫，請執行：
```
EXEC sys.sp_dropdistributiondb @database = 'distribution';
```
如果 AG 中沒有其他散發資料庫，則請在 DIST1、DIST2 和 DIST3 上執行 sp_dropdistributor。

將複本新增至散發資料庫 AG

此範例會將新的散發者新增至具有 AG 中散發資料庫的現有複寫組態。在此範例中，現有散發資料庫是在 AG 中。 DIST1 和 DIST2 是散發者、distribution 是 AG 中的散發資料庫，而 PUB 是發行者。將 DIST3 新增為 AG 中的複本。

散發者工作流程

DIST3 應該透過 sp_adddistributor @@servername 設定為散發者。應該透過 @password 參數指定 distributor_admin 的密碼。密碼應該與針對 DIST1 和 DIST2 所指定的密碼相同。
將 DIST3 新增至目前散發資料庫的 AG。

在 DIST3 上，執行：

EXEC sys.sp_adddistributiondb @database = 'distribution';

在 DIST3 上，如果複本可讀取為次要複本，請執行：
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
如果複本無法讀取為次要複本，請執行容錯移轉，讓複本變成主要複本，然後執行：
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
@working_directory 的值應該與針對 DIST1 和 DIST2 所指定的密碼相同。
在 DIST3 上，您必須重建與訂閱者的連結伺服器。

移除散發資料庫 AG 中的複本

此範例會移除目前散發資料庫 AG 中的散發者，而散發資料庫 AG 中的其餘複本不受影響。在此範例中，散發資料庫是在 AG 中。 DIST1、DIST2 和 DIST3 是散發者、distribution 是 AG 中的散發資料庫，而 PUB 是發行者。移除 AG 中的 DIST3。

散發者工作流程

請確定 DIST3 是 distribution 資料庫 AG 的次要複本。
移除 distribution 資料庫 AG 中的 DIST3。
在 DIST3 上，使用復原還原資料庫，以將 distribution 資料庫變更為 read_write 模式。例如，執行下列命令：
```
RESTORE DATABASE distribution WITH RECOVERY, KEEP_REPLICATION;
```

若要移除 DIST3 上的所有孤立作業，請執行：

EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;

在 DIST3 上，執行：

EXEC sys.sp_dropdistributiondb @database = 'distribution', @former_ag_secondary = 1;

在 DIST3 上，執行：
```
EXEC sys.sp_dropdistributor;
```

移除散發資料庫 AG 中的發行者

此範例會移除散發者的目前散發資料庫 AG 中的發行者，而此散發資料庫 AG 所服務的其餘發行者不受影響。在此範例中，現有組態於 AG 中有散發資料庫。 DIST1、DIST2 和 DIST3 是散發者、distribution 是 AG 中的散發資料庫，而 PUB1 和 PUB2 是 distribution 資料庫所服務的發行者。此範例會移除這些散發者中的 PUB1。

發行者工作流程

在 PUB1 上，卸除此發行者的所有訂閱和發行集，然後呼叫 sp_dropdistributor。

散發者工作流程

DIST1 是 distribution 資料庫 AG 的目前主要複本。

在 DIST2 和 DIST3 上，執行：

EXEC sys.sp_dropdistpublisher @publisher = 'PUB1',  @no_checks = 1;

在 DIST1 上，執行：

EXEC sys.sp_dropdistpublisher @publisher = 'PUB1';

此時，在 DIST2 或 DIST3 上，可能有與 PUB1 相關的孤立作業。只要容錯移轉至 DIST2 和 DIST3，Monitor and sync replication agent jobs 作業就會移除與 PUB1 之所有發行集相關的孤立作業。

新增訂用帳戶

此範例是有關在散發者之間適當地設定訂閱者資訊。此範例會新增訂閱者。 DIST1 是 AG 中散發資料庫的目前主要複本，而 DIST2 和 DIST3 是 AG 中散發資料庫的次要複本。訂閱者名稱是 SUB。

發行者工作流程

在 PUB 上，新增訂用帳戶，就像一般對訂閱者 SUB 執行地一樣。

散發者工作流程

在 DIST2 和 DIST3 上，如果以前未向 DIST2 或 DIST3 註冊 'SUB' 的已連結伺服器，則請予以新增。以下是已連結伺服器建立的範例 TSQL -

EXEC master.dbo.sp_addlinkedserver@server =N'SUB', @srvproduct=N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N'SUB', @useself = N'True',@locallogin = NULL,@rmtuser =N ULL,@rmtpassword = NULL;

新增提取訂閱

訂閱者工作流程

若要使用 AG 中的散發資料庫新增發行集的提取訂閱，請使用 sp_addpullsubscription_agent 之 @distributor 參數中的 AG 接聽程式名稱。

AG 中的範例 T-SQL 建立散發資料庫

下列指令碼會啟用可用性群組中的散發資料庫。

--- WorkFlow to Enable Distribution Database In AG.

-- SECTION 1 ---- CONFIGURE THE DISTRIBUTOR SERVERS

-- Step1 - Configure the Distribution DB nodes (AG Replicas) to act as a distributor
:Connect SQLNode1
EXEC [sys].[sp_adddistributor]
    @distributor = @@SERVERNAME,
    @password = 'Pass@word1';
GO
:Connect SQLNode2
EXEC [sys].[sp_adddistributor]
    @distributor = @@SERVERNAME,
    @password = 'Pass@word1';
GO

-- Step2 - Configure the Distribution Database
:Connect SQLNode1
USE [master];
EXEC [sys].[sp_adddistributiondb]
    @database = 'DistributionDB',
    @security_mode = 1;
GO
ALTER DATABASE [DistributionDB] SET RECOVERY FULL;
GO
BACKUP DATABASE [DistributionDB] TO DISK = 'NUL';
GO
-- Step 3 - Create AG for the Distribution DB.
:Connect SQLNode1
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

:Connect SQLNode2
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

:Connect SQLNode1
-- Create the Availability Group
CREATE AVAILABILITY GROUP [DistributionDB_AG]
FOR DATABASE [DistributionDB]
REPLICA ON
N'SQLNode1' WITH (ENDPOINT_URL = N'TCP://SQLNode1.contoso.com:5022', 
	 FAILOVER_MODE = AUTOMATIC, 
	 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	 BACKUP_PRIORITY = 50, 
	 SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
	 SEEDING_MODE = AUTOMATIC),
N'SQLNode2' WITH (ENDPOINT_URL = N'TCP://SQLNode2.contoso.com:5022', 
	 FAILOVER_MODE = AUTOMATIC, 
	 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	 BACKUP_PRIORITY = 50, 
	 SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
	 SEEDING_MODE = AUTOMATIC);
GO


:Connect SQLNode2
ALTER AVAILABILITY GROUP [DistributionDB_AG] JOIN;
GO  
ALTER AVAILABILITY GROUP [DistributionDB_AG] GRANT CREATE ANY DATABASE;
GO

--STEP4 - Create the Listener for the Availability Group. This is very important.
:Connect SQLNode1

USE [master]
GO
ALTER AVAILABILITY GROUP [DistributionDB_AG]
ADD LISTENER N'DistributionDBList' (
WITH IP
((N'10.0.0.8', N'255.255.255.0')) , PORT=1500);
GO

-- STEP 5 - Enable SQLNode2 also as a Distributor
:Connect SQLNode2
EXEC sys.sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;
GO

--STEP 6 - On all Distributor Nodes Configure the Publisher Details 
:Connect SQLNode1
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
	@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO
:Connect SQLNode2
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
	@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO

-- SECTION 2 ---- CONFIGURE THE PUBLISHER SERVER
:Connect SQLNode4
EXEC sys.sp_adddistributor @distributor = 'DistributionDBList', -- Listener for the Distribution DB.	
	@password = 'Pass@word1';
GO

-- SECTION 3 ---- CONFIGURE THE SUBSCRIBERS 
-- On Publisher, create the publication as one would normally do.
-- On the Secondary replicas of the Distribution DB, add the Subscriber as a linked server.
:Connect SQLNode2
EXEC master.dbo.sp_addlinkedserver @server = N'SQLNODE5', @srvproduct = N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLNODE5', @useself = N'True',
	@locallogin = NULL,@rmtuser = NULL,@rmtpassword = NULL;

發行資料和資料庫物件
 保護散發者
 檢視及修改散發者和發行者屬性
 停用發行和散發
 啟用用於複寫的資料庫 (SQL Server Management Studio)

分享方式：

設定 Always On 可用性群組中的複寫散發資料庫

支援的案例

限制或排除

組態結構

設定散發者、散發資料庫和發行者

散發者工作流程

發行者工作流程

移除散發者和發行者

發行者工作流程

散發者工作流程

將複本新增至散發資料庫 AG

散發者工作流程

移除散發資料庫 AG 中的複本

散發者工作流程

移除散發資料庫 AG 中的發行者

發行者工作流程

散發者工作流程

新增訂用帳戶

發行者工作流程

散發者工作流程

新增提取訂閱

訂閱者工作流程

AG 中的範例 T-SQL 建立散發資料庫

意見反映

更多資源

分享方式：

設定 Always On 可用性群組中的複寫散發資料庫

支援的案例

限制或排除

組態結構

設定散發者、散發資料庫和發行者

散發者工作流程

發行者工作流程

移除散發者和發行者

發行者工作流程

散發者工作流程

將複本新增至散發資料庫 AG

散發者工作流程

移除散發資料庫 AG 中的複本

散發者工作流程

移除散發資料庫 AG 中的發行者

發行者工作流程

散發者工作流程

新增訂用帳戶

發行者工作流程

散發者工作流程

新增提取訂閱

訂閱者工作流程

AG 中的範例 T-SQL 建立散發資料庫

相關內容

意見反映

更多資源