Tuesday, March 19, 2024

Enable XA transactions on Microsoft SQL 2012

This small howto outlined the steps needed to enable Microsoft jdbc xa transactions.

1.) Download the JDBC Drivers for SQL Server (e.g. Version 6.0 from here) (*.tar.gz file)

2.) Expand that file on the SQL Server to C:\Temp

3.) Copy the file sqljdbc_xa.dll from sqljdbc_6.0\enu\xa\x64 to C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn (this folder might be different depending on your environment)

4.) Open Component Services or click Run, type dcomcnfg in a CMD to open Component Services.

5.) Expand Component Services, Computers, My Computer, and then Distributed Transaction Coordinator.

6.) Right-click Local DTC and then select Properties.

7.) Then switch to the Security tab and select the checkbox “Enable XA Transactions”. This will cause a MS DTC service restart and you will get a notification box to accept that restart.

8.) Close Component Services. Stop and then restart SQL Server to ensure that it syncs up with the MS DTC changes.

9.) run the xa_install.sql from the folder sqljdbc_6.0.8112.100_enu\sqljdbc_6.0\enu\xa with the SQL Management studio. You should get the following result:

SQLJDBC XA DLL installation script complete.
Check for any error messages generated above.

10.) To control if everything is working as expected check the master DB and in the Programmability -> Extended Stored Procedures you should now see some SQLjdbc_XA entries

More infos:
https://blogs.msdn.microsoft.com/dataaccesstechnologies/2011/10/26/how-a-dba-can-confirm-if-microsoft-jdbc-xa-transactions-are-set/
https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server
https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-xa-transactions

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay Connected

35FollowersFollow
- Advertisement -

Latest Articles