Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups

In the 18th part of the SQL Server Always On Availability Group series, we will discuss the cross-database MSDTC support in an availability
group.

In this article, we will learn the following topics:

  • The requirement of MSDTC in SQL Server for distributed transactions
  • MSDTC for the Windows failover cluster
  • MSDTC for SQL Server Always On Availability Group
  • Two-phase commit in a distributed transaction

A requirement of MSDTC in SQL Server for distributed transactions

Microsoft Distributed Transaction Coordinator (popularly known as MSDTC) is a coordinator to handle the distributed transactions. A distribution transaction involves two or more databases in a single transaction.

Suppose your application started a transaction that inserts records into two different databases db1 and db2. These
databases exist on the different SQL Servers SQLNode1 and SQLNode2. Your transaction should succeed if it inserts
records in both databases involved in that transaction else you may get data inconsistencies. In this case, MSDTC
monitors the transaction. It monitors the distributed transaction, and if any of the server transaction fails, it
takes decisions to roll back the whole transaction.

Suppose once the distributed transaction starts, it inserted record in the DB1, but before it inserts into the DB2,
due to a power failure, DB2 shuts down. This transaction is known as an in-doubt transaction. MSDTC’s role is to
ensure that the in-doubt transactions are either rolled back to committed.

MSDTC ensures e any in-doubt transactions are either aborted (rolled back) or committed (rolled forward). 

MSDTC in SQL Server for distributed transactions in SQL Server Always On Availability Group

Do you use distributed transactions in your environment?

You might think that I do not use the distributed transactions in your SQL queries because you do not specify
BEGIN DISTRIBUTED TRANSACTION for your SQL Statements. SQL Server DTC for the distributed
transactions for linked server, OPENROWSET, OPENQUERY, OPENDATASOURCE and RPC activities. It is a common
misconception that my workload does not use distributed transactions. Therefore, if you use linked servers (common
usage), then you should consider the distributed transaction requirements.

MSDTC for the Windows failover cluster

You might see MSDTC clustered service in a Windows failover cluster along with SQL Service. Starting from Windows
2008, it is not mandatory to configure MSDTC to build a cluster. However, if you install MSDTC in a failover
cluster, you need to specify the DTC resource IP address and shared storage.

https://techcommunity.microsoft.com/t5/sql-server-support/msdtc-recommendations-on-sql-failover-cluster/ba-p/318037

In a failover cluster, we can have either the local MSDTC or clustered DTC. Suppose you have a clustered DTC for
distributed transactions. In the below image, we have SQL and MSDTC roles on the Node1. Usually, you should have the
MSDTC role on the same node where your SQL role exists.

Now, suppose you shut down active node SQLNode1, it causes your resources SQL and MSDTC to failover from SQLNode1 to
SQLNode2. MSDTC maintains its log in the shared drive, and it also failed over to the new primary node. In this
case, MSDTC can use the log and handle the in-doubt transactions.

MSDTC for the Windows failover cluster

In case you do not configure MSDTC in the failover cluster, SQL Server uses the MSDTC running locally on the node.
It is slightly complicated because the MSDTC log is not available in the case, one of the nodes is not available.

Local MSDTC in SQL Server Always On Availability Group

Suppose the node1 is failed (shut down) and it caused SQL resource failed over to the node2. In this case, node1 has
an in-doubt failed transaction. To resolve the transaction, node2 MSDTC tries to contact the node1 for checking the
transaction status. If it can contact the node1 MSDTC, it takes actions as per the logs. Else, it uses the
configured value in the in-doubt exact resolution SQL Server configuration. We can configure the values using the sp_configure system stored procedure.

It has the following three configurations:

  • 0: It is the default configuration. In this case, recovery fails, and MSDTC could not resolve any
    in-doubt transaction. This option is known as No presumption. If we use the default configuration, SQL
    Server could not resolve the in-doubt transaction and the database goes into the suspect mode. You need to
    manually kill the transaction from the distributed transaction manager and bring the database in the online
    state
  • 1: It presumes the in-doubt transactions are committed
  • 2: It presumes the in-doubt transactions are aborted
  • Note: You can refer to this documentation, for more information on the Server Configurations options

MSDTC for SQL Server Always On Availability Groups

In the SQL Server always on configuration, we use the failover cluster base for an availability group.

  • SQL Server 2014: You cannot use the MSDTC for a distributed transaction in an availability group
  • SQL Server 2016: It supports cross-database transactions if these databases are in different
    instancesmachines. If a distributed transaction uses multiple databases in a SQL instance, we cannot use the MSDTC. It is applicable to availability group databases as well
  • SQL Server 2016 SP2: It supports cross-database transactions even if both databases exist in an instance. It also works in cross instance databases distributed transactions as well

Does it mean that SQL Server does not allow distributed transactions in SQL Server 2016 or below? No, SQL does not stop the distributed transactions for AG databases even it is not configured for the distributed
transactions. In these cases, once the AG failover occurs, SQL Server might not recover the database in the new
AG replica. Therefore, you should configure the MSDTC for a distributed transaction in an SQL Server Always On
Availability Groups.

  • Note: DTC does not change the behavior of the synchronous and asynchronous data commit

Two-phase commit in a distributed transaction

SQL Server uses two-phase commit protocol for the distribution transaction once the user issues a commit statement.
Before explaining the two phase-commit, let’s understand the terminology used here.

  • Transaction Manager: MSDTC acts as a transaction manager for distributed transactions
  • Resource Manager: Each SQL instance works as a resource manager. In a two-node cluster, we have two resource managers of both SQL instances

The two-phase commit process uses the following phases, as shown below:

Two-phase commit in a distributed transaction for SQL Server Always On Availability Group

  • Prepare phase: In this phase, the transaction manager receives the commit request, so it sends
    the prepare command to all resource managers. As highlighted earlier, the resource manager is the SQL instance.
    The resource manager writes the transactions in the disk and performs the task to make it durable. It responds
    to the success or failure message to the transaction manager
  • Commit phase: If both the resource manager sends the success prepared message to the
    transaction manager, it issues a commit command to both resource managers. Once the resource manager commits the
    records on SQL instance, it again sends an acknowledgement. With both commit acknowledgements, SQL Server
    responds to the application about successful acknowledgement. In case any resource manager reports a failure
    message, the transaction manager sends the command to rollback the transaction on each resource manager. It also
    sends the message to the application for the rollback transaction
  • Note: If a user issues a rollback transaction command, the transaction manager immediately aborts
    the transaction and gives instructions to roll back the work done so far

Conclusion

In this article, we understood the concept of Microsoft Distributed Transaction Coordinator (MSDTC)
in SQL Server and its integration with the SQL Server Always On Availability Group. In the next article, we
will configure an SQL Server Always On Availability Group to use the MSDTC.

Table of contents

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Author: admin

Leave a Reply

Your email address will not be published.