Rajendra Gupta
SQL Server Always On Availability Groups dashboard

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

September 7, 2020 by

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: 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.

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 SQLAG2\INST2 to new primary replica SQLAG1\INST1.

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 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

    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 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

    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

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups

Rajendra Gupta
378 Views