Compartilhar via

Configurar ambos os pares em grupos de disponibilidade

  • Artigo

A partir do SQL Server 2019 (15.x) CU 13, um banco de dados que pertence a um grupo de disponibilidade Always On do SQL Server pode participar como um 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 e nomes

Esta seção descreve as funções e os nomes dos elementos que participam da topologia de replicação neste artigo.

Peer1

  • Node1: réplica primária do primeiro grupo de disponibilidade
  • Node2: réplica secundária do primeiro grupo de disponibilidade
  • MyAG: nome do primeiro grupo de disponibilidade
  • MyDBName: banco de dados do 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 do segundo grupo de disponibilidade
  • Node4: réplica secundária do 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 um banco de dados par cada.

  • Duas instâncias do SQL Server para hospedar os bancos de dados do distribuidor.

  • Todas as instâncias de servidor requerem uma edição com suporte: Enterprise ou Developer.

  • Todas as instâncias de servidor requerem uma versão com suporte: 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 ver 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;

    Observação

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

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

Configurar o distribuidor e o publicador 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 em Dist1. Use @password = para especificar uma senha que o publicador 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 do Node1 e do 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 logon e a senha especificados precisam ser válidos em cada réplica secundária.

    Observação

    Se qualquer agente de replicação modificado for executado em um computador que não seja o distribuidor, o uso da autenticação do Windows para a conexão à réplica primária exigirá a autenticação Kerberos para a comunicação entre os computadores host da réplica. O uso de um logon do SQL Server para a conexão à réplica primária atual não requer a 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 de distribuição remota (Node1). Especifique o mesmo valor de @password que aquele usado quando sp_adddistributor foi executado 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';

Configurar o host de 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 o mesmo valor de @password que aquele usado quando sp_adddistributor foi executado no distribuidor para configurar a distribuição.

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

Tornar o banco de dados parte do grupo de disponibilidade e criar 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>]));

    Observação

    No script acima, as informações entre colchetes ([ ... ]) são opcionais. Use-as para especificar um valor não padrão da porta TCP. Não inclua os colchetes.

Redirecionar o publicador original para o nome do ouvinte do 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>';

Observação

No script acima, ,<port> é opcional. Esse valor só será necessário se você usar portas não padrão. Não inclua colchetes angulares <>.

Criar a 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 ponto a ponto compatível com o grupo de disponibilidade (Peer1)

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

Observação

No script acima, ,<port> é opcional. Esse valor só será necessário se você usar portas não padrão.

Depois de você concluir as etapas acima, o grupo de disponibilidade estará preparado para participar da topologia ponto a ponto. As próximas etapas configuram um grupo de disponibilidade separado como o segundo par (Peer2) na topologia de replicação ponto a ponto.

Configurar o distribuidor e o publicador remoto (Peer2)

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

  1. Execute sp_adddistributor para configurar a distribuição em Dist2. Use @password = para especificar uma senha que o publicador 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 logon e a senha especificados precisam ser válidos em cada réplica secundária.

    Observação

    Se qualquer agente de replicação modificado for executado em um computador que não seja o distribuidor, o uso da autenticação do Windows para a conexão à réplica primária exigirá a autenticação Kerberos para a comunicação entre os computadores host da réplica. O uso de um logon do SQL Server para a conexão à réplica primária atual não requer a 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 a distribuição remota no Node3. Especifique o mesmo valor de @password que aquele usado quando sp_adddistributor foi executado no distribuidor para 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';

Configurar o host de réplica secundária como publicador de replicação (Node4)

Em cada host de réplica secundária (Node4) do segundo grupo de disponibilidade, configure a distribuição. Especifique o mesmo valor de @password que aquele usado quando sp_adddistributor foi executado no distribuidor para configurar a distribuição.

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

Tornar o banco de dados parte do grupo de disponibilidade e criar 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>]));

    Observação

    No script acima, as informações entre colchetes ([ ... ]) são opcionais. Use-as para especificar um valor não padrão da porta TCP. Não inclua os colchetes.

Redirecionar o publicador original para o nome do ouvinte do 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>';

Observação

No script acima, ,<port> é opcional. Esse valor só será necessário se você usar portas não padrão. Não inclua colchetes angulares <>.

Criar publicação ponto a ponto (Peer2)

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

Em Node3, execute o comando a seguir 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

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

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

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

Observação

No script acima, ,<port> é opcional. Esse valor só será necessário se você usar portas não padrão.

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

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

Execute o script a seguir no Node1. Isso pressupõe que Node1 está executando a réplica primária.

Importante

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

@subscriber = N'MyAGListenerName,<port>'

Observação

No script acima, ,<port> é opcional. Esse valor só será necessário se você usar portas não padrão. Não inclua 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 comando a seguir no Node3.

Importante

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

@subscriber = N'MyAGListenerName,<port>'

Observação

No script acima, ,<port> é opcional. Esse valor só será necessário se você usar portas não padrão. Não inclua 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 de réplica secundária, verifique se os assinantes push das publicações de banco de dados aparecem como servidores vinculados.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Próximas etapas