I am from Microsoft SQL Developer Support team, we support many data access technologies.
Recently I had a chance to work with SQL DBA, who was facing issue to confirm to his developers whether XA transactions were configured correctly or not. After resolving his problem thought it would be easier for everyone if I blog on XA transactions.
For the benefit of novice DBA here are few concepts that you should be aware of.
What is XA Transactions ?
An XA transaction is a "global transaction" that may span multiple resources. An XA transaction involves a coordinating transaction manager, with one or more databases (or other resources, like JMS) all involved in a single global transaction.
What is Distributed Transaction Coordinator?
The Microsoft Distributed Transaction Coordinator (MS DTC) DTC can act as either an XA-compliant resource manager or as a transaction manager. When the DTC acts as an XA-compliant resource manager, it allows SQL Server, Message Queuing (also known as MSMQ), and other OLE transactions-compliant resource managers to participate in transactions that are controlled by X/Open DTP XA-compliant transaction processing monitors.
What is XA transaction identifier?
In the DTC, you can view the superior XA transaction identifier (XID) for the transaction manager in the Component Services snap-in. You can use the XID to resolve any in-doubt transactions in the DTC that have an XA transaction manager as a superior.
Here is a quick check list to make sure that basic JDBC XA configurations are set.
Steps to Configure XA Transactions
Running/Enable the MS DTC Service
To enable MS DTC for XA transactions follow below steps and make sure that it is running when the SQL Server service is started by setting it to Automatic in Services.
On Windows XP and Windows Server 2003:
- Open Component Services or click Run, type dcomcnfg and then press OK to open Component Services.
- Expand Component Services, Computers and right-click My Computer, and then select Properties.
- Click the MSDTC tab, and then click Security Configuration.
- Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.
- Close Component Services. Stop and then restart SQL Server to ensure that it syncs up with the MS DTC changes.
On Windows Vista:
- Click the Start button, type dcomcnfg in the Start Search box, and then press ENTER to open Component Services. You can also type %windir%\system32\comexp.msc in the Start Search box to open Component Services.
- Expand Component Services, Computers, My Computer, and then Distributed Transaction Coordinator.
- Right-click Local DTC and then select Properties.
- Click the Security tab on the Local DTC Properties dialog box.
- Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.
- Close Component Services. Stop and then restart SQL Server to make sure that it syncs up with the MS DTC changes.
Configuring the JDBC Distributed Transaction Components
- Copy the sqljdbc_xa.dll from the JDBC installation directory to the Binn directory of every SQL Server computer that will participate in distributed transactions.
- Execute the database script xa_install.sql on every SQL Server instance that will participate in distributed transactions, you need to be an administrator of the SQL server instance. These extended stored procedures implement distributed transaction and XA support for the Microsoft SQL Server JDBC Driver.
- SqlJDBCXAUser role will be created in master database .The SqlJDBCXAUser role is strictly defined in the master database because it is used to grant access to the SQL JDBC extended stored procedures that reside in master.
- To create your own role for security purposes, you will have to define the role in each database
USE master
GO
EXEC sp_grantdbaccess 'shelby', 'newrole'
GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'newrole'
You will have to first grant individual users access to master, and then grant them access to the SqlJDBCXAUser role while you are logged into the master database.
More details to configure XA Transactions http://msdn.microsoft.com/en-us/library/aa342335.aspx
If you have Java Development Kit installed on any of your test machines. Then the following code from MSDN may help you test the configuration as well! I am explaining what you should look for while running this code.
Steps to confirm whether XA transactions are configured correctly
1. Copy the code from the URL http://msdn.microsoft.com/en-us/library/aa342335.aspx
import java.net.Inet4Address;
import java.sql.*;
import java.util.Random;
import javax.transaction.xa.*;
import javax.sql.*;
import com.microsoft.sqlserver.jdbc.*;
public class testXA {
public static void main(String[] args) throws Exception
{
// Create variables for the connection string , change the serverName , portNumber ,databaseName ,user & password as per your environment
String prefix = "jdbc:sqlserver://";
String serverName = "MyServerName";
int portNumber = 62204;
String databaseName = "MSTest";
String user = "TestLogin";
String password = "12345";
String connectionUrl = prefix + serverName + ":" + portNumber
+ ";databaseName=" + databaseName + ";user=" + user + ";password=" + password;
.
.
}
3. Check where sqljdbc4.jar file exist in the machine and save the testXA.java file accordingly , in this case sqljdbc4.jar is saved in folder "C:\glassfish3\jdk7\bin>javac -cp "C:\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\ "
2. Save the file as testXA.java under the folder " C:\glassfish3\jdk7\bin>javac -cp "C:\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\ "
4. Compile and run the java file.
a. Code to compile
C:\glassfish3\jdk7\bin>javac -cp "C:\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc4.jar" testXA.java
b. Code to Run
C:\glassfish3\jdk7\bin>java -classpath ".;C:\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc4.jar" testXA
c. We can see following Output
Write -> xid = formatId=4660 gtrid(64)={0xac100c040100000001000000949d721f000000..} bqual(64)={..}
Read -> xid = formatId=4660 gtrid(64)={0xac100c040100000001000000949d721f00..} bqual(64)={0xac100c040100000001000000949d721f000000000
Since we could see the xid ,this Confirms us that XA transactions are working for the “MyServerName” server , when we check in error logs we also could see the XAdll’s messages
6. Another way to confirmed that XA transactions are working by checking in Component service, snap shot attached.
As we run the java program, we see the Aggregate -> Committed count increase by 1, which confirms us XA transactions are working.
7. We can also see that SQLJDBC_XA.dll gets loaded to memory in SQL Server Error logs
Please Note: Code and notes highlighted can be varied from system to system.
Happy Coding!!!
Author : Archana (MSFT), SQL Developer Engineer, Microsoft
Reviewed by : Jay(MSFT), SQL Developer Technical Lead, Microsoft