Friday 19 June 2015

XA Transaction, MSDTC and How to Configure Multiple MSDTC Instance on SQL AlwaysON Cluster Environment

Recently we had many issues due to one of our third party application using XA Transaction, We had 4 instances of SQL Server 2012 for this application set up on an active-active 4 node Cluster with Clustered DTC as a separate Role. Since the SQL instances were running XA Transactions they were sharing the DTC on the cluster. We had a hardware issue and had to restart the node holding the MSDTC unfortunately any restart to DTC will need a SQL Server restart otherwise XA Transactions will fail with the below error.

2013-11-16 17:22:38.630 spid342 Error: 8509, Severity: 16, State: 1.
2013-11-16 17:22:38.630 spid342 Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d01c(XACT_E_CONNECTION_DOWN).
2013-11-16 17:22:46.600 spid112 Error: 8510, Severity: 20, State: 3.
2013-11-16 17:22:46.600 spid112 Enlist operation failed: 0x8004d01c(XACT_E_CONNECTION_DOWN). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.

Since the DTC is shared with 4 different database servers we ended up in a situation of restarting all the SQL Instances unnecessarily when we had a DTC outage. So we decided to have a dedicated instance of MSDTC for each Server instance.

I worked closely with Microsoft PSS Team to get the MSDTC configured.I am just sharing the information I got from Microsoft PSS Distributed Services Team on XA Transaction, and how to configure Multiple MSDTC Instance on SQL AlwaysON Cluster Environment,


Questions:-

Right set of steps to configure multiple instance of DTC.

The link below will provide you Right Set of Steps to Configure Multiple Instance of DTC:
<Comment:- Although the article is for SQL 2008, There are no changes in steps for SQL 2014/SQL 2012 both of which I was able to test in my environment>

However, the important point to note is to know pros and cons of having Independent Instance of Clustered DTCs vs Making SQL Group dependent on Clustered DTC resource. The link below provides you Pros and Cons:

Do I need to Run Tmapping in above case?

For your “4 DTC instances one per SQL instance, SQL dependent on DTC.  No TMMapping is necessary” you already have SQL dependent on MSDTC. So the mapped instance of SQL will be using the Clustered DTC instance. SQL and DTC will both failover together. So where does explicitly mapping the DTC arise?  
<Comment:- I have configured the DTC instances in the same Cluster Role as SQL Server which negates the use of TMapping but if you are configuring the MSDTC as a seperate Role on the Cluster TMapping is Mandatory to map the right SQL instanceAlthough it is not required to use TMapping when DTC Resource is Dependent on SQL doing the tmapping does not do any harm which I eventually ended up doing. 

use Msdtc –tmmappingview * or verify the mapping on Registry HKEY_LOCAL_MACHINE\Cluster\MSDTC\ to confirm the mapping has been completed successfully. Also I would suggest using DTCTester.exe to verify the transactions are getting enlisted on the right DTC Instance >

 The architecture of the JDBC driver which creates the XA Transaction

The driver is a pure Java implementation that can run on any OS. For this reason it cannot rely on having a DTC instance available on the client machine. To work around this issue, it uses a server-side component (sqljdbc_xa.dll) that is loaded in the SQL Server process space and manages DTC transactions on behalf of the client.

When an XA transaction is started in the client application, a call is made via an extended stored procedure in the dll to enlist the connection in a DTC transaction. When the XA transaction is aborted or committed by the client application, a corresponding call is made by the dll to rollback/commit the DTC transaction. So to the resource manager (SQL DB), it is as if extended stored proc performing transaction from the same server machine.

In simple SQL Server terms, distributed transactions are transactions that are capable of coordinating transaction state with 2 or more unique connections to either the same server or to different servers. SQL Server is capable of utilizing distributed transactions by communicating with MSDTC. MSDTC coordinates transaction state with SQL Server and other participants of the distributed transaction by communicating with other MSDTC instances on other machines.

In most scenarios that involve our Windows-based client technologies such as ADO, System.Data, ODBC, OLE DB etc., the client application that utilizes distributed transactions communicates with the local MSDTC on its machine. This local MSDTC communicates with the SQL Server MSDTC instance and potentially other MSDTC instances of other SQL Server instances.

Client                                                                                               Server

MSDTC  --------------------------- transaction state ------------------------------ MSDTC
 |                                                                                                          |
ADO      ------------------------ SQL Server connection -------------------------- SQL Server

What is XA

XA is an industry standard for implementing distributed transactions between generic transaction managers and resource managers. It is popular in the Java and Unix development community and JDBC drivers are expected to implement XA so that the JDBC driver can participate in distributed transactions. Most application servers such as Weblogic, Websphere, etc. use XA transactions even for single-connection scenarios so that the application server can manage transactions at a level higher than the application code.

XA Specification

XA and MSDTC
MSDTC implements the XA protocol to allow Windows machines to participate in distributed transactions in a mixed OS environment. C++ applications can use functions such as xa_start using the XA protocol to communicate with MSDTC and by extension SQL Server.
  
XA Implementation in the Microsoft JDBC Driver
The Microsoft SQL Server JDBC Driver 1.2 and 2.0 implements XA transactions so that applications using it can participate in distributed transactions. Because the JDBC driver can run on any OS (not just Windows), the driver cannot assume that a local MSDTC instance exists. The only location where we know an MSDTC instance exists is the SQL Server.

For this reason, the JDBC driver implements a mechanism where the JDBC driver (client-side) communicates with the server-side MSDTC through a server-side component that lives on the SQL Server. This component - sqljdbc_xa.dll - is a C++ dll that is loaded by the SQL Server that implements helper extended stored procedures. This component uses the XA APIs such as xa_start to start/commit/rollback distributed transactions in the server-side MSDTC instance. The client-side JDBC driver component invokes the server-side component by launching a second connection to the SQL Server and executing extended stored procedures which perform the XA calls.

Client                                                                                                                    Server

JDBC  driver  ---- SQL Server connection 1: statements ---------------------------------- SQL Server ----------
 |                                                                                                               |                          |
   ------------------ SQL Server connection 2: extended stored procedure XA calls ------ sqljdbc_xa.dll         |
                                                                                                                 |                          |
                                                                                                                             MSDTC --------------

The sequence of operations that occur in a normal XA transaction sequence is listed below. This is a single-connection scenario in the sense that the JDBC application opens a single connection object to SQL Server. JDBC uses MSDTC as a tweak to give SQL sense of Distributed Transactions. As you would have realized by now, that resource manager (SQL DB), uses extended stored proc performing transaction from the same server machine.