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).
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.
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.
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.
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 instances\machines. 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
- Synchronous commit: In this mode, primary replica waits for the acknowledgement from the secondary replica. Once it gets the acknowledgement, it commits the record on the primary. You can refer Data synchronization in SQL Server Always On Availability Groups for a detailed understanding of the synchronous commit
- Asynchronous commit: Primary replica does not wait for the acknowledgement from the secondary and commits the transactions directly on the primary replica. You can refer to Data synchronization in SQL Server Always On Availability Groups for more details
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:
- 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
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
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023