MSDTC and configuration details for cluster/Always On

MSDTC:

MSDTC stands for Microsoft Distributed Transaction Coordinator, and it is used by SQL Server and other applications when a distributed transaction between multiple machines is required.  A distributed transaction is a simple transaction that spans between two or more machines.  The basic concept is that machine 1 starts a transaction and does some work.  It then connects to machine 2 and does some work.  Then the work on machine 2 fails and is canceled and work on machine 1 needs to then be rolled back.

Use of MSDTC:

The only time that DTC needs to be used is when more than one physical computer is going to be involved in an explicit distributed transaction.  If you are going from one instance to another on the same server DTC will not be needed.  If you are going from one instance to another within a cluster, you will want to have DTC available as you may have to go between nodes of the cluster as you have no guarantee that the instances will be on the same physical node.

A requirement of MSDTC in SQL Server for distributed transactions:

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

Scenario example:

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 transactions fails, it takes decisions to roll back the whole transaction.

Suppose once the distributed transaction starts, it inserts a 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 or committed.

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

 

What does it do?

MSDTC, Microsoft Distributed Transaction Coordinator, is, as the name suggests, a coordinator/manager to handle transactions that are distributed over multiple machines. Let’s say we start a transaction, where one of the steps includes querying data from a different SQL Server instance on a different physical machine; MSDTC comes into action with these specific tasks that need transaction coordination across different physical machines. It executes the section of code that is supposed to run on remote machines and brings back the results to the local SQL instance. In this process, if any issue were to occur, on the remote machine that results in rollback, MSDTC makes sure the original transaction on this machine also rolls back safely.

How does it do?

MSDTC comes with necessary Operating System controls and memory structures to carry out these operations independent of the SQL Instances while keeping the integrity of the transaction across the multiple physical SQL machines a.k.a. the complete two-phase distributed commit protocol and the recovery of distributed transactions.

Where does SQL Server use it?

The key point here is that these need to be SQL Instances on different physical machines. Queries that request data across different instances on the same physical box do not go through MSDTC.

MSDTC is used by query activities like

• Linked Servers
• OPENROWSET
• OPENQUERY
• OPENDATASOURCE
• RPC (Remote Procedure Calls)
• Ones with
  • BEGIN DISTRIBUTED TRANSACTION

Configure distributed transactions for an Always On availability group:

SQL Server 2017 (14.x) supports all distributed transactions including databases in an availability group. This article explains how to configure an availability group for distributed transactions. In order to guarantee distributed transactions, the availability group must be configured to register databases as distributed transaction resource managers.

Note: 

SQL Server 2016 (13.x) Service Pack 2 and later provides full support for distributed transactions in availability groups. In SQL Server 2016 (13.x) versions prior to Service Pack 2, cross-database distributed transactions (i.e. transactions using databases on the same SQL Server instance) involving a database in an availability group are not supported. SQL Server 2017 (14.x) does not have this limitation.

In SQL Server 2016 (13.x) the configuration steps are the same as in SQL Server 2017 (14.x).

In a distributed transaction, client applications work with Microsoft Distributed Transaction Coordinator (MS DTC or DTC) to guarantee transactional consistency across multiple data sources. DTC is a service available on supported Windows Server-based operating systems. For a distributed transaction, DTC is the transaction coordinator. Normally, a SQL Server instance is the resource manager. When a database is in an availability group, each database needs to be its own resource manager.

SQL Server does not prevent distributed transactions for databases in an availability group – even when the availability group is not configured for distributed transactions. However, when an availability group is not configured for distributed transactions, failover may not succeed in some situations. Specifically, the new primary replica SQL Server instance may not be able to get the transaction outcome from DTC. To enable the SQL Server instance to get the outcome of in-doubt transactions from the DTC after failover, configure the availability group for distributed transactions.

More Info:

Unless a database is also a member of a Failover Cluster, DTC is not involved in availability group processing. The consistency between replicas within an availability group is maintained by the availability group logic: The primary will not complete the commit and acknowledge the commit to the caller until the secondary confirms that the log records have persisted in durable storage. Only then will the Primary declare the transaction complete. In async mode, we do not wait for the secondary to ack, and there is explicitly the chance of the loss of a small amount of data.

Prerequisites:

Before you configure an availability group to support distributed transactions, you must meet the following prerequisites:

• All instances of SQL Server that participate in the distributed transaction must be SQL Server 2016 (13.x) or later.
• Availability groups must be running on Windows Server 2012 R2 or later. For Windows Server 2012 R2, you must install the update in KB3090973 available at https://support.microsoft.com/kb/3090973.

Create an availability group for distributed transactions:

Configure an availability group to support distributed transactions. Set the availability group to allow each database to register as a resource manager. This article explains how to configure an availability group so that each database can be a resource manager in DTC.

You can create an availability group for distributed transactions on SQL Server 2016 (13.x) or later. To create an availability group for distributed transactions, include DTC_SUPPORT = PER_DB in the availability group definition. The following script creates an availability group for distributed transactions.

Creating an MSDTC resource within a Windows Failover Cluster

Windows Cluster – MSDTC Resource

1. First up we’ll be creating an MSDTC resource. Open up Windows Failover Cluster Manager. Right-click on services and applications and select Configure a Service or Application.

 

2. The High Availability Wizard begins. Click Next.

3. Within the list of services and applications select Distributed Transaction Coordinator (DTC) and click Next.

4. Give the MSDTC resource a name and IP address. In this example, I’ve named mine WinclusterDtc and given it an IP address of 192.168.10.60. Click Next.

5. I’ve already provisioned a shared iSCSI drive with 1GB of free space. The drive letter for this shared drive is M: and will hold the MSDTC resource files. Click Next.

6. Click next to start the configuration of the MSDTC resource.

7. A MSDTC resource has been created. Now Click Finish.

8. You can close the Windows Failover Cluster Manager. Click on Start – Administrative Tools and select Component Services.

 

9. Within the Component Services window expand Component Services – Computers – My Computer – Distributed Transaction Coordinator – Clustered DTCs and select the name you gave to your MSDTC cluster resource. Right-click and select properties.

 

10. Select the Security Tab.

 

11. Click Network DTC Access, select Allow Inbound, Allow Outbound, and Incoming Caller Authentication Required. Click Apply and Ok.

 

12. The MSDTC service has been reactivated. Click Yes.

13. The MSDTC service has been reactivated. Click Ok and you can now exit Component Services.