Configure um par como parte do grupo de disponibilidade

A partir de SQL Server 2019 (15.x) CU 13, 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 peer-to-peer. Este artigo descreve como configurar este cenário.

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 1 no grupo de disponibilidade MyAG .
Node2: Réplica 2 do grupo de disponibilidade MyAG.
MyAG: O nome do grupo de disponibilidade que cria no Node1 e no Node2.
MyAGListenerName: Nome do ouvinte do grupo de disponibilidade.
Dist1: O nome da instância do distribuidor remoto.
MyDBName: Nome da base de dados.
P2P_MyDBName: Nome da publicação.

Peer2

Node3: Um servidor autónomo que aloja uma instância padrão do SQL Server.
Dist2: O nome da instância do distribuidor remoto.
MyDBName: Nome da base de dados.
P2P_MyDBName: Nome da publicação.

Pré-requisitos

Duas instâncias de SQL Server em servidores físicos ou virtuais separados para hospedar o grupo de disponibilidade. O grupo de disponibilidade contém uma base de dados de pares.
Uma instância do SQL Server para alojar outra base de dados par.
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)

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>';  
```

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

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

Configura o Node1 e o Node2 como publicadores remotos.

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 for 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 da réplica. Usar 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)

Configurar o publicador original da distribuição remota (Node1). Especifique o mesmo valor para @password que foi 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>' 
```

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 de réplica secundário, configure 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)

Na réplica primária pretendida, cria-se o grupo de disponibilidade com a base de dados como base de dados membro.
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 MyAGListername.
```
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. Usa-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 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. Não incluas os parênteses angulares <>.

Depois de completar os passos anteriores, o grupo de disponibilidade está pronto para participar na topologia peer-to-peer. Os passos seguintes configuram uma instância autónoma do SQL Server (Peer2) para participar.

Configurar o distribuidor e o editor remoto (Peer2)

Configure a distribuição no distribuidor.

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

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

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

Configure 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 editor (Peer2)

No Node3, configure distribuição remota.

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

No Node3, ative a base 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 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

Crie uma subscrição de push de Peer1 para Peer2

Esta etapa cria uma subscrição push do grupo de disponibilidade para a instância autónoma do SQL Server.

Execute o seguinte script no Node1. Este passo assume que o Node1 está a executar 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

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

Para criar uma subscrição push de Peer2 para o listener do grupo de disponibilidade, execute o seguinte comando em Node3.

Importante

O script seguinte especifica o nome do ouvinte do grupo de disponibilidade para o 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 ligados

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

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Passo seguinte

Configure a base de dados de publicação ponto a ponto para integrar os grupos de disponibilidade

Comentários

Esta página foi útil?

Última atualização a 2026-09-24