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.

#always on availability groups #installation # setup and configuration