Configura entrambi i peer nei gruppi di disponibilità

  • Articolo

A partire da SQL Server 2019 (15.x) CU 13 un database appartenente 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, ognuno nel proprio gruppo di disponibilità.

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

Ruoli e nomi

In questa sezione vengono descritti i ruoli e i nomi dei vari elementi che partecipano alla topologia di replica per questo articolo.

Peer1

  • Node1: 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: server di distribuzione remoto
  • P2P_MyDBName: nome della pubblicazione
  • MyAGListenerName: listener del gruppo di disponibilità

Peer2

  • Node3: replica primaria per il secondo gruppo di disponibilità
  • Node4: replica secondaria per il secondo gruppo di disponibilità
  • MyAG2: nome del gruppo di disponibilità per il secondo nome del gruppo di disponibilità
  • MyDBName: database da pubblicare
  • Dist2: server di distribuzione remoto
  • P2P_MyDBName: nome della pubblicazione
  • MyAG2ListenerName: listener del gruppo di disponibilità

Prerequisiti

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

  • Due istanze di SQL Server per ospitare i database del server di distribuzione.

  • Tutte le istanze del server richiedono un'edizione supportata: Enterprise Edition o Developer Edition.

  • Tutte le istanze del server richiedono una versione supportata: SQL Server 2019 (15.x) CU13 o versioni successive.

  • Connettività di rete e larghezza di banda sufficienti tra tutte le istanze.

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

    Per verificare se la replica è installata in qualsiasi istanza, esegui la query seguente:

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

    Nota

    Per evitare un singolo punto di errore per il database di distribuzione, usa un server di distribuzione remoto per ogni peer.

    Per un ambiente dimostrativo o di test, puoi configurare i database di distribuzione in una singola istanza.

Configura il server di distribuzione e il server di pubblicazione remoto (Peer1)

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

  1. Esegui sp_adddistributor per configurare la distribuzione in Dist1. Usa @password = per specificare una password utilizzata dal server di pubblicazione remoto per connettersi al distributore. Usa questa password in ogni server di pubblicazione remoto quando si configura il server di distribuzione remoto.

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

  2. Creare il database di distribuzione nel server di distribuzione.

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

  3. Configura il server di pubblicazione remoto Node1 e Node2.

    Il @security_mode Determina il modo in cui gli agenti di replica si connettono all'istanza primaria corrente.

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

    Nota

    Se gli eventuali agenti di replica modificati vengono eseguiti in un computer diverso dal server di distribuzione, l'utilizzo dell'Autenticazione di Windows per la connessione alla replica primaria richiede l'autenticazione Kerberos per configurare la comunicazione tra i computer host della replica. L'uso di un account di accesso di SQL Server per la connessione alla replica primaria corrente 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 il server di pubblicazione nel server di pubblicazione originale (Node1)

  1. Configura il server di pubblicazione originale della distribuzione remota (Node1). Specifica per @password lo stesso valore usato al momento dell'esecuzione di sp_adddistributor nel server di distribuzione per configurare 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 server di pubblicazione di replica (Node2)

In ogni host di replica secondaria (Node2) per il primo gruppo di disponibilità, configura la distribuzione. Specifica per @password lo stesso valore usato al momento dell'esecuzione di sp_adddistributor nel server di distribuzione per configurare la distribuzione.

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

Rendi il database parte del gruppo di disponibilità e crea il listener (Peer1)

  1. Nella replica primaria prevista, crea il gruppo di disponibilità con il database come database membro.

  2. Crea un listener DNS per il gruppo di disponibilità. L'agente di replica si connette alla replica primaria corrente usando il listener. L'esempio seguente crea un listener denominato MyAGListenerName.

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

    Nota

    Nello script di cui sopra, le informazioni tra parentesi quadre ([ ... ]) sono facoltative. Usale per specificare un valore non predefinito per la porta TCP. Non includere le parentesi acute.

Reindirizza il server di pubblicazione originale al nome del listener del gruppo di disponibilità (Peer1)

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

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

Nota

Nello script di cui sopra, ,<port> è facoltativo. È obbligatorio solo se si usano porte non predefinite. Non includere le parentesi acute <>.

Crea una pubblicazione peer-to-peer (Peer1) nel server di pubblicazione 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

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

Nel server di pubblicazione 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

Nota

Nello script di cui sopra, ,<port> è facoltativo. È obbligatorio solo se si usano porte non predefinite.

Dopo aver completato i passaggi precedenti, 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 server di distribuzione e il server di pubblicazione remoto (Peer2)

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

  1. Esegui sp_adddistributor per configurare la distribuzione in Dist2. Usa @password = per specificare una password utilizzata dal server di pubblicazione remoto per connettersi al distributore. Usa questa password in ogni server di pubblicazione remoto quando si configura il server di distribuzione remoto.

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

  2. Creare il database di distribuzione nel server di distribuzione.

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

  3. Configura il server di pubblicazione remoto Node3 e Node4.

    Il @security_mode Determina il modo in cui gli agenti di replica si connettono all'istanza primaria corrente.

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

    Nota

    Se gli eventuali agenti di replica modificati vengono eseguiti in un computer diverso dal server di distribuzione, l'utilizzo dell'Autenticazione di Windows per la connessione alla replica primaria richiede l'autenticazione Kerberos per configurare la comunicazione tra i computer host della replica. L'uso di un account di accesso di SQL Server per la connessione alla replica primaria corrente 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 il server di pubblicazione (Peer2)

  1. Configura la distribuzione remota su (Node3). Specifica per @password lo stesso valore usato al momento dell'esecuzione di sp_adddistributor nel server di distribuzione per configurare 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 di replica (Node4)

In ogni host di replica secondaria (Node4) per il secondo gruppo di disponibilità, configura la distribuzione. Specifica per @password lo stesso valore usato al momento dell'esecuzione di sp_adddistributor nel server di distribuzione per configurare la distribuzione.

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

Rendi il database parte del gruppo di disponibilità e crea il listener (Peer2)

  1. Nella replica primaria prevista, crea il gruppo di disponibilità con il database come database membro.

  2. Crea un listener DNS per il gruppo di disponibilità. L'agente di replica si connette alla replica primaria corrente usando il listener. L'esempio seguente crea un listener denominato MyAG2ListenerName.

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

    Nota

    Nello script di cui sopra, le informazioni tra parentesi quadre ([ ... ]) sono facoltative. Usale per specificare un valore non predefinito per la porta TCP. Non includere le parentesi acute.

Reindirizza il server di pubblicazione originale al nome del listener del gruppo di disponibilità (Peer2)

Nel server di distribuzione per Peer2, reindirizza il server di pubblicazione originale al nome del listener del gruppo di disponibilità.

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

Nota

Nello script di cui sopra, ,<port> è facoltativo. È obbligatorio solo se si usano porte non predefinite. Non includere le parentesi acute <>.

Crea una pubblicazione peer-to-peer (Peer2)

Il seguente script crea la pubblicazione per Peer2.

In Node3, 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

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

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

Nel server di pubblicazione 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

Nota

Nello script di cui sopra, ,<port> è facoltativo. È obbligatorio solo se si usano porte non predefinite.

Crea una sottoscrizione push da Peer1 al listener del gruppo di disponibilità per Peer2

Per creare una sottoscrizione push da Peer1 al listener del gruppo di disponibilità Peer2, esegui il seguente comando in Node1.

Esegui il seguente script su Node1. Ciò presuppone che Node1 stia eseguendo la replica primaria.

Importante

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

@subscriber = N'MyAGListenerName,<port>'

Nota

Nello script di cui sopra, ,<port> è facoltativo. È obbligatorio solo se si usano porte non predefinite. Non includere le parentesi acute <>.

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 una sottoscrizione push da Peer2 al listener del gruppo di disponibilità (Peer1)

Per creare una sottoscrizione push da Peer2 al listener del gruppo di disponibilità (Peer2), esegui il seguente comando in Node3.

Importante

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

@subscriber = N'MyAGListenerName,<port>'

Nota

Nello script di cui sopra, ,<port> è facoltativo. È obbligatorio solo se si usano porte non predefinite. Non includere le parentesi acute <>.

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 della replica secondaria, verifica che i Sottoscrittori push delle pubblicazioni del database vengano visualizzati come server collegati.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Passaggi successivi