Implementando o uso de transações distribuídas (MSDTC)

Artigo
17/01/2024

Introdução

Este artigo aborda os erros comuns ao manipular dados entre servidores SQL Server, através de stored procedures dentro de uma transação explícita ou implícita que deve utilizar o MS DTC (Microsoft Distributed Transaction Coordinator) para gerenciar e fornecer suporte adequado para execução de todas às transações distribuídas entre os servidores envolvidos (origem - destino).

Através deste recurso é possível habilitar a execução de instruções de leitura (SELECT) e manipulação de dados (INSERT, UPDATE ou DELETE), obtendo o retorno adequado, inclusive relacionando às mensagens de erro da instância SQL Server remota, relacionada à instrução T-SQL efetuada na origem.

Problemas Relacionados

Manter o MSDTC (ou apenas, DTC) desabilitado ou "não inicializado" irá impedir a execução de Transações Distribuídas entre servidores (veja a Imagem "1").

Nestes casos, alguns erros são comuns são:

Erro 7391 (A operação não pode ser realizada porque o provedor "x" para o linked server "y" não está habilitado para transações distribuídas) ou;
Erro 8501 (O MS DTC no servidor "x" não está disponível);

O Firewall do Windows também pode acarretar estes ou outros erros quando o DTC está habilitado no servidor de origem ou destino. Isto porque o DTC está bloqueado por padrão, impedindo qualquer execução na transação de dados.

Mesmo quando você inicializa uma transação distribuída é possível encontrar outras mensagens de erro caso a configuração do DTC tenha pendências ou falhas na sua parametrização.

Então é importante habilitar corretamente o acesso do DTC na sua rede e conceder às permissões de segurança adequadas à sua necessidade.

Imagem 1 - Erro ao executar uma Transação Distribuída não configurada (clique na imagem)

Segue abaixo um script T-SQL usao para demonstração deste Caso de Uso, para obter dados de uma stored procedure armazenada em uma Instância SQL remota e posteriormente inserindo seu resultado em uma variável Table na Instância SQL local:

DECLARE @TB_FICHA AS TABLE (
CPF CHAR(14),
CODIGO CHAR(10),
CONSULTOR VARCHAR(100),
EMAIL VARCHAR(100),
RECEBIMENTO DATETIME,
TIPO_CAIXA VARCHAR(50),
CAIXA VARCHAR(12),
TIPO_RECEBIMENTO VARCHAR(30)
);

INSERT INTO @TB_FICHA
EXEC [SeuLinkedServer].SeuBanco.dbo.INTEGRACAO_DOCUMENTOS '001026012006';
GO

Tipos de Erro Esperado

Você poderá ver abaixo uma lista de erros relacionados à falhas ou problemas de configuração no DTC:

Código	Mensagem de Erro
7303	Não foi possível inicializar fonte de dados objeto de provedor OLE DB
7306	Não foi possível abrir a tabela 'x' do provedor OLE DB 'y'
7314	O provedor OLE DB 'y' não possui a tabela 'x'
7321	Ocorreu um erro ao preparar uma consulta para execução para o provedor OLE DB
7356	Provedor OLE DB forneceu metadados inconsistentes para uma coluna. Informações de metadados foram alterados em tempo de execução
7357	Não foi possível processar o objeto 'x'. O provedor OLE DB 'y' indica que o objeto não tem colunas
7391	A operação não pôde ser executada porque o provedor OLE DB 'y' não suporta transações distribuídas
7392	Não foi possível iniciar uma transação para provedor OLE DB
7399	O provedor OLE DB 'x' apresentou um erro 'y'
7403	Não foi possível localizar entrada de registro para o provedor OLE DB
7413	Não foi possível executar um logon do Windows autenticado porque delegação não está disponível
8114	Erro ao converter o tipo de dados 'x' para 'y'
8501	O MS DTC não está disponível no servidor 'x'

Todas essas mensagens de erro são atribuídas ao Nível de Severidade "16" no SQL Server, ou seja, são erros de sintaxe ou configuração que podem ser corrigidos através de uma intervenção manual do responsável ou proprietário do script T-SQL e/ou do ambiente onde está sendo executado esta instrução.

Nota
Estas mensagens de erro são algumas possíveis quando o provedor de acesso aos dados for um OLE DB. Caso você conheça outras mensagens de erro ou exista mudanças nas mensagens existentes, fique à vontade para alterar este artigo.

Configurando o DTC

A configuração do DTC é vinculada ao Component Services do Windows, localizado em "Painel de Controle" => "Ferramentas Administrativas". Você também poderá acessar através da Inicialização Rápida (Executar) ou Caixa de Pesquisa com o comando "dcomcnfg".

Através desta interface podemos parametrizar facilmente o comportamento das Transações Distribuídas em nosso ambiente (veja na Imagem "2"). Para configurar as permissões do DTC, é preciso selecionar "Computers" => "My Computer" => "Distributed Transaction Coordinator" => "Local DTC".

Ao encontrar o ícone "Local DTC", clique com o botão direito e selecione "Properties".

Imagem 2 - Configurando o DTC através do console no Component Services (clique na imagem)

Os ajustes necessários para habilitar a execução de Transações Distribuídas são relacionados a aba "Security" (veja a Imagem "3").

É importante habilitar duas opções: "Network DTC Access" e "Allow Remote Client".

Após esta configuração, já é possível manter um processo de Transações Remoto entre dois servidores SQL Server diferentes.

As demais configurações serão necessárias para obter retorno de dados e/ou efetivar manipulações de dados diversas no servidor de destino.

Então uma rede fechada poderá contar com às configurações "Allow Inbound" e "Allow Outbound", sem uma autenticação requerida ("No Authentucation Required") para permitir o tráfego de dados entre os servidores SQL Server.

Imagem 3 - Habilitando Permissões de Acesso Remoto (clique na imagem)

Por último, a opção "Enable XA Transactions" fornece a inicialização automática para gerenciamento de transações de outros tipos de provedores de dados e também por outros SGDB compatíveis com XA Transactions.

Criando Exceções no Firewall do Windows

Você poderá acessar e habilitar sua Instância do SQL Server para executar o DTC, concedendo permissão no Firewall do Windows em seus servidores de origem e destino.

Para isso é necessário você acessar o "Painel de Controle" e selecionar "Windows Firewall". Nas opções Avançadas (veja a Imagem "4"), é possível conceder a permissão de execução para o DTC selecionando a opção "Distributed Transaction Coordinator" e indicando que esta aplicação pode ser instanciada para comunicação externa nas redes "Private" e "Public".

Imagem 4 - Erro ao executar uma Transação Distribuída não configurada (clique na imagem)

É necessário conceder esta permissão tanto no servidor onde está sua Instância do SQL Server na origem, como no servidor de destino (seja o SGDB o que for).

Com todas as permissões necessárias concedidas e o DTC configurado adequadamente, é possível executar novamente o script T-SQL e obter o retorno da stored procedure como indicado no Caso de Uso no início deste artigo (veja na Imagem "5"). Agora o resultado esperado foi obtido com sucesso através da inclusão de 1.000 registros na variável Table @TB_FICHA.

Imagem 5 - Erro ao executar uma Transação Distribuída não configurada (clique na imagem)

Todos os procedimentos de manipulação de dados e retorno de uma consulta, nesta stored procedure foram executadas com sucesso no servidor remoto (destino).

Conclusão

Habilitar uma ou mais Instâncias SQL para executar Transações Distribuídas pode ser um facilitador na integração de informações, consultando e manipulando dados.

É importante ter em mente o que é necessário para atender seus requisitos para obter e manter seus dados atualizados em diferentes servidores, uma vez que este recurso garante a identificação de cada Transação para finalizar seu processamento seja qual for o resultado: sucesso (obtendo um retorno ou apenas manipulando dados no destino) ou falha (indicando uma mensagem de erro, interrompendo a execução na origem).

Para cada necessidade, uma permissão de execução deve ser implementada no DTC local, além da necessidade de habilitar o Firewall para permitir cada um destes tipos de execução. Definir e implementar corretamente apenas os acessos necessários é um fator essêncial para segurança de todo seu ambiente.

Compartilhar via