Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups

In this 19th article for SQL Server Always On Availability Groups, we configure MSDTC for distributed
transactions in an availability group.

Introduction

In the article, Explore Cross-database MSDTC for distributed transactions in SQL Server Always On
Availability Groups
, we understood the following concepts:

  • What is Microsoft distributed transaction coordinator and purpose of it in distributed transactions
  • How two-phase commit works for distributed transactions
  • What is a Cluster DTC and local resource DTC
  • MSDTC for failover cluster and SQL Server Always On Availability Groups

In this article, we understand the MSDTC support for the distributed transactions in a SQL Server Always On
Availability Group.

Prerequisites

You should follow SQL Server Always On series articles and prepare the two-node availability group cluster.

  • Nodes: SQLAG1INST1 & SQLAG2INST2
  • Data Sync: Synchronized commit
  • Existing availability groups
  • Failover mode: Automatic
  • Existing SQL Listeners
  • SQL version: SQL Server 2019 (Minimum version SQL Server 2016 (13.x))
  • Windows version: Windows version 2016 (Windows Server 2012 R2 or later)
  • SSMS 18.x

Configure an SQL Server Always On Availability Group for distributed transactions

In my demo setup, I connect to the primary replica and verify the existing availability groups. It currently has
three availability group that we created in the earlier article.

Configure an SQL Server Always On Availability Group for the distributed transactions

Let’s create a new availability group. Launch the New availability group wizard and specify an
appropriate availability group name.

In the “Specify Availability Group Options” screen, we see two options.

  • Database Level Health Detection: We already explored it in the 8th article of the
    SQL Server Always On series
  • Per Database DTC support: As we looked earlier, in a Windows failover cluster, SQL instance
    acts as a resource manager. Here, we get an option to enable per database DTC support. Therefore, each database
    configured in an availability group works as a resource manager. If you have 2 availability group databases, it
    has two DTC separate for each database

Per Database DTC support

For the distributed transactions DTC, I created two databases in the primary replica and took their full backups.
You can see both databases meets prerequisites for an availability group configuration.

distributed transactions

Configure availability replicas, availability mode and automatic failover configurations on the next page. We use
Synchronous commit in this AG that supports the automatic failover. We can go ahead with other default options in
this “Specify Replicas” page.

specify replicas

We can use automatic seeding for its initial data synchronization.

automatic seeding

In the summary page, review your configuration and script out the difference.

Availability group summary

In the generated script for the availability group configuration, it adds a new argument
DTC_SUPPORT=PER_DB to highlight distributed transactions DTC support.

availability group configuration

Before SQL Server 2017, SQL Server did not use DTC for cross distributed transactions in different databases of an
instance unless explicitly configured. In an availability group configured with the DTC_SUPPORT=PER_DB argument, SQL
Server 2017 promotes all distributed transactions to DTC in a single SQL instance.

Click on Finish to configure DTC in an availability group as shown below:

DTC in an availability group

Verify the AG dashboard for the new availability group.

SQL Server Always On Availability Groups dashboard

Demonstrate cross-database distributed transactions in an availability group

We have configured a new availability group to support the cross-database distributed transactions for AG databases.
Let’s have a demonstration on it and see how it works.

  • Create tables on the Distribute1 and Distribute2 AG databases

  • Start a distributed transaction on the current primary replica with the below script. We did not specify a
    Commit or rollback transaction statement to finish the transaction

In the output, you get 1 row affected in each database. The transaction is still open because of the absence of a
commit or rollback statement.

cross-database distributed transactions

On the primary replica server, go to Start and launch Component Services.

Component Services

In the Component Services, navigate to Computers-> My Computer -> Distributed Transaction Coordinator ->
Local DTC.

Local DTC

In the transaction list, you see an active DTC transaction. You also get a Unit of Work ID that represents a DTC
transaction, and it uniquely identifies the transaction. It is also known as the UOW (Unit of Work) ID.

You can also notice that we use a Local DTC instead of a clustered DTC for SQL Server Always On Availability Group.

clustered DTC for SQL Server Always On Availability Group.

If you right-click on the UOW, you get an option to Commit, Abort or Forget this DTC transaction. We do not want
manual intervention here so let’s ignore it as of now.

Commit, Abort or Forget this DTC transaction

Click ok the Transaction Statistics, and it shows statistical information about the existing DTC transaction.

Transaction Statistics

Now, as the transaction is still open, let’s perform a failover from the current primary replica SQLAG2INST2 to new
primary replica SQLAG1INST1.

perform a failover

After the AG failover, validate that the dashboard looks good.

Failover status

Before failover, we started cross-database distributed transactions for databases involved in an availability group.
In the new primary replica, view the transaction statistics, and it shows that DTC transaction is aborted.

view the transaction statistics

Let’s try to investigate what happened behind the scenes. Connect to the old primary instance and view the SQL
Server error logs during failover.

SQL Server error logs on SQLAG2INST2

  1. Failure message for the DTC transaction in database ‘Distribute2’

    Remote harden of transaction ‘DTC Transaction’ (ID 0x0000000000032cde 0000:00000370) started at Aug 13 2020 1:27PM in database ‘Distribute2’ at LSN (37:486:3) failed

  2. Failure message for the DTC transaction in database ‘Distribute1’

    Remote harden of transaction ‘DTC Transaction’ (ID 0x0000000000032cde 0000:00000374) started at Aug 13 2020 1:27PM in database ‘Distribute1’ at LSN (37:513:3) failed

    SQL Server error logs

  3. It releases the MSDTC resource manager for the [Distribute1] database

    Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [Distribute1] has been released. This is an informational message only. No user action is required

  4. It releases the MSDTC resource manager for the [Distribute2] database

    Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [Distribute2] has been released. This is an informational message only. No user action is required

These error log messages also prove that SQL Server using a separate local DTC resource for each database. During
failover, it releases these resource managers on the old primary replica.

SQL Server error logs on current primary replica SQLAG1INST1

In the current primary AG replica, you get the following entries in the error logs:

  1. It starts the MS DTC process for the database ‘Distribute2’ in the new replica

    Initializing Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [ab5db5dc-a6be-4550-b1ca-ed82bc0c5741] for database ‘Distribute2’. This is an informational message only. No user action is required

  2. It starts the MSDTC process for the database ‘Distribute1’ in the new replica

    Initializing Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [ab5db5dc-a6be-4550-b1ca-ed82bc0c5741] for database ‘Distribute2’. This is an informational message only. No user action is required

  3. As the transaction was not finished (commit or rollback) before AG failover, DTC marks it as an in-doubt distributed transaction. On the new replica, it tries to recover the in-doubt distributed transaction

    MSDTC error

    recover the in-doubt distributed transaction.

  4. It rollbacks the transaction in both AG databases – Distribute1 and Distribute2

    1 transactions rolled back in database ‘Distribute2’ (10:0). This is an informational message only. No user action is required

    1 transactions rolled back in database ‘Distribute1’ (11:0). This is an informational message only. No user action is required

  5. It finally states the message that recovery of the in-doubt transaction is completed. It ensures the transaction is consistent before and after the AG failover

    Modify existing availability group for

  • Note: If SQL Server could not resolve the in-doubt distributed transaction, the database goes into the suspect mode.
    In this case, you must resolve the transaction from the component services. Usually, you should set the appropriate
    options in the in-doubt xact resolution Server Configuration Option

Modify existing availability groups for distributed transactions per database DTC

We can alter an existing availability group for distributed transactions per database DTC in SQL Server 2016 SP2
onwards.

To alter an availability group, use the following query:

Before SQL 2016 SP2, you need to drop the availability group and re-create with the DTC_SUPPORT=PER_DB statement.

Conclusion

In this article, we explored the cross-database transactions for SQL Server Always On Availability Group. You should
examine the DTC requirement according to your workload and enable DTC support if required.

Table of contents

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Author: admin

Leave a Reply

Your email address will not be published.