SQL Server 2016 DTC Support In Availability Groups

  • [アーティクル]

In this post I’ll discuss the new support for Distributed Transactions in SQL Server 2016, how this is made possible and show a brief example of a distributed transaction from TSQL and a simple COM Application against a database in an Availability Group and a stand alone database.

What is MSDTC?

The Microsoft Distributed Transaction Co-ordinator is a service which tracks all parts of a transaction, over one or more distributed systems. This is to ensure that the transaction is either committed or rolled back across these distributed systems and that no part is left in a non-committed state.

A distributed transaction is a transaction which is typically initiated from an application which touches upon multiple, separate instances. For example: DTC1

Prior to SQL 2016

Prior to SQL 2016 DTC was not supported in AlwaysOn availability groups as we could not guarantee the integrity of the transaction. However, DTC was used in SQL features such as Linked Servers, Remote Procedure Calls, BEGIN DISTRIBUTED TRANSACTION etc.

So what’s new in 2016 to make DTC possible?

With SQL 2016 we now support distributed transactions in availability groups. This is possible because we now register a Resource Manager per availability database which works with the DTC service to keep track of the distributed transaction. This way we can now guarantee the integrity of a distributed transaction.

So what’s required in order to make this work?

The pre-requisites to use DTC with Availability Groups are Windows Server 2012 R2 (with KB3090973 https://support.microsoft.com/en-us/kb/3090973) and above. The availability group must also have been created with the WITH DTC_SUPPORT = PER_DB clause. At the time of this writing it is not currently possible to ALTER the availability group to add DTC support.

So how does this work?

When a distributed transaction is initiated, the transaction is known by the local DTC (or clustered depending on configuration) which then sends the requests to the local DB resource manager. The Resource Manager then carries out the transactions requests. The Resource Manager is registered in the boot page of the availability database on the primary replica. If a failover happens during this transaction, during the database recovery phase the previous primary replica DTC provides the transaction details for the new primary resource manager (which is now registered in the boot page of the new primary availability database). If the transaction was committed it can be rolled forward, if the transaction was not committed/in-doubt then it is rolled back.

Let’s look into this a little deeper. Here is my setup: DTC2

Each Hyper-V machine is running Windows 2012 R2 and SQL 2016 CTP3. Server A and B contains the pubs database in the Availability Group “pubs_dtc” which was created with the WITH DTC_SUPPORT = PER_DB clause and server C contains the standalone database “Northwind”. If we look at the sys.Availability_Groups view we can see the dtc_support flag set for Availability Groups. clip_image006

Local DTC is configured with the following properties on each node: clip_image008

You can get to the configuration by going to Start > Run > “dcomcnfg”. In Component Services navigate through Console Root > Component Services > Computers > Distributed Transaction Coordinator. Right click local DTC and select Properties and select the Security tab.

Now that we have our databases ready to receive transactions, let’s look for the Resource Manager ID for the Availability Database. On the Primary Replica, find the database id for the pubs database: clip_image010

Issue a DBCC DBTABLE to see the Resource Manager ID. The output will contain information for each database on the instance, so in the output I’ll search for the dbid 9. clip_image012

We can also see in the SQL Error Log an entry for the registered Resource Manager.

This is the entry from Server A which contains the Availability Group:

2015-12-13 19:41:53.780 spid268s Initializing Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [526fff00-659f-44d6-998d-773313ea5a4c] for database 'pubs'. This is an informational message only. No user action is required.

This is the entry from Server C which is a standalone instance. Because this instance doesn’t have an availability group, the resource manager is registered at instance level when the instance is first started:

2015-12-11 16:45:47.690 spid143 Initializing Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [c0c941a4-6638-4dbc-a779-fb41c04a00c6] for server instance SQL03. This is an informational message only. No user action is required.

If we failover the availability group to Server B and issue the same query above on Server A, we will see that the Resource manager has been un-registered: clip_image014

If we now look for the Resource Manager on the new primary replica (Server B) we can see that the resource manager has now been registered for the pubs database (database id 10 in this case). Also notice that it’s the same Resource Manager ID as in Server A. clip_image016

Ok so we have everything set up, let's initiate a distributed transaction. For the first example I’ll use TSQL BEGIN DISTRIBUTED TRANSACTION which means I’ll need to create a linked server with Server C.

Here are the distributed transaction statistics before the transaction started. clip_image017 clip_image018_thumb_1

Statistics after the transaction. Here we can see the successful increment in the Committed column. clip_image020

In the next example I’ll create a distributed transaction from inside a simple application (thanks to our escalations team for creating this app!). This application simply issues a TSQL query to both the pubs database and the Northwind database from the same transaction, however the query to the pubs database Is done through the availability group listener. The application will issue a wait before the transaction completes. This way we can test the effect of a failover.

Here are the stats before the query: clip_image022 clip_image024

After initiating the failover from Server A to Server B we can see in the SQL Error logs that the Resource Manager has been unregistered from Server A and Registered on Server B.

Server A Error Log Entry:

2015-12-13 17:32:19.710 spid149 AlwaysOn: The local replica of availability group 'pubs_dtc' is preparing to transition to the resolving role in response to a request from the Windows Server Failover Clustering (WSFC) cluster. This is an informational message only. No user action is required.

…..

2015-12-13 17:32:19.730 spid35s Nonqualified transactions are being rolled back in database pubs for an AlwaysOn Availability Groups state change. Estimated rollback completion: 100%. This is an informational message only. No user action is required.

2015-12-13 17:32:19.740 spid35s Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [pubs] has been released. This is an informational message only. No user action is required.

Server B Error Log Entry:

2015-12-13 17:32:19.570 spid63 The state of the local availability replica in availability group 'pubs_dtc' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'. The state changed because of a user initiated failover. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

…..

2015-12-13 17:32:30.270 spid39s Initializing Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [526fff00-659f-44d6-998d-773313ea5a4c] for database 'pubs'. This is an informational message only. No user action is required.

2015-12-13 17:32:30.280 spid39s Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

2015-12-13 17:32:30.280 spid39s 1 transactions rolled back in database 'pubs' (10:0). This is an informational message only. No user action is required.

Server C Transaction Log Entry:

There was no entry in the error log for Server C.  However, we can see in the transaction log that the DTC transaction was aborted: clip_image026

Statistics after the transaction. Here we can see the Aborted value has increased by 1: clip_image028

Learn More:

Cross Database Transactions Support for Database Mirroring or Availability Groups - https://msdn.microsoft.com/en-us/library/ms366279.aspx

Distributed Transactions Overview - https://msdn.microsoft.com/en-us/library/ms681205%28v=vs.85%29.aspx

Resource Managers Role in Transactions - Resource Manager's Role in Transactions – https://msdn.microsoft.com/en-us/library/ms687072(v=vs.85).aspx

MSDTC - https://technet.microsoft.com/en-us/library/dd337629(v=ws.10).aspx

Comments

  • Anonymous
    October 17, 2017
    How can you confirm if an availability group was created with DTC_SUPPORT = PER_DB or not?
    • Anonymous
      October 17, 2017
      Hi Cody,You can use the following query to find that out:select name, dtc_supportfrom sys.availability_groupsCheers, Chris.
  • Anonymous
    December 14, 2017
    If a distributed transaction occurs across several databases and instances where an AG is involved and a catastrophic event occurs where the AG instance is restarted, does the transaction gets rolled back on all databases involved? i.e. Do we stay consistent across all databases?
    • Anonymous
      January 02, 2018
      Not in all cases:Here are the unsupported scenarios for 2016:Non-support for distributed transactionsSpecific cases where distributed transactions are not supported include:In SQL Server 2016 and prior, where more than one database involved in the transaction is in the same availability group.In SQL Server 2016 and prior, where at least one database is in an availability group and another database is on the same instance of SQL Server. Where the availability group was not created with enable distributed transaction.Database mirroring.Check out the article here - https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/transactions-always-on-availability-and-database-mirroring