在可用性群組中設定這兩個同儕節點

發行項
09/25/2024

從 SQL Server 2019 (15.x) CU 13 開始，屬於 SQL Server Always On 可用性群組的資料庫可以作為同儕節點參與點對點異動複寫拓撲。本文說明如何使用兩個同儕節點來設定此案例 - 每個都在其自己的可用性群組中。

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

角色和名稱

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

Peer1

Node1：第一個可用性群組的主要複本
Node2：第一個可用性群組的次要複本
MyAG：第一個可用性群組的可用性群組名稱
MyDBName：Peer1 資料庫。要發佈的資料庫
Dist1：遠端散發者
P2P_MyDBName：發行名稱
MyAGListenerName：可用性群組接聽程式

Peer2

Node3：第二個可用性群組的主要複本
Node4：第二個可用性群組的次要複本
MyAG2：第二個可用性群組名稱的可用性群組名稱
MyDBName：要發佈的資料庫
Dist2：遠端散發者
P2P_MyDBName：發行名稱
MyAG2ListenerName：可用性群組接聽程式

必要條件

在個別實體或虛擬伺服器上託管可用性群組的四個 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)

本節說明如何在可用性群組中設定第一個同儕節點 (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)

在第一個可用性群組的每個次要複本主機 (Node2) 上，設定散發。請以在散發者端執行 @password 來設定散發時所使用的值，指定 sp_adddistributor 的值。

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

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

在預期的主要複本上，建立可用性群組，將資料庫作為成員資料庫。
建立可用性群組的 DNS 接聽程式。複寫代理程式會使用接聽程式連接到目前的主要複本。下列範例會建立名為 MyAGListenerName 的接聽程式。
```
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> 為選用。只有在使用非預設連接埠時，才需要它。

完成上述步驟之後，可用性群組會準備參與點對點拓撲。後續步驟會將個別的可用性群組設定為點對點複寫拓撲中的第二個同儕節點 (Peer2)。

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

本節說明如何在不同可用性群組中設定第二個同儕節點 (Peer2)。

執行 sp_adddistributor 以在 Dist2 上設定散發。使用 @password = 來指定遠端發行者用來連線到散發者的密碼。當您設定遠端散發者時，請在每個遠端發行者上使用此密碼。
```
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;

設定 Node3 和 Node4 遠端發行者。

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

如果任何修改的複寫代理程式在散發者以外的電腦上執行，則使用 Windows 驗證來連接到主要複本時，就必須針對複本主機電腦之間的通訊設定 Kerberos 驗證。使用 SQL Server 登入來連接到目前主要複本時，不需要 Kerberos 驗證。
```
USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node3',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir2',  
 @security_mode = 1
USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node4',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir2',  
 @security_mode = 1
```

設定發行者 (Peer2)

設定遠端散發 (Node3)。請以在散發者端執行 @password 來設定散發時所使用的值，指定 sp_adddistributor 的值。

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

啟用資料庫進行複寫。

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

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

在第二個可用性群組的每個次要複本主機 (Node4) 上，設定散發。請以在散發者端執行 @password 來設定散發時所使用的值，指定 sp_adddistributor 的值。

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

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

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

在上述指令碼中，方括弧 ([ ... ]) 中的資訊為選用。使用它來指定 TCP 通訊埠的非預設值。不要包含括弧。

將原始發行者重新導向至 AG 接聽程式名稱 (Peer2)

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

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

注意

在上述指令碼中，,<port> 為選用。只有在使用非預設連接埠時，才需要它。不要包含角括弧 <>。

建立點對點發行集 (Peer2)

下列指令碼會建立 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

-- Note – 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

讓點對點發行集與可用性群組相容 (Peer2)

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

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

注意

在上述指令碼中，,<port> 為選用。只有在使用非預設連接埠時，才需要它。

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

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

在 Node1 上執行下列指令碼。這假設 Node1 正在執行主要複本。

重要

下列指令碼會指定訂閱者的可用性群組接聽程式名稱。

@subscriber = N'MyAGListenerName,<port>'

注意

在上述指令碼中，,<port> 為選用。只有在使用非預設連接埠時，才需要它。不要包含角括弧 <>。

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAG2Listener,<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'MyAG2Listener,<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

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

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

重要

下列指令碼會指定訂閱者的可用性群組接聽程式名稱。

@subscriber = N'MyAGListenerName,<port>'

注意

在上述指令碼中，,<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';

共用方式為

在可用性群組中設定這兩個同儕節點

角色和名稱

Peer1

Peer2

必要條件

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

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

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

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

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

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

讓點對點發行集與可用性群組相容 (Peer1)

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

設定發行者 (Peer2)

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

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

將原始發行者重新導向至 AG 接聽程式名稱 (Peer2)

建立點對點發行集 (Peer2)

讓點對點發行集與可用性群組相容 (Peer2)

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

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

設定連結的伺服器

下一步

意見反應

其他資源