Configurar o banco de dados de distribuição de replicação no grupo de disponibilidade Always On

Artigo
04/06/2023

Aplica-se a: SQL Server

Este artigo explica como configurar um banco de dados de distribuição de replicação do SQL Server em um AG (grupo de disponibilidade) Always On.

O SQL Server 2017 CU6 e o SQL Server 2016 SP2-CU3 introduzem o suporte para o banco de dados de distribuição de replicação em um AG por meio dos seguintes mecanismos:

O AG do banco de dados de distribuição deve ter um ouvinte. Quando o publicador adiciona o distribuidor, ele usa o nome de ouvinte como o nome do distribuidor.
Os trabalhos de replicação são criados com o nome do ouvinte como o nome do distribuidor. Os trabalhos de instantâneo de replicação, leitor de log e agente de distribuição (assinatura push) criados no servidor de distribuição são criados em todas as réplicas secundárias do AG para o Banco de Dados de Distribuição.

Observação

Trabalhos do agente de distribuição para assinaturas pull são criados no servidor do assinante e não no servidor de distribuição.

Um novo trabalho monitora o estado (primário ou secundário no AG) dos bancos de dados de distribuição e desabilita ou habilita os trabalhos de replicação com base no estado dos bancos de dados de distribuição.

Depois que um banco de dados de distribuição no AG for configurado com base nas etapas descritas abaixo, os trabalhos de tempo de execução e de configuração da replicação poderão ser executados corretamente antes e após o failover do AG do banco de dados de distribuição.

Cenários com suporte

Configuração do banco de dados de distribuição a ser incluído em um AG.
Configuração da replicação como publicações e assinaturas antes e após o failover do AG.
Trabalhos de replicação funcionais antes e após o failover.
Remoção da replicação no distribuidor e publicador quando o banco de dados de distribuição está no AG.
Adição ou remoção de nós do AG de um banco de dados de distribuição existente.
Um distribuidor pode ter vários bancos de dados de distribuição. Cada banco de dados de distribuição pode estar em seu próprio AG e não pode estar em qualquer AG. Vários bancos de dados de distribuição podem compartilhar um AG.
O publicador e o distribuidor devem estar em instâncias separadas do SQL Server.
Se o ouvinte para o grupo de disponibilidade que hospeda o banco de dados de distribuição estiver configurado para usar uma porta não padrão, será necessário configurar um alias para o ouvinte e a porta não padrão.

Limitações e exclusões

Não há suporte para o Distribuidor Local (em que o servidor do Publicador também é o Distribuidor). O Publicador e o Distribuidor devem ser instâncias separadas do SQL Server. Essas instâncias podem ser hospedadas nos mesmos conjuntos dos nós. Não há suporte para um Distribuidor Local pelos seguintes motivos:
- Se o Distribuidor estiver configurado localmente, você não poderá usar o ouvinte do grupo de disponibilidade para rotear o tráfego para o Distribuidor, o que faz com que os agentes de replicação falhem após o failover.
- Se um Distribuidor Local for configurado e, em seguida, o grupo de disponibilidade do Distribuidor falhar no secundário original, a conexão do Publicador com o Distribuidor mudará de local para remota, o que causará falhas nos procedimentos armazenados de replicação e nos agentes.
Não há suporte para o publicador Oracle.
Não há suporte para replicação de mesclagem.
Não há suporte para replicação transacional com assinante de atualização imediata ou em fila.
A replicação ponto a ponto não é compatível antes do SQL Server 2019 (15.x) CU 17
Todas as instâncias do SQL Server 2017 que hospedam réplicas de banco de dados de distribuição devem ter a Atualização Cumulativa 6 do SQL Server 2017 ou posterior.
Todas as instâncias do SQL Server 2016 que hospedam réplicas de banco de dados de distribuição devem ter a Atualização Cumulativa 3 do SQL Server 2016 SP2 ou posterior.
Todas as instâncias do SQL Server que hospedam réplicas de banco de dados de distribuição devem ter a mesma versão, exceto durante o período curto da atualização.
O banco de dados de distribuição deve estar no modelo de recuperação completa.
Para a recuperação e para permitir o truncamento do log de transações, configure backups completos e de log de transações.
O AG do banco de dados de distribuição deve ter um ouvinte configurado.
As réplicas secundárias em um AG do banco de dados de distribuição podem ser síncronas ou assíncronas. O modo síncrono é recomendado e preferencial.
Não há suporte para a replicação transacional bidirecional.
SSMS não mostra o Banco de Dados de Distribuição como sincronizando/sincronizado quando o banco de dados de distribuição é adicionado a um grupo de disponibilidade.

Observação

Antes de executar um dos procedimentos armazenados de replicação (por exemplo – sp_dropdistpublisher, sp_dropdistributiondb, sp_dropdistributor, sp_adddistributiondb, sp_adddistpublisher) na réplica secundária, verifique se a réplica está totalmente sincronizada.
Todas as réplicas secundárias em um AG do banco de dados de distribuição devem ser legíveis. Se uma réplica secundária não for legível, as propriedades do distribuidor no SQL Server Management Studio dessa réplica secundária específica não poderão ser acessadas, no entanto, a replicação continuará funcionando corretamente.
Todos os nós no AG do banco de dados de distribuição precisam usar a mesma conta de domínio para executar o SQL Server Agent, e essa conta de domínio precisa ter o mesmo privilégio em cada nó.
Se os agentes de replicação forem executados em uma conta proxy, ela precisará existir em cada nó do AG do banco de dados de distribuição e ter o mesmo privilégio em cada nó.
Faça alterações nas propriedades do distribuidor ou do banco de dados de distribuição em todas as réplicas que participam do AG do banco de dados de distribuição.
Faça alterações nos trabalhos de replicação por meio de procedimentos armazenados do msdb ou do SQL Server Management Studio em todas as réplicas que participam do AG do banco de dados de distribuição.
Se estiver usando um perfil personalizado para qualquer agente, ele deverá ser criado manualmente em todas as réplicas secundárias usando o procedimento sp_add_agent_profile. O perfil deve ter o mesmo ID em todas as réplicas.
A configuração do distribuidor no publicador precisa ser feita com scripts. O assistente de replicação não pode ser usado. Há suporte para assistentes de replicação e folhas de propriedade para outras finalidades.
A configuração do AG para bancos de dados de distribuição só pode ser feita por meio de scripts.
A configuração de bancos de dados de distribuição em um AG precisa ser uma nova configuração de replicação. Não há suporte para a alternância de um banco de dados de distribuição existente para um AG. Também quando um banco de dados de distribuição é retirado de um AG, ele pode deixar de funcionar como um banco de dados de distribuição válido e deve ser removido.

Arquitetura de configuração

Os nomes de servidor e as configurações a seguir são usados nos exemplos deste artigo.

DIST1, DIST2 e DIST3 são servidores do distribuidor;
PUB é o servidor do publicador;
Depois que o AG do banco de dados de distribuição for formado, o nome do ouvinte será DISTLISTENER;
DIST1 deve ser a réplica primária inicial do AG do banco de dados de distribuição.

Configurar o distribuidor, o banco de dados de distribuição e o publicador

Este exemplo configura um novo distribuidor e publicador e coloca o banco de dados de distribuição em um AG.

Fluxo de trabalho dos distribuidores

Configurar DIST1, DIST2 e DIST3 como distribuidores com sp_adddistributor @@servername. Especifique a senha para o distributor_admin por meio do @password. O @password deve ser idêntico em DIST1, DIST2, DIST3.
Crie o banco de dados de distribuição em DIST1 com sp_adddistributiondb. O nome do banco de dados de distribuição é distribution. Altere o modelo de recuperação do banco de dados distribution de simples para completo.
Crie um AG para o banco de dados distribution com réplicas em DIST1, DIST2 e DIST3. Preferencialmente, todas as réplicas são síncronas. Configure réplicas secundárias para serem legíveis ou permitirem a leitura. Neste momento, os bancos de dados de distribuição são o AG, DIST1 é a réplica primária e DIST2 e DIST3 são réplicas secundárias.
Configure um ouvinte chamado DISTLISTENER para o AG.
Para a recuperação e para permitir o truncamento do log de transações, configure backups completos e de log de transações.

No DIST2 e DIST3, execute:

EXEC sys.sp_adddistributiondb @database = 'distribution';

Para adicionar PUB como publicador em DIST1, execute:
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
O valor de @working_directory deve ser um caminho de rede independente de DIST1, DIST2 e DIST3.
No DIST2 e DIST3, se a réplica for legível como secundária, execute:
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
Se uma réplica não for legível como secundária, execute o failover de modo que a réplica se torne a primária e execute
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
O valor de @working_directory deve ser o mesmo da etapa anterior.

Fluxo de trabalho do fornecedor

Para adicionar o ouvinte de AG do banco de dados distribution como o distribuidor, em PUB, execute:

EXEC sys.sp_adddistributor @distributor = 'DISTLISTENER', @password = '<distributor_admin password>' ;

O valor de @password deve ser aquele especificado quando os distribuidores foram configurados no fluxo de trabalho do distribuidor.

Remover o distribuidor e o publicador

Este exemplo remove o publicador e o distribuidor quando o banco de dados de distribuição está no AG.

Fluxo de trabalho do fornecedor

Em PUB, remova todas as assinaturas e publicações desse publicador e, em seguida, chame sp_dropdistributor.

Fluxo de trabalho dos distribuidores

Neste exemplo, DIST1 é o primário atual do AG do banco de dados distribution. DIST2 e DIST3 são réplicas secundárias.

No DIST2 e DIST3, execute:

EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;

Em DIST1, execute:

EXEC sys.sp_dropdistpublisher @publisher = 'PUB';

Exclua o AG.
Em DIST2 e DIST3, altere o banco de dados distribution para o modo read_write restaurando o banco de dados com a recuperação.
```
RESTORE DATABASE [distribution] WITH RECOVERY, KEEP_REPLICATION;
```
Para remover o banco de dados distribution e manter o diretório de instantâneo, execute:
```
EXEC sys.sp_dropdistributiondb @database = 'distribution' , @former_ag_secondary = 1;
```

Este procedimento remove todos os trabalhos pendentes nesta réplica.

Para remover o banco de dados distribution de DIST1, execute

EXEC sys.sp_dropdistributiondb @database = 'distribution';

Se não houver nenhum outro banco de dados de distribuição no AG, execute sp_dropdistributor em DIST1, DIST2 e DIST3.

Adicionar uma réplica ao AG do banco de dados de distribuição

Este exemplo adiciona um novo distribuidor a uma configuração de replicação existente com o banco de dados de distribuição no AG. Neste exemplo, um banco de dados de distribuição existente está em um AG. DIST1 e DIST2 são os distribuidores, distribution é o banco de dados de distribuição no AG e PUB é o publicador. Adicione DIST3 como uma réplica no AG.

Fluxo de trabalho dos distribuidores

DIST3 deve ser configurado como um distribuidor por meio de sp_adddistributor @@servername. A senha de distributor_admin deve ser especificada por meio do parâmetro @password. A senha deve ser a mesma que foi especificada para DIST1 e DIST2.
Adicione DIST3 ao AG do banco de dados de distribuição atual.

No DIST3, execute:

EXEC sys.sp_adddistributiondb @database = 'distribution';

No DIST3, se a réplica for legível como secundária, execute:
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
Se a réplica não for legível como secundária, execute o failover de modo que a réplica se torne a primária e execute:
```
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
```
O valor de @working_directory deve ser o mesmo especificado para DIST1 e DIST2.
Em DIST3, você precisa recriar servidores vinculados aos assinantes.

Remover uma réplica do AG do banco de dados de distribuição

Este exemplo remove um distribuidor de um AG do banco de dados de distribuição atual enquanto o restante das réplicas no AG do banco de dados de distribuição não é afetado. Neste exemplo, um banco de dados de distribuição está no AG. DIST1, DIST2 e DIST3 são os distribuidores, distribution é o banco de dados de distribuição no AG e PUB é o publicador. Remova DIST3 do AG.

Fluxo de trabalho dos distribuidores

Verifique se o DIST3 é um secundário do AG do banco de dados distribution.
Remova DIST3 do AG do banco de dados distribution.
Em DIST3, altere o banco de dados distribution para o modo read_write restaurando o banco de dados com a recuperação. Por exemplo, execute o seguinte comando:
```
RESTORE DATABASE distribution WITH RECOVERY, KEEP_REPLICATION;
```

Para remover todos os trabalhos órfãos em DIST3, execute:

EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;

No DIST3, execute:

EXEC sys.sp_dropdistributiondb @database = 'distribution', @former_ag_secondary = 1;

No DIST3, execute:
```
EXEC sys.sp_dropdistributor;
```

Remover um publicador do AG do banco de dados de distribuição

Este exemplo remove um publicador de um AG do banco de dados de distribuição atual do distribuidor enquanto o restante dos publicadores atendidos por esse AG do banco de dados de distribuição não é afetado. Neste exemplo, uma configuração existente tem o banco de dados de distribuição em um AG. DIST1, DIST2 e DIST3 são os distribuidores, distribution é o banco de dados de distribuição no AG e PUB1 e PUB2 são os publicadores atendidos pelo banco de dados distribution. O exemplo remove PUB1 desses distribuidores.

Fluxo de trabalho do fornecedor

Em PUB1, remova todas as assinaturas e publicações desse publicador e, em seguida, chame sp_dropdistributor.

Fluxo de trabalho do Distribuidor

DIST1 é o primário atual do AG do banco de dados distribution.

No DIST2 e DIST3, execute:

EXEC sys.sp_dropdistpublisher @publisher = 'PUB1',  @no_checks = 1;

Em DIST1, execute:

EXEC sys.sp_dropdistpublisher @publisher = 'PUB1';

Neste ponto, pode haver trabalhos órfãos relacionados ao PUB1 em DIST2 ou DIST3. Sempre que ocorrer um failover em DIST2 e DIST3, os trabalhos órfãos relacionados a todas as publicações de PUB1 serão removidos pelo trabalho Monitor and sync replication agent jobs.

Adicionar assinatura

Este exemplo trata da configuração correta das informações de assinante entre distribuidores. O exemplo adiciona um assinante. DIST1 é a réplica primária atual do banco de dados de distribuição no AG e DIST2 e DIST3 são réplicas secundárias do banco de dados de distribuição no AG. O nome do assinante é SUB.

Fluxo de trabalho do fornecedor

Em PUB, adicione a assinatura como normalmente faria ao assinante SUB.

Fluxo de trabalho do Distribuidor

Em DIST2 e DIST3, adicione um servidor vinculado para 'SUB', caso ele não esteja registrado no DIST2 ou DIST3 anteriormente. Veja abaixo um TSQL de exemplo para a criação do servidor vinculado:

EXEC master.dbo.sp_addlinkedserver@server =N'SUB', @srvproduct=N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N'SUB', @useself = N'True',@locallogin = NULL,@rmtuser =N ULL,@rmtpassword = NULL;

Adicionar uma assinatura pull

Fluxo de trabalho do Assinante

Para adicionar uma assinatura pull a uma publicação com o banco de dados de distribuição em um AG, use o nome de ouvinte do AG no parâmetro @distributor de sp_addpullsubscription_agent.

T-SQL de exemplo – Criar BD de distribuição no AG

O script a seguir permite um banco de dados de distribuição em um grupo de disponibilidade.

--- WorkFlow to Enable Distribution Database In AG.

-- SECTION 1 ---- CONFIGURE THE DISTRIBUTOR SERVERS

-- Step1 - Configure the Distribution DB nodes (AG Replicas) to act as a distributor
:Connect SQLNode1
EXEC [sys].[sp_adddistributor]
    @distributor = @@SERVERNAME,
    @password = 'Pass@word1';
GO
:Connect SQLNode2
EXEC [sys].[sp_adddistributor]
    @distributor = @@SERVERNAME,
    @password = 'Pass@word1';
GO

-- Step2 - Configure the Distribution Database
:Connect SQLNode1
USE [master];
EXEC [sys].[sp_adddistributiondb]
    @database = 'DistributionDB',
    @security_mode = 1;
GO
ALTER DATABASE [DistributionDB] SET RECOVERY FULL;
GO
BACKUP DATABASE [DistributionDB] TO DISK = 'NUL';
GO
-- Step 3 - Create AG for the Distribution DB.
:Connect SQLNode1
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

:Connect SQLNode2
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

:Connect SQLNode1
-- Create the Availability Group
CREATE AVAILABILITY GROUP [DistributionDB_AG]
FOR DATABASE [DistributionDB]
REPLICA ON
N'SQLNode1' WITH (ENDPOINT_URL = N'TCP://SQLNode1.contoso.com:5022', 
	 FAILOVER_MODE = AUTOMATIC, 
	 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	 BACKUP_PRIORITY = 50, 
	 SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
	 SEEDING_MODE = AUTOMATIC),
N'SQLNode2' WITH (ENDPOINT_URL = N'TCP://SQLNode2.contoso.com:5022', 
	 FAILOVER_MODE = AUTOMATIC, 
	 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	 BACKUP_PRIORITY = 50, 
	 SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
	 SEEDING_MODE = AUTOMATIC);
GO


:Connect SQLNode2
ALTER AVAILABILITY GROUP [DistributionDB_AG] JOIN;
GO  
ALTER AVAILABILITY GROUP [DistributionDB_AG] GRANT CREATE ANY DATABASE;
GO

--STEP4 - Create the Listener for the Availability Group. This is very important.
:Connect SQLNode1

USE [master]
GO
ALTER AVAILABILITY GROUP [DistributionDB_AG]
ADD LISTENER N'DistributionDBList' (
WITH IP
((N'10.0.0.8', N'255.255.255.0')) , PORT=1500);
GO

-- STEP 5 - Enable SQLNode2 also as a Distributor
:Connect SQLNode2
EXEC sys.sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;
GO

--STEP 6 - On all Distributor Nodes Configure the Publisher Details 
:Connect SQLNode1
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
	@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO
:Connect SQLNode2
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
	@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO

-- SECTION 2 ---- CONFIGURE THE PUBLISHER SERVER
:Connect SQLNode4
EXEC sys.sp_adddistributor @distributor = 'DistributionDBList', -- Listener for the Distribution DB.	
	@password = 'Pass@word1';
GO

-- SECTION 3 ---- CONFIGURE THE SUBSCRIBERS 
-- On Publisher, create the publication as one would normally do.
-- On the Secondary replicas of the Distribution DB, add the Subscriber as a linked server.
:Connect SQLNode2
EXEC master.dbo.sp_addlinkedserver @server = N'SQLNODE5', @srvproduct = N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLNODE5', @useself = N'True',
	@locallogin = NULL,@rmtuser = NULL,@rmtpassword = NULL;

Publicar dados e objetos de banco de dados
Proteger o Distribuidor
Exibir e modificar propriedades de Publicador e Distribuidor
Desabilitar a publicação e a distribuição
Habilitar um banco de dados para replicação (SQL Server Management Studio)

Partilhar via

Configurar o banco de dados de distribuição de replicação no grupo de disponibilidade Always On

Cenários com suporte

Limitações e exclusões

Arquitetura de configuração

Configurar o distribuidor, o banco de dados de distribuição e o publicador

Fluxo de trabalho dos distribuidores

Fluxo de trabalho do fornecedor

Remover o distribuidor e o publicador

Fluxo de trabalho do fornecedor

Fluxo de trabalho dos distribuidores

Adicionar uma réplica ao AG do banco de dados de distribuição

Fluxo de trabalho dos distribuidores

Remover uma réplica do AG do banco de dados de distribuição

Fluxo de trabalho dos distribuidores

Remover um publicador do AG do banco de dados de distribuição

Fluxo de trabalho do fornecedor

Fluxo de trabalho do Distribuidor

Adicionar assinatura

Fluxo de trabalho do fornecedor

Fluxo de trabalho do Distribuidor

Adicionar uma assinatura pull

Fluxo de trabalho do Assinante

T-SQL de exemplo – Criar BD de distribuição no AG

Comentários

Recursos adicionais

Partilhar via

Configurar o banco de dados de distribuição de replicação no grupo de disponibilidade Always On

Cenários com suporte

Limitações e exclusões

Arquitetura de configuração

Configurar o distribuidor, o banco de dados de distribuição e o publicador

Fluxo de trabalho dos distribuidores

Fluxo de trabalho do fornecedor

Remover o distribuidor e o publicador

Fluxo de trabalho do fornecedor

Fluxo de trabalho dos distribuidores

Adicionar uma réplica ao AG do banco de dados de distribuição

Fluxo de trabalho dos distribuidores

Remover uma réplica do AG do banco de dados de distribuição

Fluxo de trabalho dos distribuidores

Remover um publicador do AG do banco de dados de distribuição

Fluxo de trabalho do fornecedor

Fluxo de trabalho do Distribuidor

Adicionar assinatura

Fluxo de trabalho do fornecedor

Fluxo de trabalho do Distribuidor

Adicionar uma assinatura pull

Fluxo de trabalho do Assinante

T-SQL de exemplo – Criar BD de distribuição no AG

Conteúdo relacionado

Comentários

Recursos adicionais