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

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 grupo de disponibilidade Always On (AG).

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

• O banco de dados de distribuição AG precisa ter um ouvinte. Quando o editor adiciona o distribuidor, ele usa o nome do 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 snapshot 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 for Distribution DB.

• Uma nova tarefa monitoriza o estado (primário ou secundário no AG) dos bancos de dados de distribuição e habilita ou desabilita os trabalhos de replicação com base nos estados desses bancos de dados de distribuição.

Depois que um banco de dados de distribuição no AG é configurado com base nas etapas descritas abaixo, a configuração de replicação e os trabalhos em tempo de execução podem ser executados corretamente antes e depois do failover AG do banco de dados de distribuição.

Cenários suportados

• Configurar a base de dados de distribuição para ser incluída num AG.
• Configuração da replicação, como publicações e assinaturas, antes e depois do failover do AG.
• Trabalhos de replicação funcionais antes e depois do failover.
• Remoção da replicação no distribuidor e no publicador quando a base de dados de distribuição está no AG.
• Adicionar ou remover nós ao banco de dados de distribuição existente AG.
• Um distribuidor pode ter vários bancos de dados de distribuição. Cada base de dados de distribuição pode estar na sua própria AG ou pode não estar em nenhuma AG. Vários bancos de dados de distribuição podem compartilhar um AG.
• O editor e o distribuidor precisam estar em instâncias separadas do SQL Server.
• Se o ouvinte do 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 ou exclusões

• Não há suporte para Distribuidor Local (onde o servidor do Publisher 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 de nós. Um Distribuidor local não é suportado 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 fará com que os agentes de replicação falhem após o failover.
  • Se um Distribuidor local estiver configurado e, em seguida, o grupo de disponibilidade do Distribuidor fizer failover para o secundário original, a conexão do Publicador com o Distribuidor mudará de local para remoto, o que fará com que os procedimentos armazenados de replicação e os agentes falhem.

• O editor Oracle não é suportado.

• Não há suporte para replicação de mesclagem.

• Não há suporte para replicação transacional com assinante que atualiza imediatamente ou em fila.

• Não há suporte para replicação ponto a ponto antes do SQL Server 2019 (15.x) CU 17

• Todas as instâncias do SQL Server 2017 que têm réplicas da base de dados de distribuição devem ser SQL Server 2017 CU 6 ou posterior.

• Todas as instâncias do SQL Server 2016 que hospedam réplicas de banco de dados de distribuição devem ser do SQL Server 2016 SP2-CU3 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 de tempo estreito em que a atualização ocorre.

• O banco de dados de distribuição deve estar no modelo de recuperação completa.

• Para recuperação e para permitir o truncamento do log de transações, configure backups integrais e de log de transações.

• O banco de dados de distribuição AG deve ter um listener configurado.

• As réplicas secundárias em um banco de dados de distribuição AG podem ser síncronas ou assíncronas. O modo síncrono é recomendado e preferido.

• Não há suporte para replicação transacional bidirecional.

• O SSMS não mostra o Banco de Dados de Distribuição como sincronizado/sincronizado quando o banco de dados de distribuição é adicionado a um grupo de disponibilidade.

  Observação

  Antes de executar qualquer 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 banco de dados de distribuição AG devem ser legíveis. Se uma réplica secundária não for legível, as propriedades do distribuidor no SQL Server Management Studio na réplica secundária específica não poderão ser acessadas, no entanto, a replicação continuará a funcionar corretamente.

• Todos os nós no banco de dados de distribuição AG devem usar a mesma conta de domínio para executar o SQL Server Agent, e essa conta de domínio deve ter os mesmos privilégios em cada nó.

• Se algum agente de replicação for executado em uma conta proxy, a conta proxy precisará existir em cada nó do banco de dados de distribuição AG 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 banco de dados de distribuição AG.

• Faça alterações nos trabalhos de replicação através de procedimentos armazenados no msdb ou utilizando o SQL Server Management Studio em todas as réplicas que participam do Grupo de Disponibilidade (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 identificador em todas as réplicas. Se o perfil não existir em uma réplica secundária, você poderá obter erros de violação de chave primária após o failover. Você provavelmente precisará reinicializar a assinatura da publicação para resolver os erros.

• 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 fichas de propriedades para outros propósitos.

• A configuração do AG para bancos de dados de distribuição só pode ser feita por meio de scripts.

• A configuração de bases de dados de distribuição numa AG deve constituir uma nova configuração de replicação. Não há suporte para mudar um banco de dados de distribuição existente para um Grupo de Disponibilidade. Além disso, uma vez que um banco de dados de distribuição é retirado de um AG, ele não pode mais funcionar como um banco de dados de distribuição válido e deve ser descartado.

Arquitetura de configuração

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

• DIST1, DIST2, DIST3 são servidores distribuidores;
• PUB é servidor de publicação.
• Depois que o banco de dados de distribuição AG é formado, o nome do ouvinte é DISTLISTENER;
• DIST1 destina-se a ser a réplica primária inicial do banco de dados de distribuição AG.

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

Este exemplo configura um novo distribuidor e editor e coloca a base de dados de distribuição em um AG.

Fluxo de trabalho dos distribuidores

1. Configure DIST1, DIST2, DIST3 como distribuidor com sp_adddistributor @@servername. Especifique a senha para distributor_admin através do @password. O @password deve ser idêntico em DIST1, DIST2, DIST3.

2. Crie o banco de dados de distribuição no 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.

3. Crie um AG para a base de dados distribution com réplicas em DIST1, DIST2 e DIST3. De preferência, todas as réplicas são síncronas. Configure réplicas secundárias para serem legíveis ou permitirem 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.

4. Configure um ouvinte chamado DISTLISTENER para o AG.

5. Para recuperação e para permitir o truncamento do log de transações, configure backups completos e de log de transações.

6. Em DIST2 e DIST3, execute:

   EXEC sys.sp_adddistributiondb @database = 'distribution';
   

7. Para adicionar PUB como editor no 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.

8. Em 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 puder ser lida como secundária, faça o failover para 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 Publisher

Para adicionar o listener AG da base de dados distribution como distribuidor, em PUB, execute:

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

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

Remover distribuidor e editor

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

Fluxo de trabalho do Publisher

No PUB, remova todas as assinaturas e publicações deste servidor e, em seguida, ligue para sp_dropdistributor.

Fluxo de trabalho dos distribuidores

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

1. Em DIST2 e DIST3, execute:

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

2. No DIST1, execute o comando:

   EXEC sys.sp_dropdistpublisher @publisher = 'PUB';
   

3. Eliminar a AG.

4. Em DIST2 e DIST3, altere o banco de dados distribution para o modo read_write restaurando o banco de dados com recuperação.

   RESTORE DATABASE [distribution] WITH RECOVERY, KEEP_REPLICATION;
   

5. Para excluir a base 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.

1. Para eliminar a base de dados distribution em DIST1, execute

   EXEC sys.sp_dropdistributiondb @database = 'distribution';
   

2. Se não houver outros bancos de dados de distribuição no AG, execute sp_dropdistributor em DIST1, DIST2 e DIST3.

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

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

Fluxo de trabalho dos distribuidores

1. DIST3 deve ser configurado como distribuidor através de sp_adddistributor @@servername. A senha para distributor_admin deve ser especificada através @password parâmetro. A senha deve ser a mesma especificada para DIST1 e DIST2.

2. Adicione DIST3 ao AG para o banco de dados de distribuição atual.

3. No DIST3, corra:

   EXEC sys.sp_adddistributiondb @database = 'distribution';
   

4. Em 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 acessível como secundária, execute o failover de modo a que a réplica se torne a principal e execute o seguinte comando:

   EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
   

   O valor de @working_directory deve ser o mesmo que foi especificado para DIST1 e DIST2.

5. No DIST3, você deve recriar Servidores Vinculados para os assinantes.

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

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

Fluxo de trabalho dos distribuidores

1. Certifique-se de que DIST3 é um secundário para a base de dados distribution AG.

2. Remova DIST3 do banco de dados distribution AG.

3. No DIST3, altere o banco de dados distribution para o modo read_write restaurando o banco de dados com recuperação. Por exemplo, execute o seguinte comando:

   RESTORE DATABASE distribution WITH RECOVERY, KEEP_REPLICATION;
   

4. Para remover todos os trabalhos órfãos no DIST3, execute:

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

5. No DIST3, corra:

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

6. Na plataforma DIST3, execute o comando:

   EXEC sys.sp_dropdistributor;
   

Remover um editor da base de dados de distribuição AG

Este exemplo remove um editor do banco de dados de distribuição atual AG de um distribuidor, enquanto o restante dos editores atendidos por esse banco de dados de distribuição AG não são afetados. Neste exemplo, uma configuração existente tem a base de dados de distribuição num AG. DIST1, DIST2 e DIST3 são os distribuidores, distribution é o banco de dados de distribuição em AG, e PUB1 e PUB2 são os editores atendidos por distribution banco de dados. O exemplo remove PUB1 desses distribuidores.

Fluxo de trabalho do Publisher

Em PUB1, elimine todas as assinaturas e publicações deste editor e execute o sp_dropdistributor.

Fluxo de trabalho do distribuidor

DIST1 é o primário atual da base de dados AG distribution.

1. Em DIST2 e DIST3, execute:

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

2. Na DIST1, execute o comando:

   EXEC sys.sp_dropdistpublisher @publisher = 'PUB1';
   

3. Neste ponto, pode haver empregos órfãos relacionados ao PUB1 no DIST2 ou DIST3. Sempre que ocorrer um failover para o DIST2 e o DIST3, as tarefas órfãs relacionadas a todas as publicações do PUB1 serão removidas pela tarefa Monitor and sync replication agent jobs.

Adicionar subscrição

Este exemplo trata da configuração correta das informações do assinante entre os distribuidores. O exemplo adiciona um assinante. DIST1 é a réplica primária atual do banco de dados de distribuição no AG, 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 Publisher

No PUB, adicione a subscrição como faria normalmente ao subscritor SUB.

Fluxo de trabalho do distribuidor

Em DIST2 e DIST3, adicione um servidor vinculado para 'SUB' se ele não estiver registrado anteriormente com DIST2 ou DIST3. Abaixo está um exemplo de TSQL para criação de 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 subscrição pull

Fluxo de trabalho do assinante

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

Exemplo de criação de um banco de dados de distribuição usando T-SQL no AG

O script a seguir habilita 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;

Conteúdo relacionado

• Publicar dados e objetos de banco de dados
• Proteger o Distribuidor
• Exibir e modificar propriedades de distribuidor e publicador
• Desativar publicação e distribuição
• habilitar um banco de dados para replicação (SQL Server Management Studio)