Configurar um par como parte do grupo 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 explica como configurar esse cenário.

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 1 no grupo de disponibilidade MyAG.
  • Node2: réplica 2 no grupo de disponibilidade MyAG.
  • MyAG: o nome do grupo de disponibilidade que será criado em Node1 e Node2.
  • MyAGListenerName: nome do ouvinte do grupo de disponibilidade.
  • Dist1: o nome da instância do distribuidor remoto.
  • MyDBName: nome do banco de dados.
  • P2P_MyDBName: nome da publicação.

Peer2

  • Node3: um servidor autônomo que hospeda uma instância padrão do SQL Server.
  • Dist2: o nome da instância do distribuidor remoto.
  • MyDBName: nome do banco de dados.
  • P2P_MyDBName: nome da publicação.

Pré-requisitos

  • Duas instâncias do SQL Server em servidores físicos ou virtuais separados para hospedar o grupo de disponibilidade. O grupo de disponibilidade conterá um banco de dados par.

  • Uma instância do SQL Server para hospedar outro banco de dados par.

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

  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 Node1 e o Node2 como publicador remoto.

    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, 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 MyAGListername.

    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. Não inclua colchetes angulares <>.

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 uma instância autônoma do SQL Server (Peer2) para participar.

Configurar o distribuidor e o publicador remoto (Peer2)

  1. Configurar a distribuição no distribuidor.

    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. Configurar o Node3 como publicador remoto no distribuidor Dist2

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

Configurar o publicador (Peer2)

  1. No Node3, configure a distribuição remota.

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

  2. No Node3, habilite o banco de dados para replicação.

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

Criar publicação ponto a ponto (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

Criar uma assinatura push do Peer1 para o Peer2

Esta etapa cria uma assinatura push do grupo de disponibilidade para a instância autônoma do SQL Server.

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

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

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

Para criar uma assinatura push do Peer2 para o ouvinte do grupo de disponibilidade, 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>'

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 aparecem como servidores vinculados.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Próxima etapa

Configurar o banco de dados de publicação ponto a ponto para fazer parte dos grupos de disponibilidade