Configurar ambos os pares em grupos de disponibilidade

A partir do SQL Server 2019 (15.x) CU 13, um banco de dados que pertence a um dos grupos de disponibilidade Always On do SQL Server pode participar como par em uma topologia de replicação transacional ponto a ponto. Este artigo descreve como configurar esse cenário com dois pares, cada um no próprio grupo de disponibilidade.

Os scripts neste exemplo usam procedimentos armazenados T-SQL.

Funções & nomes

Esta seção descreve os papéis e nomes dos diversos elementos que participam da topologia de replicação deste artigo.

Peer1

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

Peer2

  • Node3: Réplica primária para o segundo grupo de disponibilidade
  • Node4: Réplica secundária para o segundo grupo de disponibilidade
  • MyAG2: nome do segundo grupo de disponibilidade
  • MyDBName: Banco de dados a ser publicado
  • 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 hospedar os grupos de disponibilidade. Dois grupos de disponibilidade contêm cada um um banco de dados par.

  • Duas instâncias do SQL Server para hospedar os bancos 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 ponto único de falha no banco de dados de distribuição, use um distribuidor remoto para cada peer.

    Para ambiente de demonstração ou teste, você pode configurar os bancos de dados de distribuição em uma única instância.

Configure o distribuidor e o publisher remoto (Peer1)

Esta seção descreve como configurar o primeiro par (Peer1) em um grupo de disponibilidade.

  1. Execute sp_adddistributor para configurar a distribuição no Dist1. Use @password = para especificar uma senha que o editor remoto usa para se conectar ao distribuidor. Use essa senha em cada publicador remoto ao configurar o distribuidor remoto.

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

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

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

  3. Configure o publicador remoto Node1 e Node2.

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

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

    Note

    Se algum agente de replicação modificado for executado em um computador diferente do distribuidor, o uso da autenticação do Windows na conexão com o primário exigirá autenticação Kerberos para a comunicação entre os computadores host da réplica. O uso de um login do SQL Server na conexão com a instância primária 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

Configurar o publicador no publicador original (Node1)

  1. Configurar o publicador original da distribuição remota (Node1). Especifique para @password o mesmo valor usado quando você 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. Habilitar o banco de dados para replicação.

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

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

Em cada host de réplica secundária (Node2) do primeiro grupo de disponibilidade, configure a distribuição. Especifique para @password o mesmo valor que você usou ao executar 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>'

Torne o banco de dados parte do grupo de disponibilidade e crie o ouvinte (Peer1)

  1. Na réplica primária visada, crie o grupo de disponibilidade com o banco de dados como um banco de dados membro.

  2. Crie um ouvinte DNS para o grupo de disponibilidade. O agente de replicação conecta-se à réplica primária atual usando o ouvinte. O exemplo a seguir 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, informações entre colchetes quadrados ([ ... ]) são opcionais. Use para especificar um valor não padrão para a porta TCP. Não inclua os parênteses.

Redirecionar o publicador original para o nome do listener da AG (Peer1)

No distribuidor do Peer1, redirecione o publicador original para o nome do ouvinte 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 você estiver usando portas não padrão. Não inclua os colchetes angulares <>.

Criar publicação ponto a ponto (Peer1) no publicador original - Node1

O script a seguir 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 você estiver usando portas não padrão.

Após completar os passos anteriores, o grupo de disponibilidade está pronto para participar da topologia peer-to-peer. As próximas etapas configuram um grupo de disponibilidade separado como o segundo peer (Peer2) na topologia de replicação ponto a ponto.

Configurar o distribuidor e o publisher remoto (Peer2)

Esta seção descreve como configurar o segundo par (Peer2) em um grupo de disponibilidade diferente.

  1. Execute sp_adddistributor para configurar a distribuição no Dist2. Use @password = para especificar uma senha que o editor remoto usa para se conectar ao distribuidor. Use essa senha em cada publicador remoto ao configurar o distribuidor remoto.

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

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

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

  3. Configure o publicador remoto do Node3 e do Node4.

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

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

    Note

    Se algum agente de replicação modificado estiver em execução em um computador diferente do distribuidor, o uso da autenticação do Windows para a conexão com o servidor primário requer autenticação Kerberos para a comunicação entre os computadores host da réplica. O uso de um login do SQL Server na conexão com a instância primária 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 publicador (Peer2)

  1. Configure distribuição remota em (Node3). Especifique para @password o mesmo valor usado quando você executou sp_adddistributor no Distribuidor ao configurar a distribuição.

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

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

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

Configure o host 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, configure a distribuição. Especifique para @password o mesmo valor usado quando você 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>'

Torne o banco de dados parte do grupo de disponibilidade e crie o ouvinte (Peer2)

  1. Na réplica primária visada, crie o grupo de disponibilidade com o banco de dados como um banco de dados membro.

  2. Crie um ouvinte DNS para o grupo de disponibilidade. O agente de replicação conecta-se à réplica primária atual usando o ouvinte. O exemplo a seguir 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, informações entre colchetes quadrados ([ ... ]) são opcionais. Use para especificar um valor não padrão para a porta TCP. Não inclua os parênteses.

Redirecione o publicador original para o nome do listener AG (Peer2)

No distribuidor do Peer2, redirecione o publicador original para o nome do ouvinte 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 você estiver usando portas não padrão. Não inclua os colchetes angulares <>.

Criar publicação ponto a ponto (Peer2)

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

No 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

Torne 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 você estiver usando portas não padrão.

Criar uma assinatura push do Peer1 para o ouvinte do grupo de disponibilidade do Peer2

Para criar uma assinatura por push de Peer1 para o listener do grupo de disponibilidade Peer2, execute o comando a seguir em Node1.

Execute o seguinte script em Node1. Este script assume que o Node1 está rodando a réplica primária.

Importante

O script a seguir 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 você estiver usando portas não padrão. Não inclua os colchetes 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

Criar uma assinatura push do Peer2 para o ouvinte do grupo de disponibilidade (Peer1)

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

Importante

O script a seguir especifica o nome do ouvinte do grupo de disponibilidade do assinante.

@subscriber = N'MyAGListenerName,<port>'

Note

No script anterior, ,<port> é opcional. Só é obrigatório se você estiver usando portas não padrão. Não inclua os colchetes 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 vinculados

Em cada host réplica secundária, certifique-se de que os assinantes push das publicações do banco de dados apareçam como servidores vinculados.

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