Configura entrambi i peer in gruppi di disponibilità

A partire da SQL Server 2019 (15.x) CU 13, un database che appartiene a un gruppo di disponibilità Always On di SQL Server può partecipare come peer in una topologia di replica transazionale peer-to-peer. Questo articolo descrive come configurare questo scenario con due peer - ciascuno nel proprio gruppo di disponibilità.

Gli script in questo esempio usano procedure archiviate T-SQL.

Ruoli e nomi

Questa sezione descrive i ruoli e i nomi dei vari elementi che partecipano alla topologia di replicazione per questo articolo.

Peer1

  • Nodo1: Replica primaria per il primo gruppo di disponibilità
  • Node2: Replica secondaria per il primo gruppo di disponibilità
  • MyAG: Nome del gruppo di disponibilità per il primo gruppo di disponibilità
  • MyDBName: database Peer1. Database da pubblicare
  • Dist1: Distributore remoto
  • P2P_MyDBName: Nome della pubblicazione
  • MyAGListenerName: Ascoltatore del gruppo di disponibilità

Peer2

  • Nodo3: Replica primaria per il secondo gruppo di disponibilità
  • Nodo4: Replica secondaria per il secondo gruppo di disponibilità
  • MyAG2: Nome del gruppo di disponibilità per il secondo gruppo di disponibilità
  • MyDBName: Database da pubblicare
  • Dist2: Distributore remoto
  • P2P_MyDBName: Nome della pubblicazione
  • MyAG2ListenerName: Ascoltatore del gruppo di disponibilità

Prerequisiti

  • Quattro istanze di SQL Server su server fisici o virtuali separati per ospitare i gruppi di disponibilità. Due gruppi di disponibilità contengono ciascuno un database peer.

  • Due istanze di SQL Server per ospitare i database dei distributori.

  • Tutte le istanze server richiedono un'edizione supportata - edizione Enterprise o edizione Developer.

  • Tutte le istanze server richiedono una versione supportata - SQL Server 2019 (15.x) CU13 o successiva.

  • Sufficiente connettività di rete e larghezza di banda tra tutte le istanze.

  • Installa la replica di SQL Server su tutte le istanze di SQL Server.

    Per verificare se la replica è installata su qualche istanza, esegui la seguente inquisizione:

    USE master;   
GO   
DECLARE @installed int;   
EXEC @installed = sys.sp_MS_replication_installed;   
SELECT @installed;

    Note

    Per evitare un singolo punto di guasto per il database di distribuzione, si utilizza un distributore remoto per ogni peer.

    Per l'ambiente dimostrativo o di test, puoi configurare i database di distribuzione su un'unica istanza.

Configura il distributore e il publisher remoto (Peer1)

Questa sezione descrive come configurare il primo peer (Peer1) in un gruppo di disponibilità.

  1. Esegui sp_adddistributor per configurare la distribuzione su Dist1. Usalo @password = per specificare una password che il publisher remoto usa per connettersi al distributore. Usa questa password in ogni publisher remoto quando configuri il distributore remoto.

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

  2. Creare il database di distribuzione nel distributore.

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

  3. Configura Node1 e Node2 come editore remoto.

    @security_mode determina come gli agenti di replica si connettono al primario corrente.

    • 1 = autenticazione di Windows.
    • 0= Autenticazione SQL Server. Richiede @login e @password. L'accesso e la password specificati devono essere validi per ogni replica secondaria.

    Note

    Se eventuali agenti di replica modificati vengono eseguiti su un computer diverso dal distributore, l'uso dell'autenticazione di Windows per la connessione al server primario richiede l'autenticazione Kerberos per la comunicazione tra i computer host di replica. L'uso di un login SQL Server per la connessione all'attuale primario non richiede l'autenticazione 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

Configura l'editore presso il publisher originale (Nodo1)

  1. Configurare il publisher originale della distribuzione remota (Nodo1). Specifica lo stesso valore per @password come usato quando sp_adddistributor è stato eseguito dal distributore per impostare la distribuzione.

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

  2. Abilitare il database per la replica.

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

Configura l'host della replica secondaria come publisher per la replica (Node2)

In ogni host di replica secondaria (Node2) del primo gruppo di disponibilità, configurare la distribuzione. Specifica lo stesso valore per @password come usato quando sp_adddistributor è stato eseguito dal distributore per impostare la distribuzione.

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

Rendere il database parte del gruppo di disponibilità e creare l'ascoltatore (Peer1)

  1. Sulla replica primaria designata, creare il gruppo di disponibilità includendo il database come database membro.

  2. Crea un ascoltatore DNS per il gruppo di disponibilità. L'agente di replicazione si collega alla replica primaria corrente utilizzando l'ascoltatore. Il seguente esempio crea un ascoltatore chiamato MyAGListenerName.

    ALTER AVAILABILITY GROUP 'MyAG'
ADD LISTENER 'MyAGListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));

    Note

    Nella scrittura sopra, le informazioni tra parentesi quadrate ([ ... ]) sono opzionali. Usalo per specificare un valore non predefinito per la porta TCP. Non includere le parentesi.

Reindirizza il server di pubblicazione originale al nome del listener AG (Peer1)

Nel server di distribuzione per Peer1, reindirizza il server di pubblicazione originale al nome del listener AG.

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

Note

Nello script precedente ,<port> è facoltativo. È richiesto solo se usi porte non predefinite. Non includere allora le <>parentesi angolari.

Crea una pubblicazione peer-to-peer (Peer1) sull'editore originale - Node1

Il seguente script crea la pubblicazione per 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

Rendere la pubblicazione peer to peer compatibile con il gruppo di disponibilità (Peer1)

Sull'editore originale (Node1), esegui il seguente script per rendere la pubblicazione compatibile con il gruppo di disponibilità:

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

Note

Nello script qui sopra ,<port> è facoltativo. È richiesto solo se usi porte non predefinite.

Dopo aver completato i passaggi sopra, il gruppo di disponibilità è pronto a partecipare alla topologia peer-to-peer. I passaggi successivi configurano un gruppo di disponibilità separato come secondo peer (Peer2) nella topologia di replica peer-to-peer.

Configura il distributore e il publisher remoto (Peer2)

Questa sezione descrive come configurare il secondo peer (Peer2) in un gruppo di disponibilità diverso.

  1. Esegui sp_adddistributor per configurare la distribuzione su Dist2. Usalo @password = per specificare una password che il publisher remoto usa per connettersi al distributore. Usa questa password in ogni publisher remoto quando configuri il distributore remoto.

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

  2. Creare il database di distribuzione nel distributore.

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

  3. Configura Node3 e il publisher remoto di Node4 .

    @security_mode determina come gli agenti di replica si connettono al primario corrente.

    • 1 = autenticazione di Windows.
    • 0= Autenticazione SQL Server. Richiede @login e @password. L'accesso e la password specificati devono essere validi per ogni replica secondaria.

    Note

    Se eventuali agenti di replica modificati vengono eseguiti su un computer diverso dal distributore, l'uso dell'autenticazione di Windows per la connessione al server primario richiede l'autenticazione Kerberos per la comunicazione tra i computer host di replica. L'uso di un login SQL Server per la connessione all'attuale primario non richiede l'autenticazione 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

Configura l'editore (Peer2)

  1. Configura la distribuzione remota su (Nodo3). Specifica lo stesso valore per @password come usato quando sp_adddistributor è stato eseguito dal distributore per impostare la distribuzione.

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

  2. Abilitare il database per la replica.

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

Configura l'host della replica secondaria come server di pubblicazione per la replica (Node4)

In ogni host della replica secondaria (Node4) per il secondo gruppo di disponibilità, configurare la distribuzione. Specifica lo stesso valore per @password come usato quando sp_adddistributor è stato eseguito dal distributore per impostare la distribuzione.

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

Fai del database parte del gruppo di disponibilità e crea l'ascoltatore (Peer2)

  1. Sulla replica primaria designata, creare il gruppo di disponibilità includendo il database come database membro.

  2. Crea un ascoltatore DNS per il gruppo di disponibilità. L'agente di replicazione si collega alla replica primaria corrente utilizzando l'ascoltatore. Il seguente esempio crea un ascoltatore chiamato MyAG2ListenerName.

    ALTER AVAILABILITY GROUP 'MyAG2'
ADD LISTENER 'MyAG2ListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));

    Note

    Nella scrittura sopra, le informazioni tra parentesi quadrate ([ ... ]) sono opzionali. Usalo per specificare un valore non predefinito per la porta TCP. Non includere le parentesi.

Reindirizza il server di pubblicazione originale al nome listener AG (Peer2)

Nel distributore di Peer2, reindirizzare il server di pubblicazione originale al nome del listener AG.

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

Note

Nello script precedente ,<port> è facoltativo. È richiesto solo se usi porte non predefinite. Non includere allora le <>parentesi angolari.

Creare pubblicazioni peer-to-peer (Peer2)

Il seguente script crea la pubblicazione per Peer2.

Su Nodo3 esegui il seguente comando per creare la pubblicazione peer-to-peer.

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

Rendere la pubblicazione peer-to-peer compatibile con il gruppo di disponibilità (Peer2)

Sull'editore originale (Node3), esegui il seguente script per rendere la pubblicazione compatibile con il gruppo di disponibilità:

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

Note

Nello script precedente, ,<port> è facoltativo. È richiesto solo se usi porte non predefinite.

Crea un abbonamento push da Peer1 all'ascoltatore del gruppo di disponibilità per Peer2

Per creare un abbonamento push da Peer1 all'ascoltatore del gruppo di disponibilità Peer2, esegui il seguente comando su Nodo1.

Esegui il seguente script su Nodo1. Questo presuppone che Node1 stia eseguendo la replica primaria.

Importante

Lo script seguente specifica il nome del listener del gruppo di disponibilità per il sottoscrittore.

@subscriber = N'MyAGListenerName,<port>'

Note

Nello script sopra ,<port> è facoltativo. È richiesto solo se usi porte non predefinite. Non includere allora le <>parentesi angolari.

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

Crea un abbonamento push da Peer2 all'ascoltatore del gruppo di disponibilità (Peer1)

Per creare un abbonamento push da Peer2 all'ascoltatore del gruppo di disponibilità (Peer1), esegui il seguente comando su Node3.

Importante

Lo script seguente specifica il nome del listener del gruppo di disponibilità per il sottoscrittore.

@subscriber = N'MyAGListenerName,<port>'

Note

Nello script precedente ,<port> è facoltativo. È richiesto solo se usi porte non predefinite. Non includere allora le <>parentesi angolari.

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

Configura i server collegati

In ogni host di replica secondaria, assicurarsi che i sottoscrittori push delle pubblicazioni del database appaiano come server collegati.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';
  • Last updated on