Enabling XA Transactions in SQL Server 2008 for ADEP Interactive Statements

For Oracle, see this.

The Adobe Customer Experience solution “Customer Communications – Interactive Statements” (formerly Interactive Statements Solution Accelerator) requires an XA-type JDBC data source. XA is a specification for distributed transaction processing that involves multiple resources such as a database, or a JMS queue. More here.

In the JBoss Turnkey as well as in the pre-configured JBoss EAP tree, it is defined in the file adobe-sa-ds.xml in %JBOSS_HOME%\aep_sqlserver\deploy\. The JNDI name is “AdobeDefaultSA_DS“. It uses a different driver class (com.microsoft.sqlserver.jdbc.SQLServerXADataSource) compared to the other ADEP Document Services JDBC data sources (IDP_DS and EDC_DS) which are non-XA (com.microsoft.sqlserver.jdbc.SQLServerDriver). Both these classes are available in version 3 of Microsoft’s SQL Server JDBC Driver which supports SQL Server 2008.

Default SQL Server 2008 installs are not configured for XA transactions. If you have a JBoss 5.1 EAP instance running ADEP Document Services against Microsoft SQL Server 2008, you will get the following error messages every 2 minutes in JBoss server.log:

[com.arjuna.ats.internal.jta.recovery.xarecovery1] Local XARecoveryModule.xaRecovery got XA exception 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’.”, XAException.XAER_RMERR

To configure a SQL Server 2008 instance for XA, your SQL Server 2008 DBA has to perform several steps server-side, on the SQL Server machine:

1) Download the Microsoft SQL Server JDBC Driver 3.0 from here and install it somewhere (run the .exe)

2) Copy sqljdbc_xa.dll from %JDBC_DRIVER_INSTALL_ROOT%\sqljdbc_3.0\enu\xa\x64\ (assuming the SQL Server 2008 instance is 64-bit) to %SQLSERVER_INSTALL_ROOT%\Instance_root\MSSQL10.MSSQLSERVER\MSSQL\Binn\

CREATE XA STORED PROCEDURES and ROLES

3) Copy-paste the contents of the script xa_install.sql in %JDBC_DRIVER_INSTALL_ROOT%\sqljdbc_3.0\enu\xa\ and execute it using SQL Server Management Studio’s Query Editor.

4) Confirm the creation of new stored procedures in SQL Server Management Studio. In the left pane, navigate to Programmability->Extended Stored Procedures for the “master” database – it should look something like this:

APPLY ROLE TO J2EE APPSERVER JDBC USER

5) Using SQL Server Management Studio’s Query Editor, assign the ‘SqlJDBCXAUser’ role to the SQL Server user that the JDBC connection pool uses for authentication and execute it. The following script assigns the role to the user ADEPJBoss:

USE master
GO
EXEC sp_grantdbaccess 'ADEPJBoss', 'ADEPJBoss'
GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'ADEPJBoss'

“Command(s) completed successfully.”

CONFIGURE THE DISTRIBUTED TRANSACTION COORDINATOR (DTC)

6) Choose the Windows menu Administrative Tools->Component Services.

7) In the left navigation pane, navigate to Console Root->Component Services->Computers->My Computer->Distributed Transaction Coordinator->Local DTC.

8) Highlight ‘Local DTC’, right-click the mouse and choose ‘Properties’.

9) Choose the ‘Security’ tab. Ensure that the checkbox for ‘Enable XA Transactions’ is checked. Please see screenshot below.

10) Click Apply, then OK.

CONFIGURE REQUIRED WINDOWS SERVICES TO START AUTOMATICALLY

11) Choose the Windows menu Administrative Tools->Services. In it, make sure that the “Distributed Transaction Coordinator” service is set to start automatically. Start it.

12) Also make sure that the other services it depends on, namely ‘Remote Procedure Call‘, ‘Security Accounts Manager‘ are also set to start automatically.

13) Re-start the SQL Server instance

14) Re-start the appserver instance hosting ADEP Document Services and make sure that the error messages no longer appear.

VN:F [1.9.22_1171]
Was this helpful? Please rate the content.
Rating: 9.4/10 (16 votes cast)
Enabling XA Transactions in SQL Server 2008 for ADEP Interactive Statements, 9.4 out of 10 based on 16 ratings
This entry was posted in ADEP, Customer Experience Solutions, Document Services and tagged , , , , , , . Bookmark the permalink.

One Response to Enabling XA Transactions in SQL Server 2008 for ADEP Interactive Statements

  1. Chalukya says:

    Thank Jayan, finally was able to install ADEP successfully …

    Platform: Windows 2000 Server x64, JBOss, SQL Server 2008.