Availability Groups Supporting Distributed Transactions

By default, SQL Server Availability Groups (AG) do not fully support Distributed Transactions – even if the AG’s underlying Windows Servers are configured for Distributed Transaction support.

 
Typically, if a system is required to support Distributed Transactions, the only requirement is to enable and configure the Windows Servers’ local MS-DTC to support XA Transactions. “XA” is a two-phase commit protocol used to ensure data integrity for a single transaction shared between multiple relational databases. However, with SQL Server AG, there are additional requirements to enable Distributed Transaction support and high availability.

Prior to SQL Server 2016 SP2, it was not recommended or supported to implement AGs on systems which supported Distributed Transactions within databases on the same Instance. Since SQL Server 2016 SP2, distributed transactions are fully supported in all databases – including those involved in AG and within databases on the same Instance.

If an AG is not configured to support Distributed Transactions (this includes unsupported SQL Server versions) – potential side effects can go unnoticed…. until the AG is moved to a different Replica. Here is a real-life example that happened during a “planned” SQL Server 2017 AG failover in which Local DTCs were used and the AG was not configured for Distributed Transactions. The database names and server names have been genericized:

All AG databases, including the Shipping database, were reporting “Synchronized” just prior to a planned failover. However, a DTC-enlisted transaction was made on ReplicaA_Instance immediately prior to the failover. When the AG failed over to ReplicaB_Instance, all standard transactions were either rolled forward or back successfully in each AG database. Then the recovery of in-doubt Distributed Transactions were invoked.  At that point, ReplicaB_Instance went to check in its local MS DTC for the Distributed Transaction result….but since MS DTC on ReplicaB_Instance doesn’t have any knowledge of the Distributed Transactions which were initiated from ReplicaA_Instance – ReplicaB_Instance generated this error:
 
Message: SQL Server detected a DTC/KTM in-doubt transaction with UOW  {F37CAD38-899F-4469-9FAF-45BF1C67FD9D}.Please resolve it following the guideline for Troubleshooting DTC Transactions.
 
Then automatically (and immediately) the Shipping AG database was put in Suspect mode.

It is surprising we do not see this issue more often! Luckily there are a handful of easy actions which will help prevent this issue from happening going forward:

Ask the Application Owner if Distributed Transactions are required to be supported when configuring an AG.

• There is already a myriad of questions a DBA must ask when architecting a reliant SQL Server environment for an application. Make sure to add this one to your list of questions!
• If transactions can span multiple databases, even on the same Instance, they are Distributed Transactions!

 
Configure a Clustered MS-DTC as a Resource in the WSFC.

• To make this highly available, configure this Resource onto Clustered Shared Storage

 
Disable the Local MS-DTC on all Replica Windows Servers.

Enable the AG for Distributed Transactions with the “DTC_SUPPORT = PER_DB” option.

• In SQL Server 2016 SP2, this option must be set during the CREATE AVAILABILITY GROUP sequence. This option is not enable-able after the AG is created.
• In SQL Server 2017 or beyond, this option can be enabled during and after the AG is created

 
Configure the in-doubt xact resolution option if you are using a Local DTC.

• To presume commit any in-doubt MS DTC transactions, set to 1
• To presume abort any in-doubt MS DTC transactions, set to 2
• By default, this is set to 0, which requires the DBA to manually resolve in-doubt transactions as the affected database will go into Suspect Mode.
• It is recommended to have this configuration option consistently set across all Instances which support Distributed Transactions

Here are the effects of configuring a Clustered MS DTC and configuring the DTC_SUPPORT = PER_DB AG option:

In order to participate in distributed transactions, an instance of SQL Server enlists with a DTC. Normally the instance of SQL Server enlists with DTC on the local server, but in the case of an AG, the SQL Server Instance should enlist with a Clustered MS DTC. Each instance of SQL Server creates a resource manager with a unique resource manager identifier (RMID) and registers it with the Clustered DTC. In the default configuration, all databases on an instance of SQL Server use the same RMID. Also in the default configuration, during AG failover – the new Primary Replica will have a different RMID (because it is running on a new Instance), therefore the new Primary Replica’s Instance does not have any knowledge of prior in-doubt distributed transactions initiated on the old Primary Replica’s Instance.

To support distributed transactions during AG failover movement, each database should act as a separate resource manager and must have a unique RMID. When an availability group has DTC_SUPPORT = PER_DB, SQL Server creates a resource manager for each database and registers with the Clustered DTC using a unique RMID. In this configuration, the database is a resource manager for DTC transactions. It is very important to know that the RMID follows the database during an AG failover! This is the secret sauce which allows the resolution of in-doubt transactions!

Other helpful tips to ensure trouble-free AG failovers:

• Replicas should be in Synchronous Mode
• Validate the AG is Healthy and Synchronized between the current Primary and Secondary Replica(s) immediately prior to moving the AG to a different Replica
• Validate the AG is Healthy and Synchronized after the failover (Databases incurring a large number of transactions may take several minutes to become synchronized.)
• If any issues are detected, consult the SQL Errorlog immediately

In short review, if your SQL Server 2016 SP2 (or higher) AG environment is required to support Distributed Transactions, ensure the following configurations are enabled:

Enable Clustered MS DTC

• Put this Resource on Clustered Shared Storage

 
Disable Local MS DTC

• If this is not done, at least set the in-doubt xact resolution option

 
Enable the “DTC_SUPPORT = PER_DB” option in the AG

• In SQL 2016 SP2, you must set this option while the AG is being created
• In SQL 2017 and beyond, this AG option can be set at any time

 
If you’re looking for support with SQL Server availability groups, or other initiatives around SQL Server please contact us.