共用方式為

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

  • 發行項

從 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)

  1. 執行 sp_adddistributor 以在 Dist1 上設定散發。 使用 @password = 來指定遠端發行者用來連線到散發者的密碼。 當您設定遠端散發者時，請在每個遠端發行者上使用此密碼。

    USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist1',  
 @password = '<Strong password for distributor>';

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

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

  3. 將 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)

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

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

  2. 啟用資料庫進行複寫。

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

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

在每個次要複本主機上，設定散發。 請以在散發者端執行 @password 來設定散發時所使用的值，指定 sp_adddistributor 的值。

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

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

  1. 在預期的主要複本上，建立可用性群組，將資料庫作為成員資料庫。

  2. 建立可用性群組的 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)

  1. 在散發者端設定散發。

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

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

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

  3. 在散發者 Dist2 上將 Node3 設定為遠端發行者

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

設定發行者 (Peer2)

  1. 在 Node3 上，設定遠端散發。

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

  2. 在 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

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

建立從 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';

後續步驟

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