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, you
must follow these steps:
On Windows XP and
Windows Server 2003:- Select .
- Select and right-click My
Computer, and 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.
- Click OK again to close the Properties window,
and then close Component Services.
- Restart SQL Server to ensure that it syncs up with the MS DTC
changes.
On Windows Vista, Windows
7, Windows Server 2008 R2, or Windows Server 2012:- Select .
- Select .
- Right-click Local DTC and then select Properties.
- Click the Security tab on the Local
DTC Properties window.
- Select the Enable XA Transactions check
box, and click OK. This will restart the MS
DTC service.
- Click OK again to close the Properties
window, and then close Component Services.
- Restart SQL Server to ensure that it syncs up with the MS DTC
changes.
- Configure the JDBC Distributed
Transaction Components:
- If you haven't installed IBM® Business Process Manager, download
the Microsoft SQL Server JDBC Driver 3.0 from the Microsoft website
and extract it to any folder.
- If 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
from the JDBC unarchived directory to the Binn directory
(for a default SQL Server install, the location is C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn) of SQL Server computer. If you are using XA transactions
with a 32-bit SQL Server, use the sqljdbc_xa.dll file
in the x86 folder, even if the SQL Server is
installed on a x64 processor. If you are using XA transactions with
a 64-bit SQL Server on the x64 processor, 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.
- Open the SQL Server Management Studio to locate
the security folder under the master database. To
grant permissions to a specific user to participate in distributed
transactions with the JDBC driver, add the user to the SqlJDBCXAUser role
in the master database (for example, for a Lombardi user add master
database in User mappings and check SqlJDBCXAUser role).
- Follow these steps for configuring
Windows authentication:
- Locate sqljdbc_auth.dll.
If you haven't installed IBM Business Process Manager, go
to the BPM_install_root\jdbcdrivers\SQLServer\auth to
obtain the file. If you haven't installed 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/MSSQL10_50.MSSQLSERVER/MSSQL/Binn) of SQL Server
computer. If your JRE is 32-bit , use the sqljdbc_auth.dll file
in the x86 folder, even if the SQL Server is
installed on a x64 processor. If your JRE 64-bit, 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 .
- Expand
- 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.