Rajendra Gupta

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

September 4, 2020 by

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

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

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
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Group
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views