Criar recurso DTC clusterizado para um grupo de disponibilidade Always On
Aplica-se a:
SQL Server – Somente Windows
Este artigo descreve uma configuração completa de um recurso de controle DTC clusterizado para o AG (grupo de disponibilidade) Always On do SQL Server. A configuração completa pode levar até uma hora para ser concluída.
O passo a passo cria um recurso DTC clusterizado e os AGs do SQL Server para se alinharem aos requisitos descritos em Como criar clusters do serviço DTC para um grupo de disponibilidade do SQL Server.
O passo a passo usa scripts do PowerShell e T-SQL (Transact-SQL). Muitos dos scripts T-SQL exigem que o Modo SQLCMD seja habilitado. Para obter mais informações sobre o Modo SQLCMD, consulte Editar scripts SQLCMD com o Editor de Consultas. O módulo do PowerShell FailoverClusters deve ser importado. Para obter mais informações sobre como importar um módulo do PowerShell, confira Importar um módulo do PowerShell. Este passo a passo se baseia nas seguintes opções de configuração:
- Todos os requisitos de Pré-requisitos, restrições e recomendações para grupos de disponibilidade Always On são atendidos.
- O domínio é
contoso.lab. - O usuário tem a permissão de objetos Create Computer na UO em que o recurso de Nome de Rede do DTC será criado.
- O usuário é um usuário de domínio com direitos de administrador em todos os nós do cluster.
- Um compartilhamento de arquivos chamado
sqlbackupsfoi criado para backups. - As instâncias padrão do SQL Server são nomeadas
SQLNODE1eSQLNODE2. - A mesma conta de serviço é usada em todas as instâncias do SQL Server.
- O usuário é um membro da função fixa sysadmin do SQL Server em todas as instâncias do SQL Server.
- O resultado padrão das transações que o DTC não puder resolver será definido como
presume commit. - O ponto de extremidade de espelhamento usará a porta
5022. - Não existem outros AGs nem recursos DTC clusterizados.
- Detalhes do cluster (existente):
- Nome:
Cluster - Nome da rede:
Cluster Network 1 - Nós:
SQLNODE1, SQLNODE2 - Armazenamento compartilhado:
Cluster Disk 3(pertencentes aSQLNODE1)
- Nome:
- Detalhes do cluster (a ser criado):
- Recurso de nome de rede:
DTCnet1 - Recurso de nome de rede do DTC:
DTC1 - Recurso Disco Físico do DTC:
DTCDisk1 - Recurso de IP e sub-rede do DTC:
192.168.2.54,255.255.255.0 - Recurso de IP do DTC:
DTCIP1
- Recurso de nome de rede:
1. Verificar sistema operacional
Para as transações distribuídas com suporte, o Grupos de disponibilidade Always On deve ser executado no Windows Server 2012 R2 ou versões posteriores. Para o Windows Server 2012 R2, é necessário instalar a atualização na KB3090973. Esse script verifica a versão do sistema operacional e especifica se o hotfix 3090973 precisa ser instalado. Execute o Script do PowerShell a seguir no SQLNODE1.
# A few OS checks
<#
Script:
1) is re-runnable
2) will work on any node in the cluster, and
3) will execute against every node in the cluster
#>
$nodes = (Get-ClusterNode).Name;
foreach ($node in $nodes) {
# At least 2012 R2
$os = (Get-WmiObject -class Win32_OperatingSystem -ComputerName $node).caption;
if ($os -like "*2012 R2*" -or $os -like "*2016*" -or $os -like "*2019*" -or $os -like "*2022*") {
Write-Host "$os is supported on $node.";
}
else {
Write-Host "STOP! $os is not supported on $node.";
}
# KB 3090973
if ($os -like "*2012 R2*") {
$kb = Get-Hotfix -ComputerName $node | Where { $_.HotFixID -eq 'KB3090973' };
if ($kb) {
Write-Host "KB3090973 is installed on $node."
}
else {
Write-Host "HotFixID KB3090973 must be applied on $node. See https://support.microsoft.com/help/3090973 for additional information and to download the hotfix.";
}
}
else {
Write-Host "KB3090973 does not apply to $os on $node."
}
}
2. Configurar regras de firewall
Esse script configura o firewall para permitir o tráfego do DTC em cada SQL Server que hospeda uma réplica do AG e em qualquer outro servidor que participe da transação distribuída. O script também configura o firewall para permitir conexões para o ponto de extremidade de espelhamento de banco de dados. Execute o Script do PowerShell a seguir no SQLNODE1.
# Configure Firewall
<#
Script:
1) is re-runnable
2) will work on any node in the cluster, and
3) will execute against every node in the cluster
#>
$nodes = (Get-ClusterNode).Name;
foreach ($node in $nodes) {
Get-NetFirewallRule -CimSession $node -DisplayGroup 'Distributed Transaction Coordinator' -Enabled False -ErrorAction SilentlyContinue | Enable-NetFirewallRule;
New-NetFirewallRule -CimSession $node -DisplayName 'SQL Server Mirroring' -Description 'Port 5022 for SQL Server Mirroring' -Action Allow -Direction Inbound -Protocol TCP -LocalPort 5022 -RemotePort Any -LocalAddress Any -RemoteAddress Any;
};
3. Configurar in-doubt xact resolution
Esse script configura a opção in-doubt xact resolution de configuração do servidor como "presume commit" para as transações incertas.
Execute o script T-SQL a seguir no SSMS (SQL Server Management Studio) para SQLNODE1, no modo SQLCMD.
/*******************************************************************
Execute script in its entirety on SQLNODE1 in SQLCMD mode
*******************************************************************/
-- Configure in-doubt xact resolution on all SQL Server instances to presume commit
IF (
SELECT CAST(value_in_use AS BIT)
FROM sys.configurations WITH (NOLOCK)
WHERE [name] = N'show advanced options'
) = 0
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
END
-- Configure the server to presume commit for in-doubt transactions.
IF (
SELECT CAST(value AS BIT)
FROM sys.configurations WITH (NOLOCK)
WHERE [name] = N'in-doubt xact resolution'
) <> 1
BEGIN
EXEC sp_configure 'in-doubt xact resolution', 1;
RECONFIGURE;
END
GO
-----------------------------------------------------------------------------
:connect SQLNODE2
IF (
SELECT CAST(value_in_use AS BIT)
FROM sys.configurations WITH (NOLOCK)
WHERE [name] = N'show advanced options'
) = 0
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
END
-- Configure the server to presume commit for in-doubt transactions.
IF (
SELECT CAST(value AS BIT)
FROM sys.configurations WITH (NOLOCK)
WHERE [name] = N'in-doubt xact resolution'
) <> 1
BEGIN
EXEC sp_configure 'in-doubt xact resolution', 1;
RECONFIGURE;
END
GO
4. Criar bancos de dados de teste
O script cria um banco de dados chamado AG1 no SQLNODE1 e um banco de dados chamado dtcDemoAG1 no SQLNODE2. Execute o script T-SQL a seguir no SSMS para SQLNODE1 no modo SQLCMD.
/*******************************************************************
Execute script in its entirety on SQLNODE1 in SQLCMD mode
*******************************************************************/
-- On SQLNODE1
USE master;
GO
SET NOCOUNT ON;
IF EXISTS (
SELECT *
FROM sys.databases
WHERE name = N'AG1'
)
BEGIN
DROP DATABASE AG1;
END
GO
CREATE DATABASE AG1;
GO
ALTER DATABASE AG1
SET RECOVERY FULL WITH NO_WAIT;
ALTER AUTHORIZATION ON DATABASE::AG1 TO sa;
GO
USE AG1;
CREATE TABLE [dbo].[Names] (
[Name] [varchar](64) NULL,
[EditDate] DATETIME
);
INSERT [dbo].[Names]
VALUES ('AG1', GETDATE());
GO
-- Against SQNODE2
:connect SQLNODE2
USE master;
GO
SET NOCOUNT ON;
IF EXISTS (
SELECT *
FROM sys.databases
WHERE name = N'dtcDemoAG1'
)
BEGIN
DROP DATABASE dtcDemoAG1;
END
GO
CREATE DATABASE dtcDemoAG1;
GO
ALTER DATABASE dtcDemoAG1
SET RECOVERY SIMPLE WITH NO_WAIT;
ALTER AUTHORIZATION ON DATABASE::dtcDemoAG1 TO sa;
GO
USE dtcDemoAG1;
GO
CREATE TABLE [dbo].[Names] (
[Name] [varchar](64) NULL,
[EditDate] DATETIME
);
GO
5. Criar pontos de extremidade
Esse script cria um ponto de extremidade chamado AG1_endpoint que escuta na porta TCP 5022. Execute o script T-SQL a seguir no SSMS para SQLNODE1 no modo SQLCMD.
/**********************************************
Execute on SQLNODE1 in SQLCMD mode
**********************************************/
-- Create endpoint on the server instance that hosts the primary replica:
IF NOT EXISTS (SELECT * FROM sys.database_mirroring_endpoints)
BEGIN
CREATE ENDPOINT AG1_endpoint AUTHORIZATION [sa]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING(ROLE = ALL);
END
GO
-----------------------------------------------------------------------------
:connect SQLNODE2
IF NOT EXISTS (SELECT *FROM sys.database_mirroring_endpoints)
BEGIN
CREATE ENDPOINT AG1_endpoint AUTHORIZATION [sa]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING(ROLE = ALL);
END
GO
6. Preparar bancos de dados para o grupo de disponibilidade
O script faz backup do AG1 no SQLNODE1 e o restaurano SQLNODE2. Execute o script T-SQL a seguir no SSMS para SQLNODE1 no modo SQLCMD.
/*******************************************************************
Execute script in its entirety on SQLNODE1 in SQLCMD mode
*******************************************************************/
-- Backup database
BACKUP DATABASE AG1 TO DISK = N'\\sqlnode1\sqlbackups\AG1.bak'
WITH FORMAT, STATS = 10;
-- Backup transaction log
BACKUP LOG AG1 TO DISK = N'\\sqlnode1\sqlbackups\AG1_Log.bak'
WITH FORMAT, STATS = 10;
GO
-- Restore database and logs on secondary WITH NORECOVERY
:connect SQLNODE2
USE [master];
GO
RESTORE DATABASE AG1
FROM DISK = N'\\sqlnode1\sqlbackups\AG1.bak'
WITH NORECOVERY, STATS = 10;
RESTORE LOG AG1
FROM DISK = N'\\sqlnode1\sqlbackups\AG1_Log.bak'
WITH NORECOVERY, STATS = 10;
GO
7. Criar grupo de disponibilidade
Os grupos de disponibilidade Always On devem ser criados com o comando CREATE AVAILABILITY GROUP e a cláusula WITH DTC_SUPPORT = PER_DB. No momento, não é possível alterar um AG existente. O Assistente de Novo Grupo de Disponibilidade não permite habilitar o suporte a DTC para um novo AG. O script a seguir cria o novo AG e une o secundário. Execute o script T-SQL a seguir no SSMS para SQLNODE1 no modo SQLCMD.
/*******************************************************************
Execute the script in its entirety on SQLNODE1 in SQLCMD mode
*******************************************************************/
-- Create availability group on SQLNODE1
USE master;
GO
CREATE AVAILABILITY GROUP DTCAG1
WITH (DTC_SUPPORT = PER_DB)
FOR DATABASE AG1 REPLICA ON
'SQLNODE1' WITH (
ENDPOINT_URL = 'TCP://SQLNODE1.contoso.lab:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
),
'SQLNODE2' WITH (
ENDPOINT_URL = 'TCP://SQLNODE2.contoso.lab:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
);
GO
-- SQLNODE2
-- Join a secondary replica to the availability group
:connect SQLNODE2
ALTER AVAILABILITY GROUP DTCag1 JOIN;
-- Join the database to the availability group
ALTER DATABASE AG1
SET HADR AVAILABILITY GROUP = DTCAG1;
GO
Do SQL Server 2016 (13.x) Service Pack 2 em diante, é possível alterar um AG para transações distribuídas. Para versões do SQL Server 2016 (13.x) anteriores ao Service Pack 2, você não pode habilitar o DTC em grupos de disponibilidade Always On existentes. SQL Server aceita a seguinte sintaxe para um AG existente:
USE master;
GO
ALTER AVAILABILITY GROUP <availability_group>
SET (DTC_Support = Per_DB);
No entanto, nenhuma alteração de configuração é feita. É possível confirmar a configuração dtc_support com a seguinte consulta T-SQL:
SELECT name, dtc_support FROM sys.availability_groups;
A única maneira de habilitar o suporte DTC em um AG para versões anteriores ao SQL Server 2016 (13.x) e versões anteriores ao Service Pack 2 é criando um AG usando Transact-SQL.
8. Preparar recursos de cluster
Esse script prepara os recursos dependentes do DTC: Disco e IP. O armazenamento compartilhado é adicionado ao Windows Cluster. Os recursos de rede são criados e o DTC é criado e transformado em um recurso para o AG. Execute o Script do PowerShell a seguir no SQLNODE1. Esse script é baseado no exemplo localizado em Como configurar corretamente o DTC para instâncias clusterizadas do SQL Server (Revisado).
# Create a clustered Microsoft Distributed Transaction Coordinator properly in the resource group with SQL Server
<#----------------------------------- Begin User Input -----------------------------------#>
$AGgrp = "DTCag1"; # Name of the WSFC resource group that will contain the DTC resource
$WSFC = (Get-Cluster).Name; # Windows Failover Cluster name
$DTCnetwk = "Cluster Network 1" # WSFC Network to use for the DTC IP address
$ClusterAvailableDisk = "Cluster Disk 3"; # Designated disk that can support failover clustering and is visible to all nodes but not yet part of the set of clustered disks
$DTCdisk = "DTCDisk1"; # Name of the disk to be used with DTC
$DTCipresnm = "DTCIP1"; # WSFC Friendly Name of the DTC's IP resource
$DTCipaddr = "192.168.2.54"; # IP address of the DTC resource
$DTCsubnet = "255.255.255.0"; # Subnet for the DTC IP address
$DTCnetnm = "DTCNet1"; # WSFC Friendly Name of the Network Name resource
$DTCresnm = "DTC1"; # Name of the WSFC DTC Network Name resource; Name must be unique in AD
<#------------------------------------ End User Input ------------------------------------#>
# Make a new disk available for use in a failover cluster.
Get-ClusterAvailableDisk | Where-Object { $_.Name -eq $ClusterAvailableDisk } | Add-ClusterDisk;
# Rename disk
$resource = Get-ClusterResource $ClusterAvailableDisk; $resource.Name = $DTCdisk;
# Create the IP resource
Add-ClusterResource -Name $DTCipresnm -ResourceType "IP Address" -Group $AGgrp;
# Set the network to use, IP address, and subnet
# All three have to be configured at the same time
$DTCIPres = Get-ClusterResource $DTCipresnm;
$ntwk = New-Object Microsoft.FailoverClusters.PowerShell.ClusterParameter $DTCipres, Network, $DTCnetwk;
$ipaddr = New-Object Microsoft.FailoverClusters.PowerShell.ClusterParameter $DTCipres, Address, $DTCipaddr;
$subnet = New-Object Microsoft.FailoverClusters.PowerShell.ClusterParameter $DTCipres, SubnetMask, $dtcsubnet;
$setdtcipparams = $ntwk, $ipaddr, $subnet;
$setdtcipparams | Set-ClusterParameter;
# Create the Network Name resource
Add-ClusterResource $DTCnetnm -ResourceType "Network Name" -Group $AGgrp;
# Set the value for the Network Name resource
Get-ClusterResource $DTCnetnm | Set-ClusterParameter DnsName $DTCresnm;
# Add the IP address as a depenency of the Network Name resource
Add-ClusterResourceDependency $DTCnetnm $DTCipresnm;
# Create the Distributed Transaction Coordinator resource
Add-ClusterResource $DTCresnm -ResourceType "Distributed Transaction Coordinator" -Group $AGgrp;
# Add the Network Name as a dependency of the DTC resource
Add-ClusterResourceDependency $DTCresnm $DTCnetnm;
# Move the disk into the resource group with SQL Server
Move-ClusterResource -Name $DTCdisk -Group $AGgrp;
# Add the disk as a dependency of the DTC resource
Add-ClusterResourceDependency $DTCresnm $DTCdisk;
# Bring the IP resource online
Start-ClusterResource $DTCipresnm;
# Bring the Network Name resource online
Start-ClusterResource $DTCnetnm;
# Bring the DTC resource online
Start-ClusterResource $DTCresnm;
9. Habilitar o acesso do DTC de rede
O script a seguir habilita o Acesso de DTC de Rede do serviço DTC clusterizado para permitir que computadores remotos se inscrevam em transações distribuídas pela rede. Execute o Script do PowerShell a seguir no SQLNODE1.
# Enable Network DTC access for the clustered DTC service
<#
Script:
1) is re-runnable
2) will work on any node in the cluster, and
3) will execute against every node in the cluster
#>
# Enter the Name of DTC resource
$DtcName = "DTC1";
<# ------- End of User Input ------- #>
[bool]$restart = 0;
$node = (Get-ClusterResource -Name $DtcName).OwnerNode.Name;
$DtcSettings = Get-DtcNetworkSetting -DtcName $DtcName;
if ($DtcSettings.InboundTransactionsEnabled -eq $false) {
Set-DtcNetworkSetting -CimSession $node -DtcName $DtcName -AuthenticationLevel "Mutual" -InboundTransactionsEnabled $true -Confirm:$false;
$restart = 1;
}
if ($DtcSettings.OutboundTransactionsEnabled -eq $false) {
Set-DtcNetworkSetting -CimSession $node -DtcName $DtcName -AuthenticationLevel "Mutual" -OutboundTransactionsEnabled $true -Confirm:$false;
$restart = 1;
}
if ($restart -eq 1) {
Stop-Dtc -CimSession $node -DtcName $DTCname -Confirm:$false;
Start-Dtc -CimSession $node -DtcName $DTCname;
}
10. Desabilitar e interromper o serviço DTC local em cada nó
Desabilite o DTC local em ambos os nós para garantir que as transações distribuídas usem o recurso DTC clusterizado. O script a seguir desabilita e interrompe o serviço DTC local em cada nó. Execute o Script do PowerShell a seguir no SQLNODE1.
# Disable local DTC service
<#
Script:
1) is re-runnable
2) will work on any node in the cluster, and
3) will execute against every node in the cluster
#>
$DTCname = 'Local';
$nodes = (Get-ClusterNode).Name;
foreach ($node in $nodes) {
$service = Get-WmiObject -class Win32_Service -computername $node -Filter "Name='MSDTC'";
if ($service.StartMode -ne 'Disabled') {
$service.ChangeStartMode('Disabled');
}
if ($service.State -ne 'Stopped') {
$service.StopService();
}
}
11. Realizar um ciclo do serviço SQL Server em cada instância
Com o serviço DTC clusterizado totalmente configurado, será necessário interromper e reiniciar cada instância do SQL Server no AG para garantir que o SQL Server seja inscrito para usar esse serviço DTC.
Na primeira vez que o serviço do SQL Server exigir uma transação distribuída. Ele é registrado com um serviço DTC. O serviço do SQL Server continua a usar o serviço DTC até que ele reinicie. Se um serviço DTC clusterizado estiver disponível, o SQL Server é inscrito no serviço DTC clusterizado. Se um serviço DTC clusterizado não estiver disponível, o SQL Server é inscrito no serviço DTC local. Para verificar se o SQL Server é registrado com o serviço DTC clusterizado, pare e reinicie cada instância do SQL Server.
Siga as etapas contidas no script T-SQL seguinte:
/*
Gracefully cycle the SQL Server service and failover the availability group
a. On SQLNODE2, cycle the SQL Server service from SQL Server Configuration Manager
b. On SQLNODE2 failover, the availability group to SQLNODE2
Execute the T-SQL script below on SQLNODE2 (Use Results to Text)
c. On SQLNODE1, cycle the SQL Server service from SQL Server Configuration Manager
d. On SQLNODE1, failover the availability group to SQLNODE1 once the databases are back in sync.
Execute the T-SQL script below on SQLNODE1 (Use Results to Text)
*/
SET NOCOUNT ON;
-- Ensure replica is secondary
IF (
SELECT rs.is_primary_replica
FROM sys.availability_groups ag
INNER JOIN sys.dm_hadr_database_replica_states rs
ON ag.group_id = rs.group_id
WHERE ag.name = N'DTCag1' AND rs.is_local = 1
) = 0
BEGIN
-- Wait for SYNCHRONIZED state
DECLARE @ctr TINYINT = 0;
DECLARE @msg VARCHAR(128);
WHILE (
SELECT synchronization_state
FROM sys.availability_groups ag
INNER JOIN sys.dm_hadr_database_replica_states rs
ON ag.group_id = rs.group_id
WHERE ag.name = N'DTCag1'
AND rs.is_primary_replica = 0
AND rs.is_local = 1
) <> 2
BEGIN
WAITFOR DELAY '00:00:01';
SET @ctr += 1;
SET @msg = 'Waiting for databases to become synchronized. Duration in seconds: ' + cast(@ctr AS VARCHAR(3));
RAISERROR (@msg, 0, 1) WITH NOWAIT;
END;
ALTER AVAILABILITY GROUP DTCAG1 FAILOVER;
SELECT 'Failover complete' AS [Sucess];
END;
ELSE
BEGIN
SELECT 'This instance is the primary replica. Connect to the secondary replica and try again.' AS [Error];
END;
12. Testar configuração
Esse teste usa um servidor vinculado do SQLNODE1 para o SQLNODE2 para criar uma transação distribuída. Verifique se a réplica primária do AG está em SQLNODE1. Para testar a configuração, você vai:
- Criar servidores vinculados
- Executar uma transação distribuída
Criar servidores vinculados
O script a seguir cria dois servidores vinculados no SQLNODE1. Execute o script T-SQL a seguir no SSMS para SQLNODE1.
-- SQLNODE1
IF NOT EXISTS (SELECT * FROM sys.servers WHERE name = N'SQLNODE1')
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = N'SQLNODE1';
END
IF NOT EXISTS (SELECT * FROM sys.servers WHERE name = N'SQLNODE2')
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = N'SQLNODE2';
END
Executar uma transação distribuída
Esse script primeiro retorna as estatísticas atuais de transação do DTC. Em seguida, o script executa uma transação distribuída usando bancos de dados no SQLNODE1 e SQLNODE2. Em seguida, o script retorna estatísticas de transação do controle DTC, que agora devem ter a contagem maior. Conecte-se fisicamente ao SQLNODE1 e execute o script T-SQL a seguir no SSSMS para SQLNODE1 no modo SQLCMD.
/*******************************************************************
Execute the script in its entirety on SQLNODE1 in SQLCMD mode
Must be physically connected to SQLNODE1
*******************************************************************/
USE AG1;
GO
SET NOCOUNT ON;
-- Get Baseline
!! Powershell; $DtcNameC = Get-DtcClusterDefault; Get-DtcTransactionsStatistics -DtcName $DtcNameC;
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO SQLNODE1.[AG1].[dbo].[Names] VALUES ('TestValue1', GETDATE());
INSERT INTO SQLNODE2.[dtcDemoAG1].[dbo].[Names] VALUES ('TestValue2', GETDATE());
COMMIT TRAN
GO
-- Review DTC Transaction Statistics
!! Powershell; $DtcNameC = Get-DtcClusterDefault; Get-DtcTransactionsStatistics -DtcName $DtcNameC;
Importante
A instrução USE AG1 deve ser executada para garantir que o contexto do banco de dados é definido como AG1. Caso contrário, você receberá a seguinte mensagem de erro: "O contexto da transação está sendo usado por outra sessão."
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de