Configura un peer come parte del gruppo 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.

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 1 nel gruppo di disponibilità MyAG.
  • Node2: Replica 2 nel gruppo di disponibilità MyAG.
  • MyAG: nome del gruppo di disponibilità che verrà creato in Node1 e Node2.
  • MyAGListenerName: nome del listener del gruppo di disponibilità.
  • Dist1: nome dell'istanza del server di distribuzione remoto.
  • MyDBName: nome del database.
  • P2P_MyDBName: nome della pubblicazione

Peer2

  • Node3: un server autonomo che ospita un'istanza predefinita di SQL Server.
  • Dist2: nome dell'istanza del server di distribuzione remoto.
  • MyDBName: nome del database.
  • P2P_MyDBName: nome della pubblicazione

Prerequisiti

  • Due istanze di SQL Server in server fisici o virtuali separati per ospitare il gruppo di disponibilità. Il gruppo di disponibilità conterrà ognuno un database peer.

  • Un'istanza di SQL Server per ospitare un altro 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)

  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 Node1 e Node2 come server di pubblicazione remoto.

    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)

Configurare la distribuzione per ogni host della replica secondaria. 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 MyAGListername.

    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. Non includere le parentesi acute <>.

Dopo aver completato i passaggi precedenti, il gruppo di disponibilità è pronto a partecipare alla topologia peer-to-peer. I passaggi successivi configurano un'istanza autonoma di SQL Server (Peer2) per partecipare.

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

  1. Configurare la distribuzione sul server di distribuzione.

    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 Node3 come server di pubblicazione remoto nel server di distribuzione Dist2

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

Configura il server di pubblicazione (Peer2)

  1. Su Node3, configura la distribuzione remota.

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

  2. Su Node3, abilita il database per la replica.

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

Crea una pubblicazione peer-to-peer (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

Crea una sottoscrizione push da Peer1 a Peer2

Questo passaggio crea una sottoscrizione push dal gruppo di disponibilità all'istanza autonoma di SQL Server.

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

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

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

Per creare una sottoscrizione push da Peer2 al listener del gruppo di disponibilità, 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>'

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 vengano visualizzati come server collegati.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Passaggio successivo

Configura il database di pubblicazione peer-to-peer come parte dei gruppi di disponibilità