In this 19^th 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: SQLAG1\INST1 & SQLAG2\INST2
• 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.

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 8^th 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

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.

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.

We can use automatic seeding for its initial data synchronization.

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

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

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:

Verify the AG dashboard for the new availability group.

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

  1 2 3 4 5 6

  Use Distribute1 Go Create table T1 (id int) Go Use Distribute2 Go

• 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

  1 2 3

  Begin Distributed transaction Insert into Distribute1.dbo.t1 values(1) Insert into Distribute2.dbo.t2 values(2)

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.

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

In the Component Services, navigate to Computers-> My Computer -> Distributed Transaction Coordinator -> 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.

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.

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

Now, as the transaction is still open, let’s perform a failover from the current primary replica SQLAG2\INST2 to new primary replica SQLAG1\INST1.

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

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.

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 SQLAG2\INST2

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

   

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 SQLAG1\INST1

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

   

   

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

   

• 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

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023
• Learn the PostgreSQL COALESCE command - January 19, 2023