將一個對等節點設定為可用性群組的一部分

從 SQL Server 2019（15.x）CU 13 開始，屬於 SQL Server Always On 可用性群組的資料庫可以作為對等節點參與點對點交易複製拓撲。本文說明如何設定此情境。

此範例中的指令碼會使用 T-SQL 預存程序。

角色和名稱

本節說明參與本文複寫拓撲之各種元素的角色和名稱。

Peer1

Node1：MyAG 可用性群組中的複本 1。
Node2：MyAG 可用性群組中的複本 2。
MyAG：你在 Node1 和 Node2 上建立的可用性群組名稱。
MyAGListenerName：可用性群組接聽程式的名稱。
Dist1：遠端發佈者執行個體的名稱。
MyDBName：資料庫名稱。
P2P_MyDBName：發行名稱。

Peer2

Node3：一台獨立伺服器，承載 SQL Server 的預設實例。
Dist2：遠端散發者執行個體的名稱。
MyDBName：資料庫名稱。
P2P_MyDBName：發行名稱。

必要條件

在個別實體或虛擬伺服器上託管可用性群組的兩個 SQL Server 執行個體。可用性群組包含一個對等資料庫。
託管另一個對等資料庫的 SQL Server 執行個體。
兩個用來裝載散發者資料庫的 SQL Server 執行個體。
所有伺服器執行個體都需要受支援版本 - 企業版本或開發人員版本。
所有伺服器執行個體都需要受支援版本 - SQL Server 2019 (15.x) CU13 或更新版本。
所有執行個體之間的足夠網路連線和頻寬。
在所有 SQL Server 執行個體上安裝 SQL Server 複寫。

若要查看是否在任何執行個體上安裝複寫，請執行下列查詢：
```
USE master;   
GO   
DECLARE @installed int;   
EXEC @installed = sys.sp_MS_replication_installed;   
SELECT @installed; 
```
注意

若要避免散發資料庫發生單點故障，請讓每個對等節點各自使用遠端散發者。

在示範或測試環境中，你可以在單一實例上配置發行資料庫。

設定散發者和遠端發佈者 (Peer1)

執行 sp_adddistributor 以設定 Dist1 的發行版本。使用 @password = 來指定遠端發行者連接到散發者時所使用的密碼。當您設定遠端散發者時，請在每個遠端發行者上使用此密碼。
```
USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist1',  
 @password = '<Strong password for distributor>';  
```

在散發者端建立散發資料庫。

USE master;
GO  
EXEC sys.sp_adddistributiondb  
 @database = 'distribution',  
 @security_mode = 1;

將 Node1 和 Node2 設定為遠端發行者。

@security_mode 會決定複寫代理程式如何連線到目前的主要伺服器。
- 1 = Windows 驗證。
- 0 = SQL Server 驗證。需要 @login 和 @password。指定的登入名和密碼必須在每個次要複本上有效。
注意

若任何修改過的複製代理程式在非發行商電腦上運行，請使用 Windows 驗證來連接主主機，複本主機間通訊需使用 Kerberos 認證。使用 SQL Server 登入，連接目前主帳號不需要 Kerberos 認證。
```
USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node1',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir',  
 @security_mode = 1
USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node2',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir',  
 @security_mode = 1
```

在原始發行者 (Node1) 上設定發行者

設定遠端分發的原始發行者 (Node1)。請為 @password 指定與在散發端執行 sp_adddistributor 以設定散發時所使用的相同值。
```
exec sys.sp_adddistributor  
@distributor = 'Dist1',  
@password = '<Password used when running sp_adddistributor on distributor server>' 
```

啟用資料庫進行複寫。

USE master;  
GO  
EXEC sys.sp_replicationdboption  
 @dbname = 'MyDBName',  
 @optname = 'publish',  
 @value = 'true';

將次要複本主機設定為複寫發行者 (Node2)

在每個次要複本主機上，設定分發。為 sp_adddistributor 指定與您在散發者端執行 @password 以設定散發時所使用的相同值。

EXEC sys.sp_adddistributor  
   @distributor = 'Dist1',  
   @password = '<Password used when running sp_adddistributor on distributor server>'

讓資料庫成為可用性群組的一部分，並建立接聽程式 (Peer1)

在預期的主要複本上，建立可用性群組，將資料庫作為成員資料庫。
建立可用性群組的 DNS 接聽程式。複寫代理程式會使用接聽程式連接到目前的主要複本。下列範例會建立名為 MyAGListername 的接聽程式。
```
ALTER AVAILABILITY GROUP 'MyAG'
ADD LISTENER 'MyAGListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));   
```
注意

在前述文字中，方括號內[ ... ]的資訊為可選。用它來指定 TCP 埠的非預設值。不要包含括號。

將原始發行者重新導向至 AG 接聽器名稱 (Peer1)

在 Peer1 的散發者上，將原始發行者重新導向至 AG 接聽程式名稱。

USE distribution;   
GO   
EXEC sys.sp_redirect_publisher    
@original_publisher = 'Node1',   
@publisher_db = 'MyDBName',   
@redirected_publisher = 'MyAGListenerName,<port>';

注意

在前面的指令碼中，,<port> 是可選的。只有在使用非預設埠時才需要。不要包含角括號 <>。

在原始發行者上建立點對點發行 (Peer1) - Node1

以下腳本建立 Peer1 的發佈。

exec master..sp_replicationdboption  @dbname=  'MyDBName'    
        ,@optname=  'publish'    
        ,@value=  'true'   

GO 

DECLARE @publisher_security_mode smallint = 1 
EXEC [MyDBName].dbo.sp_addlogreader_agent @publisher_security_mode = @publisher_security_mode 

GO 

DECLARE @allow_dts nvarchar(5) = N'false' 
DECLARE @allow_pull nvarchar(5) = N'true' 
DECLARE @allow_push nvarchar(5) = N'true' 
DECLARE @description nvarchar(255) = N'Peer-to-Peer publication of database MyDBName from Node1' 
DECLARE @enabled_for_p2p nvarchar(5) = N'true' 
DECLARE @independent_agent nvarchar(5) = N'true' 
DECLARE @p2p_conflictdetection nvarchar(5) = N'true' 
DECLARE @p2p_originator_id int = 100 
DECLARE @publication nvarchar(256) = N'P2P_MyDBName' 
DECLARE @repl_freq nvarchar(10) = N'continuous' 
DECLARE @restricted nvarchar(10) = N'false' 
DECLARE @status nvarchar(8) = N'active' 
DECLARE @sync_method nvarchar(40) = N'NATIVE' 
EXEC [MyDBName].dbo.sp_addpublication @allow_dts = @allow_dts, @allow_pull = @allow_pull, @allow_push = @allow_push, @description = @description, @enabled_for_p2p = @enabled_for_p2p,  
@independent_agent = @independent_agent, @p2p_conflictdetection = @p2p_conflictdetection, @p2p_originator_id = @p2p_originator_id, @publication = @publication, @repl_freq = @repl_freq, @restricted = @restricted,  
@status = @status, @sync_method = @sync_method 
go 

DECLARE @article nvarchar(256) = N'tbl0' 
DECLARE @description nvarchar(255) = N'Article for dbo.tbl0' 
DECLARE @destination_table nvarchar(256) = N'tbl0' 
DECLARE @publication nvarchar(256) = N'P2P_MyDBName' 
DECLARE @source_object nvarchar(256) = N'tbl0' 
DECLARE @source_owner nvarchar(256) = N'dbo' 
DECLARE @type nvarchar(256) = N'logbased' 
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, @source_owner = @source_owner, @type = @type 
go 

DECLARE @article nvarchar(256) = N'tbl1' 
DECLARE @description nvarchar(255) = N'Article for dbo.tbl1' 
DECLARE @destination_table nvarchar(256) = N'tbl1' 
DECLARE @publication nvarchar(256) = N'P2P_MyDBName' 
DECLARE @source_object nvarchar(256) = N'tbl1' 
DECLARE @source_owner nvarchar(256) = N'dbo' 
DECLARE @type nvarchar(256) = N'logbased' 
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object,  
@source_owner = @source_owner, @type = @type 
GO

讓點對點發佈與可用性群組（Peer1）相容

在原始發行者 (Node1) 上，執行下列指令碼，讓發行集與可用性群組相容：

USE MyDBName
GO
DECLARE @publication sysname = N'P2P_MyDBName' 
DECLARE @property sysname = N'redirected_publisher' 
DECLARE @value sysname = N'MyAGListenerName,<port>' 
EXEC MyDBName..sp_changepublication @publication = @publication, @property = @property, @value = @value 
GO

注意

在前面的指令碼中，,<port> 是可選的。只有在使用非預設埠時才需要。不要包含角括號 <>。

完成前述步驟後，可用群組即可參與點對點拓撲。後續步驟會設定要參與的獨立 SQL Server 執行個體 (Peer2)。

設定散發者與遠端發行者 (Peer2)

在發佈端設定發佈。

USE master;   
GO   
EXEC sys.sp_adddistributor   
 @distributor = 'Dist2',   
 @password = '**Strong password for distributor**';

在散發者端建立散發資料庫。

USE master;   
GO   
EXEC sys.sp_adddistributiondb   
 @database = 'distribution',   
 @security_mode = 1;

在發行商 Dist2 上設定 Node3 為遠端發佈者。

USE master;   
GO   
EXEC sys.sp_adddistpublisher   
 @publisher = 'Node3',   
 @distribution_db = 'distribution',   
 @working_directory = '\\MyReplShare\WorkingDir2',   
 @security_mode = 1

設定發行者 (Peer2)

在 Node3 上，設定遠端發佈。

exec sys.sp_adddistributor  
@distributor = 'Dist2',  
@password = '<Password used when running sp_adddistributor on distributor server>'

在 Node3 上，啟用資料庫進行複寫。

USE master;  
GO  
EXEC sys.sp_replicationdboption  
    @dbname = 'MyDBName',  
    @optname = 'publish',  
    @value = 'true';

建立點對點發行 (Peer2)

在 Node3 上，執行以下指令建立點對點發佈。

exec master..sp_replicationdboption  @dbname=  'MyDBName'   
        ,@optname=  'publish'   
        ,@value=  'true'  
go
DECLARE @publisher_security_mode smallint = 1
EXEC [MyDBName].dbo.sp_addlogreader_agent @publisher_security_mode = @publisher_security_mode
go

-- Make sure that the value for @p2p_originator_id is different from Peer1.
DECLARE @allow_dts nvarchar(5) = N'false'
DECLARE @allow_pull nvarchar(5) = N'true'
DECLARE @allow_push nvarchar(5) = N'true'
DECLARE @description nvarchar(255) = N'Peer-to-Peer publication of database MyDBName from Node3'
DECLARE @enabled_for_p2p nvarchar(5) = N'true'
DECLARE @independent_agent nvarchar(5) = N'true'
DECLARE @p2p_conflictdetection nvarchar(5) = N'true'
DECLARE @p2p_originator_id int = 1
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @repl_freq nvarchar(10) = N'continuous'
DECLARE @restricted nvarchar(10) = N'false'
DECLARE @status nvarchar(8) = N'active'
DECLARE @sync_method nvarchar(40) = N'NATIVE'
EXEC [MyDBName].dbo.sp_addpublication @allow_dts = @allow_dts, @allow_pull = @allow_pull, @allow_push = @allow_push, @description = @description, @enabled_for_p2p = @enabled_for_p2p, @independent_agent = @independent_agent, @p2p_conflictdetection = @p2p_conflictdetection, @p2p_originator_id = @p2p_originator_id, @publication = @publication, @repl_freq = @repl_freq, @restricted = @restricted, @status = @status, @sync_method = @sync_method
GO

DECLARE @article nvarchar(256) = N'tbl0'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl0'
DECLARE @destination_table nvarchar(256) = N'tbl0'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl0'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, @source_owner = @source_owner, @type = @type
GO

DECLARE @article nvarchar(256) = N'tbl1'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl1'
DECLARE @destination_table nvarchar(256) = N'tbl1'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl1'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, 
@source_owner = @source_owner, @type = @type
GO

建立從 Peer1 到 Peer2 的發送訂閱

此步驟會建立從可用性群組到 SQL Server 獨立執行個體的發送訂閱。

在 Node1 上執行下列指令碼。此步驟假設 Node1 正在執行主要副本。

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'Node3'
 , @destination_db = N'MyDBName'
 , @subscription_type = N'push'
 , @sync_type = N'replication support only'
GO

EXEC [MyDBName].dbo.sp_addpushsubscription_agent 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'Node3'
 , @subscriber_db = N'MyDBName'
 , @job_login = null
 , @job_password = null
 , @subscriber_security_mode = 1
 , @frequency_type = 64
 , @frequency_interval = 1
 , @frequency_relative_interval = 1
 , @frequency_recurrence_factor = 0
 , @frequency_subday = 4
 , @frequency_subday_interval = 5
 , @active_start_time_of_day = 0
 , @active_end_time_of_day = 235959
 , @active_start_date = 0
 , @active_end_date = 0
 , @dts_package_location = N'Distributor'
GO

建立從 Peer2 到可用性群組接聽程式的推送訂閱

若要建立從 Peer2 到可用性群組接聽程式的發送訂閱，請在 Node3 上執行下列命令。

重要

以下腳本指定用戶的可用性群組監聽器名稱。

@subscriber = N'MyAGListenerName,<port>'

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAGListenerName,<port>'
 , @destination_db = N'MyDBName'
 , @subscription_type = N'push'
 , @sync_type = N'replication support only'
GO

EXEC [MyDBName].dbo.sp_addpushsubscription_agent 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAGListenerName,<port>'
 , @subscriber_db = N'MyDBName'
 , @job_login = null
 , @job_password = null
 , @subscriber_security_mode = 1
 , @frequency_type = 64
 , @frequency_interval = 1
 , @frequency_relative_interval = 1
 , @frequency_recurrence_factor = 0
 , @frequency_subday = 4
 , @frequency_subday_interval = 5
 , @active_start_time_of_day = 0
 , @active_end_time_of_day = 235959
 , @active_start_date = 0
 , @active_end_date = 0
 , @dts_package_location = N'Distributor'
GO

設定連結的伺服器

在每個次要副本主機上，確保出版物的推送訂閱者以連結伺服器的形式出現。

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

後續步驟

將點對點發行集資料庫設定為可用性群組的一部分

意見反應

此頁面對您有幫助嗎？

上次更新時間： 2026-09-24