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 instance. Although 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.
