Configurar ambos os pares em grupos de disponibilidade

A partir da Atualização Cumulativa 13 (CU 13) do SQL Server 2019 (15.x), uma base de dados que pertence a um grupo de disponibilidade Always On do SQL Server pode participar como um par numa topologia de replicação transacional ponto a ponto. Este artigo descreve como configurar este cenário com dois pares - cada um no seu próprio grupo de disponibilidade.

Os scripts neste exemplo utilizam procedimentos armazenados T-SQL.

Funções e nomes

Esta secção descreve os papéis e nomes dos vários elementos que participam na topologia de replicação deste artigo.

Peer1

  • Node1: Réplica primária para o primeiro grupo de disponibilidade
  • Nodo2: Réplica secundária para o primeiro grupo de disponibilidade
  • MyAG: Nome do grupo de disponibilidade para o primeiro grupo de disponibilidade
  • MyDBName: Base de dados Peer1 . Base de dados a publicar
  • Dist1: Distribuidor remoto
  • P2P_MyDBName: Nome da publicação
  • MyAGListenerName: Ouvinte do grupo de disponibilidade

Peer2

  • Node3: Réplica primária do segundo grupo de disponibilidade
  • Node4: Réplica secundária para o segundo grupo de disponibilidade
  • MyAG2: Nome do grupo de disponibilidade para o nome do segundo grupo de disponibilidade
  • MyDBName: Base de dados a ser publicada
  • Dist2: Distribuidor remoto
  • P2P_MyDBName: Nome da publicação
  • MyAG2ListenerName: Ouvinte do grupo de disponibilidade

Pré-requisitos

  • Quatro instâncias do SQL Server em servidores físicos ou virtuais separados para alojar os grupos de disponibilidade. Dois grupos de disponibilidade contêm cada um uma base de dados de pares.

  • Duas instâncias do SQL Server para alojar as bases de dados dos distribuidores.

  • Todas as instâncias de servidor requerem uma edição suportada - edição Enterprise ou edição Developer.

  • Todas as instâncias de servidor requerem uma versão suportada - SQL Server 2019 (15.x) CU13 ou posterior.

  • Conectividade de rede e largura de banda suficientes entre todas as instâncias.

  • Instale a replicação do SQL Server em todas as instâncias do SQL Server.

    Para verificar se a replicação está instalada em alguma instância, execute a seguinte consulta:

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

    Note

    Para evitar um único ponto de falha na base de dados de distribuição, use um distribuidor remoto para cada par.

    Para ambiente de demonstração ou teste, pode configurar as bases de dados de distribuição numa única instância.

Configurar o distribuidor e o editor remoto (Peer1)

Esta secção descreve como configurar o primeiro peer (Peer1) num grupo de disponibilidade.

  1. Execute sp_adddistributor para configurar a distribuição no Dist1. Use @password = para especificar uma palavra-passe que o editor remoto usa para se ligar ao distribuidor. Utilize esta palavra-passe em cada publicador remoto ao configurar o distribuidor remoto.

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

  2. Crie o banco de dados de distribuição no distribuidor.

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

  3. Configure Node1 e Node2 como publicador remoto.

    O @security_mode determina como os agentes de replicação se ligam ao primário atual.

    • 1 = autenticação do Windows.
    • 0= Autenticação SQL Server. Requer @login e @password. O login e a palavra-passe especificados devem ser válidos em cada réplica secundária.

    Note

    Se quaisquer agentes de replicação modificados forem executados num computador que não seja o distribuidor, a utilização da autenticação do Windows para a ligação ao servidor principal requer autenticação Kerberos para a comunicação entre os computadores anfitriões das réplicas. O uso de um login SQL Server para a ligação ao principal atual não requer autenticação 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

Configure o editor no editor original (Node1)

  1. Configurar o publicador original da distribuição remota (Node1). Especifique para @password o mesmo valor utilizado quando executou sp_adddistributor no distribuidor para configurar a distribuição.

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

  2. Habilite o banco de dados para replicação.

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

Configurar o anfitrião da réplica secundária como publicador de replicação (Node2)

Em cada host réplica secundária (Node2) para o primeiro grupo de disponibilidade, configura a distribuição. Especifique para @password o mesmo valor que utilizou quando executou sp_adddistributor no distribuidor para configurar a distribuição.

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

Tornar a base de dados parte do grupo de disponibilidade e criar o ouvinte (Peer1)

  1. Na réplica primária pretendida, cria-se o grupo de disponibilidade com a base de dados como base de dados membro.

  2. Crie um ouvinte DNS para o grupo de disponibilidade. O agente de replicação liga-se à réplica primária atual usando o ouvinte. O exemplo seguinte cria um ouvinte chamado MyAGListenerName.

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

    Note

    No script anterior, a informação entre parênteses ([ ... ]) é opcional. Use-o para especificar um valor não padrão para a porta TCP. Não incluas os parênteses.

Redirecionar o publicador original para o nome do escutador AG (Peer1)

No distribuidor de Peer1, redirecione o publicador original para o nome do serviço de escuta do AG.

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

Note

No script anterior, ,<port> é opcional. Só é obrigatório se estiveres a usar portas não padrão. Não incluas os parênteses angulares <>.

Criar publicação peer-to-peer (Peer1) no publicador original - Node1

O script seguinte cria a publicação para o 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

Tornar a publicação peer-to-peer compatível com o grupo de disponibilidade (Peer1)

No editor original (Node1), execute o seguinte script para tornar a publicação compatível com o grupo de disponibilidade:

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

No script anterior, ,<port> é opcional. Só é obrigatório se estiveres a usar portas não padrão.

Depois de completar os passos anteriores, o grupo de disponibilidade está pronto para participar na topologia peer-to-peer. Os passos seguintes configuram um grupo de disponibilidade separado como segundo par (Peer2) na topologia de replicação peer-to-peer.

Configurar o distribuidor e o editor remoto (Peer2)

Esta secção descreve como configurar o segundo par (Peer2) num grupo de disponibilidade diferente.

  1. Execute sp_adddistributor para configurar a distribuição no Dist2. Use @password = para especificar uma palavra-passe que o editor remoto usa para se ligar ao distribuidor. Utilize esta palavra-passe em cada publicador remoto ao configurar o distribuidor remoto.

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

  2. Crie o banco de dados de distribuição no distribuidor.

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

  3. Configure Node3 e Node4 como publicador remoto.

    O @security_mode determina como os agentes de replicação se ligam ao primário atual.

    • 1 = autenticação do Windows.
    • 0= Autenticação SQL Server. Requer @login e @password. O login e a palavra-passe especificados devem ser válidos em cada réplica secundária.

    Note

    Se algum agente de replicação modificado estiver a ser executado num computador diferente do distribuidor, a utilização da autenticação do Windows para a ligação ao primário requer autenticação Kerberos para a comunicação entre os computadores anfitriões das réplicas. O uso de um login SQL Server para a ligação ao principal atual não requer autenticação 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

Configurar o editor (Peer2)

  1. Configurar distribuição remota em (Node3). Especifique o mesmo valor para @password que utilizou quando executou sp_adddistributor no distribuidor para configurar a distribuição.

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

  2. Habilite o banco de dados para replicação.

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

Configure o anfitrião da réplica secundária como publicador de replicação (Node4)

Em cada host réplica secundária (Node4) para o segundo grupo de disponibilidade, configura a distribuição. Especifique para @password o mesmo valor que utilizou quando executou sp_adddistributor no distribuidor para configurar a distribuição.

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

Tornar a base de dados parte do grupo de disponibilidade e criar o ouvinte (Peer2)

  1. Na réplica primária pretendida, cria-se o grupo de disponibilidade com a base de dados como base de dados membro.

  2. Crie um ouvinte DNS para o grupo de disponibilidade. O agente de replicação liga-se à réplica primária atual usando o ouvinte. O exemplo seguinte cria um ouvinte chamado MyAG2ListenerName.

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

    Note

    No script anterior, a informação entre parênteses ([ ... ]) é opcional. Use-o para especificar um valor não padrão para a porta TCP. Não incluas os parênteses.

Redirecionar o publicador original para o nome do escutador AG (Peer2)

No distribuidor de Peer2, redirecione o publicador original para o nome do listener do AG.

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

Note

No script anterior, ,<port> é opcional. Só é obrigatório se estiveres a usar portas não padrão. Não incluas os parênteses angulares <>.

Criar publicação ponto a ponto (Peer2)

O script seguinte cria a publicação para Peer2.

Em Node3, execute o seguinte comando para criar a publicação ponto a ponto.

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

Tornar a publicação peer-to-peer compatível com o grupo de disponibilidade (Peer2)

No editor original (Node3), execute o seguinte script para tornar a publicação compatível com o grupo de disponibilidade:

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

No script anterior, ,<port> é opcional. Só é obrigatório se estiveres a usar portas não padrão.

Crie uma subscrição push do Peer1 para o ouvinte do grupo de disponibilidade do Peer2

Para criar uma subscrição push do Peer1 para o ouvinte do grupo de disponibilidade Peer2, execute o seguinte comando no Node1.

Execute o seguinte script no Node1. Este script assume que o Node1 está a executar a réplica principal.

Importante

O script seguinte especifica o nome do ouvinte do grupo de disponibilidade para o assinante.

@subscriber = N'MyAGListenerName,<port>'

Note

No script anterior, ,<port> é opcional. Só é obrigatório se estiveres a usar portas não padrão. Não incluas os parênteses angulares <>.

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

Crie uma subscrição push do Peer2 para o ouvinte do grupo de disponibilidade (Peer1)

Para criar uma subscrição push do Peer2 para o ouvinte do grupo de disponibilidade (Peer1), execute o seguinte comando no Node3.

Importante

O script seguinte especifica o nome do ouvinte do grupo de disponibilidade para o assinante.

@subscriber = N'MyAGListenerName,<port>'

Note

No script anterior, ,<port> é opcional. Só é obrigatório se estiveres a usar portas não padrão. Não incluas os parênteses angulares <>.

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

Configurar servidores ligados

Em cada host réplica secundária, certifique-se de que os subscritores push das publicações da base de dados aparecem como servidores ligados.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';
  • Última atualização a