Configurar transações distribuídas para um grupo de disponibilidade Always On

Aplica-se a: SQL Server

O SQL Server 2017 (14.x) e versões posteriores suportam todas as transações distribuídas, incluindo bases de dados num grupo de disponibilidade. Este artigo explica como configurar um grupo de disponibilidade para transações distribuídas

Para garantir transações distribuídas, o grupo de disponibilidade deve ser configurado para registar bases de dados como gestores de recursos de transações distribuídos.

Numa transação distribuída, as aplicações clientes trabalham com o Coordenador de Transações Distribuídas da Microsoft (MSDTC ou DTC) para garantir consistência transacional em múltiplas fontes de dados. O DTC é um serviço disponível em sistemas operativos baseados em Windows Server suportados. Para uma transação distribuída, o DTC é o coordenador da transação. Normalmente, uma instância do SQL Server é o gestor de recursos. Quando uma base de dados está num grupo de disponibilidade, cada base de dados precisa de ser o seu próprio gestor de recursos.

O SQL Server não impede transações distribuídas para bases de dados num grupo de disponibilidade – mesmo quando o grupo de disponibilidade não está configurado para transações distribuídas. No entanto, quando um grupo de disponibilidade não está configurado para transações distribuídas, o failover pode não ter sucesso em algumas situações. Especificamente, a nova instância principal réplica do SQL Server pode não conseguir obter o resultado da transação do DTC. Para permitir que a instância do SQL Server obtenha o resultado das transações em dúvida do DTC após o failover, configure o grupo de disponibilidade para transações distribuídas.

O DTC não está envolvido no processamento de grupos de disponibilidade, a menos que uma base de dados também seja membro de um Cluster de Failover. Dentro de um grupo de disponibilidade, a consistência entre réplicas é mantida pela lógica do grupo de disponibilidade: o primário não conclui a confirmação nem a confirma ao chamador até que o secundário confirme que persistiu os registos do registo de transações em armazenamento persistente. Só então o primário declara a transação concluída. No modo assíncrono, não esperamos que o secundário faça ack, e existe explicitamente a possibilidade de perda de uma pequena quantidade de dados.

Prerequisites

Antes de configurar um grupo de disponibilidade para suportar transações distribuídas, deve cumprir os seguintes pré-requisitos:

• Todas as instâncias do SQL Server que participam na transação distribuída devem ser versões do SQL Server 2016 (13.x) ou posteriores.

• Os grupos de disponibilidade devem estar a correr no Windows Server 2012 R2 ou versões posteriores. Para o Windows Server 2012 R2, você deve instalar a atualização no KB3090973.

Crie um grupo de disponibilidade para transações distribuídas

Configure um grupo de disponibilidade para suportar transações distribuídas. Defina o grupo de disponibilidade para permitir que cada base de dados se registre como gestor de recursos. Este artigo explica como configurar um grupo de disponibilidade para que cada base de dados possa ser um gestor de recursos no DTC.

Pode criar um grupo de disponibilidade para transações distribuídas no SQL Server 2016 (13.x) ou versões posteriores. Para criar um grupo de disponibilidade para transações distribuídas, inclua DTC_SUPPORT = PER_DB na definição do grupo de disponibilidade. O script seguinte cria um grupo de disponibilidade para transações distribuídas.

CREATE AVAILABILITY
GROUP MyAG
WITH (DTC_SUPPORT = PER_DB)
FOR DATABASE DB1,
    DB2 REPLICA
ON 'Server1' WITH (
   ENDPOINT_URL = 'TCP://SERVER1.corp.com:5022',
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   FAILOVER_MODE = AUTOMATIC
),
'Server2' WITH (
   ENDPOINT_URL = 'TCP://SERVER2.corp.com:5022',
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   FAILOVER_MODE = AUTOMATIC
);

Alterar um grupo de disponibilidade para transações distribuídas

Pode alterar um grupo de disponibilidade para transações distribuídas no SQL Server 2017 (14.x) ou versões posteriores. Para alterar um grupo de disponibilidade para transações distribuídas, inclua DTC_SUPPORT = PER_DB no ALTER AVAILABILITY GROUP script. O script de exemplo altera o grupo de disponibilidade para suportar transações distribuídas.

ALTER AVAILABILITY GROUP MyaAG
SET (DTC_SUPPORT = PER_DB);

Para desativar transações distribuídas, utilize o seguinte comando Transact-SQL:

ALTER AVAILABILITY GROUP MyaAG
SET (DTC_SUPPORT = NONE);

Transações distribuídas - conceitos técnicos

Uma transação distribuída abrange duas ou mais bases de dados. Como gestor de transações, o DTC coordena a transação entre as instâncias do SQL Server e outras fontes de dados. Cada instância do motor de base de dados SQL Server pode funcionar como gestor de recursos. Quando um grupo de disponibilidade está configurado com DTC_SUPPORT = PER_DB, as bases de dados podem funcionar como gestores de recursos. Para mais informações, consulte a documentação MSDTC.

Uma transação com duas ou mais bases de dados numa única instância do motor de base de dados é, na verdade, uma transação distribuída. A instância gerencia a transação distribuída internamente; para o usuário, ele opera como uma transação local. O SQL Server 2017 (14.x) promove todas as transações entre bases de dados para DTC quando as bases de dados estão num grupo de disponibilidade configurado com DTC_SUPPORT = PER_DB - mesmo dentro de uma única instância do SQL Server.

Na aplicação, uma transação distribuída é gerida de forma muito semelhante a uma transação local. No final da transação, o aplicativo solicita que a transação seja confirmada ou revertida. O gestor de transações deve gerir uma confirmação distribuída de forma diferente, para minimizar o risco de uma falha de rede levar alguns gestores de recursos a confirmar com sucesso, enquanto outros anulam a transação. Isto é conseguido através da gestão do processo de compromisso em duas fases (a fase de preparação e a fase de confirmação), que é conhecida como uma confirmação em duas fases.

• Fase de preparação

  Quando o gerenciador de transações recebe uma solicitação de confirmação, ele envia um comando prepare para todos os gerentes de recursos envolvidos na transação. Cada gestor de recursos faz então tudo o que é necessário para tornar a transação durável, e todos os buffers que contêm imagens de registo da transação são esvaziados para o disco. À medida que cada gestor de recursos completa a fase de preparação, devolve o sucesso ou fracasso da fase de preparação ao gestor de transações.

• Fase de commit

  Se o gestor de transações receber preparações bem-sucedidas de todos os gestores de recursos, enviará comandos de confirmação para cada gestor de recursos. Os gerentes de recursos podem então concluir a confirmação. Se todos os gerentes de recursos relatarem uma confirmação bem-sucedida, o gerente de transações enviará uma notificação de êxito para o aplicativo. Se algum gerente de recursos relatar uma falha na preparação, o gerenciador de transações enviará um comando de reversão para cada gerente de recursos e indicará a falha da confirmação para o aplicativo.

Passos detalhados

A lista seguinte explica como a aplicação funciona com o DTC para completar transações distribuídas.

1. A instância do SQL Server inscreve-se na transação DTC. Isto pode acontecer quando há mais do que um gestor de recursos na transação ou se o cliente solicitar que uma transação seja promovida a transação DTC.
2. O cliente executa algumas operações na instância do SQL Server no âmbito de uma transação DTC.
3. O cliente efetua a confirmação ou a anulação da transação DTC.
   • Se o cliente ordenar o cancelamento, a transação é imediatamente cancelada.
   • Se o cliente emitir um commit, o DTC inicia o protocolo de commit em duas fases pedindo a todos os gestores de recursos da transação que preparem a transação.
4. O DTC informa todos os gestores de recursos para comprometerem a transação depois de todos reconhecerem com sucesso a fase de preparação. Se algo impedir o reconhecimento bem-sucedido, o DTC aborta a transação.

Efeitos da configuração de um grupo de disponibilidade para transações distribuídas

Cada entidade que participa numa transação distribuída é chamada gestor de recursos. Exemplos de gestores de recursos incluem:

• Uma instância do SQL Server.
• Uma base de dados num grupo de disponibilidade configurada para transações distribuídas.
• Serviço DTC - pode também ser um gestor de transações.
• Outras fontes de dados.

Para participar em transações distribuídas, uma instância do SQL Server inscreve-se num DTC. Normalmente, a instância do SQL Server regista-se com DTC no servidor local. Cada instância do SQL Server cria um gestor de recursos com um identificador único de gestor de recursos (RMID) e regista-o no DTC. Na configuração predefinida, todas as bases de dados numa instância do SQL Server usam o mesmo RMID.

Quando uma base de dados está num grupo de disponibilidade, a cópia de leitura-escrita da base de dados – ou réplica principal – pode ser transferida para uma instância diferente do SQL Server. Para suportar transações distribuídas durante este movimento, cada base de dados deve atuar como um gestor de recursos separado e ter um RMID único. Quando um grupo de disponibilidade tem DTC_SUPPORT = PER_DB, o SQL Server cria um gestor de recursos para cada base de dados e regista-se no DTC usando um RMID único. Nesta configuração, a base de dados é um gestor de recursos para transações DTC.

Para obter mais detalhes sobre transações distribuídas no SQL Server, consulte Distributed transactions

Gerir transações não resolvidas

O resultado das transações ativas que existe durante a alteração do RMID não pode ser recuperado após um failover. Isto acontece porque o RMID SQL Server utilizado para o registo e o RMID SQL Server utilizado para recuperar são diferentes. A alteração do RMID pode ocorrer nos seguintes casos:

• Altere DTC_SUPPORT para um grupo de disponibilidade.
• Adicionar ou remover uma base de dados de um grupo de disponibilidade.
• Eliminar um grupo de disponibilidade.

Nos casos anteriores, se a réplica primária fizer failover para uma nova instância do SQL Server, a instância tenta contactar o DTC para identificar o resultado da transação. O DTC não pode devolver o resultado porque o RMID que a base de dados usa para obter o resultado das transações em dúvida durante a recuperação não estava listado antes. Assim, a base de dados entra em estado SUSPEITO.

O novo registo de erros do SQL Server tem uma entrada como o seguinte exemplo:

Microsoft Distributed Transaction Coordinator (MSDTC)
failed to reenlist citing that the database RMID does
not match the RMID [xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx]
associated with the transaction.  Please manually resolve
the transaction.

SQL Server detected a DTC/KTM in-doubt transaction with UOW
{yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy}.Please resolve it
following the guideline for Troubleshooting DTC Transactions.

O exemplo anterior mostra que o DTC não conseguiu reativar a base de dados a partir da nova réplica primária na transação criada após o failover. A instância do SQL Server não consegue determinar o resultado da transação distribuída, por isso marca a base de dados como suspeita. A transação é marcada como uma unidade de trabalho (UOW) e identificada por um GUID. Para recuperar a base de dados, deve confirmar ou reverter manualmente a transação.

Execute apenas um dos seguintes scripts:

• Para confirmar a transação, atualize e execute o seguinte script - substitua o yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy pela transação em dúvida UOW da mensagem de erro anterior, e execute:

  KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH COMMIT;
  

• Para reverter a transação, atualize e execute o seguinte script - substitua o yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy pela transação em dúvida UOW da mensagem de erro anterior, e execute:

  KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH ROLLBACK;
  

Depois de confirmar ou reverter a transação, pode utilizar ALTER DATABASE para colocar a base de dados online. Atualize e execute o seguinte script - defina o nome da base de dados para o nome da base de dados suspeita:

ALTER DATABASE [DB1] SET ONLINE;

Para mais informações sobre como resolver transações em dúvida, consulte Resolver Transações Manualmente.

Conteúdo relacionado

• Transações distribuídas
• Grupos de disponibilidade Always On: interoperabilidade (SQL Server)
• Transações - grupos de disponibilidade e espelhamento de bases de dados
• Suporte para Transações XA
• Como funciona: Sessão/SPID (-2) para Transações DTC