Configuring XA transactions

You must configure XA transactions after the Microsoft SQL Server database is installed and before you start the server. The SQL Server JDBC driver provides support for Java Platform, Enterprise Edition/JDBC 2.0 optional distributed transactions. JDBC connections obtained from the SQLServerXADataSource class can participate in standard distributed transaction processing environments such as Java Platform, Enterprise Edition (Java EE) application servers.

About this task

Failure to configure the XA transactions can result in the following error when the server starts:javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Could not find stored procedure 'master..xp_sqljdbc_xa_init_ex'."..

The MS DTC service should be marked Automatic in Service Manager to make sure that it is running when the SQL Server service is started.

Procedure

To enable MS DTC for XA transactions, complete the following steps:
1. Select Control Panel > Administrative Tools > Component Services.
2. Select Distributed Transaction CoordinatorComponent Services > Computers > My Computer.
3. Right-click Local DTC and then select Properties.
4. Click the Security tab on the Local DTC Properties window.
5. Select the Enable XA Transactions check box, and click OK. This will restart the MS DTC service.
6. Click OK again to close the Properties window, and then close Component Services.
7. Restart SQL Server to ensure that it syncs up with the MS DTC changes.
Configure the JDBC Distributed Transaction Components:
1. If you haven't installed IBM® Business Process Manager, download the Microsoft SQL Server JDBC Driver 4.0 from the Microsoft website and extract it to any folder.
2. If IBM BPM is already installed, go to bpm_install_root\jdbcdrivers\SQLServer\xa to obtain the files you require in the following steps:
  - Copy the sqljdbc_xa.dll file to the Binn directory (for a default SQL Server install, the location isC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn) of SQL Server computer. Use the sqljdbc_xa.dll file in the x64 folder.
  - Run the xa_install.sql database script on SQL Server. For example; from the command prompt, run sqlcmd -i xa_install.sql. This script installs the extended stored procedures that are called by sqljdbc_xa.dll. These extended stored procedures implement distributed transaction and XA support for the Microsoft SQL Server JDBC Driver. You will need to run this script as an administrator of the SQL Server instance. You can ignore errors about unable to drop procedures that don't exist.
Follow these steps for configuring Windows authentication:
- Locate the sqljdbc_auth.dll file. If you have installed IBM BPM, go to the BPM_install_root\jdbcdrivers\SQLServer\auth directory to obtain the file. If you haven't installed IBM BPM, locate the DLL file in the Microsoft JDBC driver package that you downloaded.
- Copy the sqljdbc_auth.dll file to the Binn directory (for a default SQL Server install, the location is C:/Program Files/Microsoft SQL Server/MSSQL12.MSSQLSERVER/MSSQL/Binn) of the SQL Server computer. Use the sqljdbc_auth.dll file in the x64 folder.

What to do next

After you configure the XA transactions and before you start the server, you must configure your TCP/IP connectivity using the below steps:

From Start menu, click Microsoft SQL Server 2014 > Configuration Tools > SQL Server Configuration Manager.
Expand SQL Server Network Configuration > Protocols for SQL2014
Locate TCP/IP on the right-hand side.
Double click TCP/IP and enable it under the Protocol tab.
Click the IP Addresses tab to enable the TCP port for each configured IP address.

In some situations, it is possible that you could observe the following lock timeout errors in IBM BPM SystemOut.logs:

Lock request time out period exceeded.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 
  Lock request time out period exceeded.

To help prevent a lock timeout from occurring (or to resolve a lock timeout after it has occurred), you can add the custom property ENABLE_XARESOURCE_TIMEOUT on the transaction service. The transaction service will provide an appropriate timeout value to the XA resources when they are enlisted in global transactions. Information about the ENABLE_XARESOURCE_TIMEOUT custom property is found in the topic Transaction service custom properties.