Replicação transacional com a Instância Gerenciada de SQL do Azure

  • Artigo

Aplica-se a:Instância Gerenciada de SQL do Azure

A replicação transacional é um recurso da Instância Gerenciada de SQL do Azure e do SQL Server que permite replicar dados de uma tabela na Instância Gerenciada de SQL do Azure ou em uma instância de SQL Server para tabelas colocadas em bancos de dados remotos. Esse recurso permite que você sincronize várias tabelas em bancos de dados diferentes.

Visão geral

Você pode usar a replicação transacional para enviar por push as alterações feitas em uma instância gerenciada do SQL do Azure para:

  • Um banco de dados SQL Server (local ou uma máquina virtual do Azure)
  • Um banco de dados no Banco de Dados SQL do Azure
  • Um banco de dados de instância na Instância Gerenciada de SQL do Azure

Observação

Para usar todos os recursos da Instância Gerenciada de SQL do Azure, você deve estar usando as versões mais recentes do SSMS (SQL Server Management Studio) e do SSDT (SQL Server Data Tools).

Componentes

Os principais componentes na replicação transacional são o Publicador, o Distribuidore o Assinante, conforme mostrado na figura a seguir:

Diagram of replication with Azure SQL.

Função Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure
Publicador Não Sim
Distribuidor Não Sim
Assinante de pull Não Sim
Assinante de push Sim Sim

O Publicador publica as alterações feitas em algumas tabelas (artigos), enviando as atualizações para o Distribuidor. O publicador pode ser uma instância gerenciada do SQL do Azure ou uma instância do SQL Server.

O Distribuidor coleta as alterações nos artigos de um Publicador e as distribui aos Assinantes. O Distribuidor pode ser uma Instância Gerenciada de SQL do Azure ou uma instância do SQL Server (qualquer versão, desde que seja igual ou maior do que a versão do Editor).

O Assinante recebe as alterações feitas no Publicador. Uma instância do SQL Server e uma Instância Gerenciada de SQL do Azure podem ser assinantes push e pull, embora não haja suporte para uma assinatura pull quando o distribuidor é um Instância Gerenciada de SQL do Azure e o assinante não é. Um banco de dados no Banco de Dados SQL do Azure só pode ser um assinante push.

A Instância Gerenciada de SQL do Azure pode dar suporte a um assinante das seguintes versões do SQL Server:

Observação

Para outras versões do SQL Server que não dão suporte à publicação para objetos no Azure, é possível utilizar o método de republicação de dados para mover dados para versões mais recentes do SQL Server.

A tentativa de configurar a replicação usando uma versão mais antiga pode resultar no erro MSSQL_REPL20084 (O processo não pôde se conectar ao Assinante.) e MSSQL_REPL40532 (Não é possível abrir o servidor <nome> solicitado pelo logon. Falha no logon).

Tipos de replicação

Existem diferentes tipos de replicação:

Replicação Banco de Dados SQL do Azure Instância Gerenciada do SQL do Azure
Transacional Padrão Sim (somente como assinante) Sim
Instantâneo Sim (somente como assinante) Sim
Replicação de mesclagem Não No
Ponto a ponto Não Não
Bidirecional Não Sim
Assinaturas atualizáveis Não Não

Matriz de suporte

A matriz de suporte de replicação transacional para a Instância Gerenciada de SQL do Azure é a mesma para SQL Server.

Publicador Distribuidor Assinante
SQL Server 2022 SQL Server 2022 SQL Server 2022
SQL Server 2019
Microsoft SQL Server 2017
SQL Server 2019 SQL Server 2022
SQL Server 2019		 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
Microsoft SQL Server 2017 SQL Server 2022
SQL Server 2019
Microsoft SQL Server 2017		 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2016 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016		 SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2014 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014		 Microsoft SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2012 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012		 SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2008 R2
SQL Server 2008		 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008		 SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008

Quando usar

A replicação transacional é útil nos seguintes cenários:

  • Publique alterações feitas em uma ou mais tabelas em um banco de dados e as distribua para um ou vários bancos de dados em uma instância do SQL Server ou banco de dados do SQL do Azure que assinou as alterações.
  • Mantenha vários bancos de dados distribuídos em estado sincronizado.
  • Por meio da publicação contínua das alterações, migre bancos de dados de uma instância do SQL Server ou Instância Gerenciada de SQL do Azure para outro banco de dados.

Comparar a sincronização de dados com a replicação transacional

Categoria Sincronização de Dados Replicação transacional
Vantagens – Suporte ativo-ativo
– Bidirecional entre o Banco de Dados SQL do Azure e o local		 – Menor latência
– Consistência transacional
– Reutilização da topologia existente após a migração
Desvantagens – Não há consistência transacional
– Maior impacto do desempenho		 – Não pode publicar do Banco de Dados SQL do Azure
– Alto custo de manutenção

Configurações comuns

De modo geral, o publicador e o distribuidor devem ambos estar na nuvem ou ser locais. Há suporte para as seguintes configurações:

Publicador com distribuidor local na Instância Gerenciada de SQL

Single instance as Publisher and Distributor.

O publicador e o distribuidor são configurados em uma única Instância Gerenciada de SQL e estão distribuindo as alterações para outra Instância Gerenciada de SQL, Banco de Dados do SQL ou instância do SQL Server.

Publicador com distribuidor remoto em uma Instância Gerenciada de SQL

Nessa configuração, uma instância gerenciada publica as alterações em um distribuidor colocado em outra Instância Gerenciada do SQL que pode atender a muitas instâncias gerenciadas do SQL de origem e distribuir as alterações para um ou vários destinos no banco de dados SQL do Azure, Instância Gerenciada de SQL do Azure ou SQL Server.

Separate instances for Publisher and Distributor.

O publicador e o distribuidor são configurados em duas instâncias gerenciadas. Essa configuração tem algumas restrições:

  • As duas instâncias gerenciadas estão na mesma vNet.
  • As duas instâncias gerenciadas estão no mesmo local.

Publicador/Distribuidor local com assinante remoto

Azure SQL Database as subscriber.

Nessa configuração, um banco de dados no Banco de Dados do SQL do Azure ou Instância Gerenciada de SQL do Azure é um assinante. Essa configuração dá suporte à migração do local para o Azure. Se um assinante for um banco de dados no Banco de Dados SQL do Azure, ele deverá estar no modo de push.

Requisitos

  • Use a autenticação do SQL para conectividade entre os participantes da replicação.
  • Use um compartilhamento da Conta de Armazenamento do Azure para o diretório de trabalho usado pela replicação.
  • Abra a porta de saída TCP 445 nas regras de segurança de sub-rede para acessar o compartilhamento de arquivos do Azure.
  • Abra a porta de saída TCP 1433 quando o Instância Gerenciada do SQL for o Publicador/distribuidor e o Assinante não for. Talvez você também precise alterar a regra de segurança de saída NSG da Instância Gerenciada de SQL para allow_linkedserver_outbound a Marca de serviço de destino da porta 1433 de virtualnetwork para internet.
  • Coloque o publicador e o distribuidor na nuvem ou ambos no local.
  • Configure o emparelhamento VPN entre as redes virtuais dos participantes da replicação se as redes virtuais forem diferentes.

Observação

Você pode encontrar o erro 53 ao se conectar a um arquivo de armazenamento do Azure se a porta 445 de saída do grupo de segurança de rede (NSG) for bloqueada quando o distribuidor for um banco de dados da Instância Gerenciada de SQL do Azure e o assinante for local. Atualize a NSG da vNET para resolver esse problema.

Limitações

A replicação transacional tem algumas limitações específicas para Instância Gerenciada de SQL do Azure. Saiba mais sobre essas limitações nesta seção.

Os arquivos de instantâneo não são excluídos da Conta de Armazenamento do Azure

Instância Gerenciada de SQL do Azure está usando a Conta de Armazenamento do Azure configurada pelo usuário para instantâneo arquivos usados para replicação transacional. Ao contrário do SQL Server no ambiente local, a Instância Gerenciada de SQL do Azure não está excluindo arquivos de instantâneo da conta de armazenamento do Azure. Depois que os arquivos não forem mais necessários, você deverá excluí-los. Isso pode ser feito por meio da interface do Armazenamento do Azure no portal do Azure, Gerenciador de Armazenamento do Microsoft Azure ou por meio de clientes de linha de comando (Azure PowerShell ou CLI) ou API REST do Gerenciamento de Armazenamento do Azure.

Aqui está um exemplo de como você pode excluir o arquivo e como você pode excluir uma pasta vazia.

az storage file delete-batch --source <file_path> --account-key <account_key> --account-name <account_name>
az storage directory delete --name <directory_name> --share-name <share_name> --account-key <account_key> --account-name <account_name>

Número de agentes de distribuição em execução contínua

O número de agentes de distribuição configurados para execução contínua é limitado a 30 em Instância Gerenciada de SQL do Azure. Para ter mais agentes de distribuição, eles precisam estar em execução sob demanda ou com um agendamento definido. O agendamento pode ser definido com frequência diária e ocorrência a cada 10 segundos (ou mais), portanto, mesmo que não seja contínuo, você ainda pode ter um distribuidor que está introduzindo latência de apenas vários segundos. Quando um grande número de distribuidores é necessário, é recomendável usar a configuração agendada e não contínua.

Com grupos de failover

Há suporte para o uso da replicação transacional com instâncias que estão em um grupo de failover. No entanto, se você configurar a replicação antes de adicionar sua instância gerenciada do SQL a um grupo de failover, a replicação será pausada quando você começar a criar seu grupo de failover, e o monitor de replicação mostrará uma status de Replicated transactions are waiting for the next log backup or for mirroring partner to catch up. A replicação é retomada depois que o grupo de failover é criado com êxito.

Se uma Instância Gerenciada de SQL de publicador ou distribuidor estiver em um grupo de failover, o administrador da Instância Gerenciada de SQL deverá limpar todas as publicações no antigo primário e reconfigurá-las no novo primário após a ocorrência de um failover. As seguintes atividades são necessárias neste cenário:

  1. Pare todos os trabalhos de replicação em execução no banco de dados, se houver algum.

  2. Remova os metadados da assinatura do publicador executando o seguinte script no banco de dados do publicador. Substitua os valores <name of publication> e <name of subscriber>:

    EXEC sp_dropsubscription @publication = '<name of publication>',
    @article = 'all',
    @subscriber = '<name of subscriber>'

  3. Remova os metadados da assinatura do assinante. Execute o seguinte script no banco de dados de assinatura na Instância Gerenciada de SQL do assinante. Substitua o valor <full DNS of publisher>. Por exemplo example.ac2d23028af5.database.windows.net:

    EXEC sp_subscription_cleanup
   @publisher = N'<full DNS of publisher>',
   @publisher_db = N'<publisher database>',
   @publication = N'<name of publication>';

  4. Force a remoção de todos os objetos de replicação do publicador executando o seguinte script no banco de dados publicado:

    EXEC sp_removedbreplication;

  5. Force a remoção do distribuidor antigo da Instância Gerenciada de SQL primária original (se estiver fazendo failover novamente para um antigo primário que costumava ter um distribuidor). Execute o seguinte script no banco de dados master da Instância Gerenciada de SQL do distribuidor antigo:

    EXEC sp_dropdistributor 1, 1;

Se uma Instância Gerenciada de SQL do assinante estiver em um grupo de failover, a publicação deverá ser configurada para se conectar ao ponto de extremidade do ouvinte do grupo de failover para a instância gerenciada SQL do assinante. No caso de um failover, a ação subsequente pelo administrador da instância gerenciada SQL depende do tipo de failover ocorrido:

  • Para um failover sem perda de dados, a replicação continuará funcionando após o failover.
  • Para um failover com perda de dados, a replicação também funciona. Ele replica as alterações perdidas novamente.
  • Para um failover com perda de dados, mas a perda de dados está fora do período de retenção do banco de dado de distribuição, o administrador da instância gerenciada SQL precisa reinicializar o banco de dados de assinatura.

Solução de problemas comuns

Log de transações e replicação transacional

Em circunstâncias normais, o log de transcações é usado para registrar alterações dos dados em um banco de dados. As alterações são registradas no log de transações, aumentando o consumo de armazenamento de log. Existe também um processo automático que permite o truncamento seguro do log de transações, e esse processo reduz o espaço de armazenamento usado para o log. Quando a publicação para replicação transacional é configurada, o truncamento do log de transações é impedido até que o trabalho do leitor de log processe as alterações no log. Em algumas circunstâncias, o processamento do log de transações é efetivamente bloqueado, e esse estado pode causar o preenchimento de todo o armazenamento reservado para o log de transações. Quando não há espaço livre para o log de transações e não há mais espaço para o log de transações crescer, o log de transações está cheio. Nesse estado, o banco de dados não pode mais processar nenhuma carga de trabalho de gravação, tornando-se efetivamente um banco de dados somente leitura.

Agente de leitor de log desabilitado

Às vezes, a publicação de Replicação Transacional é configurada para um banco de dados, mas o agente de leitor de log não está configurado para execução. Quando isso acontece, as alterações estão se acumulando no log de transações e não estão sendo processadas. Isso provoca o crescimento constante do log de transações e, por fim, faz o log de transações ficar cheio. O usuário deve garantir que o trabalho do leitor de log existe e está ativo. A alternativa seria desabilitar a replicação transacional, se ela não for necessária.

Tempos limite de consulta do agente de leitor de log

Às vezes, o trabalho do leitor de log não consegue progredir efetivamente devido a tempos limite de consulta repetidos. Um modo de corrigir tempos limite de consulta é aumentar a configuração de tempo limite de consulta para o trabalho do agente de leitor de log.

O aumento do tempo limite de consulta para o trabalho do leitor de log pode ser realizado com o SSMS. No pesquisador de objetos, em SQL Server Agent, localize o trabalho que você deseja modificar. Primeiro, pare o trabalho e, em seguida, abra as propriedades dele. Localize step 2 e realize a edição. Acrescente o valor do comando com -QueryTimeout <timeout_in_seconds>. Para o valor de tempo limite de consulta, tente 21600 ou números maiores. Por fim, inicie o trabalho novamente.

O tamanho do armazenamento de logs alcançou o limite máximo de 2 TB

Quando o tamanho do armazenamento do log de transações alcança o limite máximo, que é de 2 TB, fisicamente os logs não podem aumentar mais do que isso. Nesse caso, a única mitigação disponível é marcar todas as transações que passarão pelo processo de replicação como processadas para permitir que o log de transações seja truncado. De forma efetiva, isso significa que as transações restantes no log não serão replicadas e que você precisará reinicializar a replicação.

Observação

Após realizar a mitigação, você precisará reinicializar a replicação, o que significa executar a replicação de todo o conjunto de dados novamente. Esse é o tamanho da operação de dados e pode ter uma execução prolongada com base na quantidade de dados que devem ser replicados.

Para executar a mitigação, primeiro é necessário parar o agente de leitor de log no distribuidor. Em seguida, você deve executar o procedimento armazenado sp_repldone com o sinalizador reset, definido como 1 no banco de dados publicador, para permitir o truncamento do log de transações. O comando deve ser desta forma: EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1. Depois disso, será necessário reinicializar a replicação.

Próximas etapas

Para obter mais informações sobre como configurar a replicação transacional, consulte os seguintes tutoriais:

Confira também