How to configure XA transactions with Microsoft SQL Server and TIBCO EMS Server in JBoss

This article describes the processes involved in enabling XA transactions within an SQL Server and TIBCO EMS environment.

Prerequisites
1. SQL Server JDBC Driver
2. JBoss 5.1.0
3. TIBCO EMS server
4. An SQL Server 2000/2005 instance

Enabling XA transactions in SQL Server:
1. Download the SQL Server JDBC Driver from here and unzip the file.

2. Prior to running the SQL script you must copy the extended stored procedure dll SQLJDBC_XA.dll to the target SQL Server's Binn folder (typically in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn).

3. Permissions to the distributed transaction support procedures for the Microsoft SQL Server JDBC Driver 2.0 are granted through the SQL Server role SqlJDBCXAUser. To maintain a secure default configuration, no user is granted access to this role by default.

4. Run the following SQL script in the target SQL Server:

use master
go

-- Drop any existing procedure definitions.
exec sp_dropextendedproc 'xp_sqljdbc_xa_init' 
exec sp_dropextendedproc 'xp_sqljdbc_xa_start'
exec sp_dropextendedproc 'xp_sqljdbc_xa_end'
exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare'
exec sp_dropextendedproc 'xp_sqljdbc_xa_commit'
exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback'
exec sp_dropextendedproc 'xp_sqljdbc_xa_forget'
exec sp_dropextendedproc 'xp_sqljdbc_xa_recover'
exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_forget_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_init_ex'
go

-- Install the procedures.
exec sp_addextendedproc 'xp_sqljdbc_xa_init', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_start', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_end', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_commit', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_recover', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_init_ex', 'SQLJDBC_XA.dll'
go

-- Create the [SqlJDBCXAUser] role in master database.
-- The SQL administrator can later add users to this role to allow users to participate 
-- in Microsoft SQL Server JDBC Driver 2.0 distributed transactions.
sp_addrole [SqlJDBCXAUser]
go

-- Grant privileges to [SqlJDBCXAUser] role to the extended stored procedures.
grant execute on xp_sqljdbc_xa_init to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_start to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_end to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_commit to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_recover to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_init_ex to [SqlJDBCXAUser]
go

-- Add users to the [SqlJDBCXAUser] role as needed.

-- Example for adding a SQL authentication user to the SqlJDBCXAUser role.
-- exec sp_addrolemember [SqlJDBCXAUser], 'MySQLUser'

-- Example for adding a windows domain user to the SqlJDBCXAUser role.
-- exec sp_addrolemember [SqlJDBCXAUser], 'MyDomain\MyWindowsUser'

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

5. From Control Panel, open Administrative Tools, and then open Component Services. You can also click the Start button, click Run, type dcomcnfg in the Open box, and then press OK to open Component Services.

6. Expand Component Services, Computers and right-click My Computer, and then select Properties.

7. Click the MSDTC tab, and then click Security Configuration.

8. Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.

9. Click OK again to close the Properties dialog box, and then close Component Services.

10. Stop and then restart SQL Server to ensure that it syncs up with the MS DTC changes.

Configuring an XA Datasource
This is a sample XA Datasource to connect to SQL Server 2005 and SQL Server 2000. This -ds.xml file must be place under the deploy folder of your server.

serviceName-ds.xml

    
        nameOfDataSource
        
        com.microsoft.sqlserver.jdbc.SQLServerXADataSource
        server
        database
        cursor
        username
        password
        jdbc:sqlserver://server:1433;databasename=database
        false
        
        select 1
        
        select 1
        
        
            
            MS SQLSERVER2000
        

    

Configuring TIBCO EMS Provider
This is a sample JMS Provider to connect to TIBCO EMS Server. Please note that the Connection Factory uses tx-connection-factory element which tells the Transaction Manager that this connection will take part in JTA transactions. This -ds.xml file must be place under the deploy folder of your server.

ems-ds.xml

EmsQueueProvider
org.jboss.jms.jndi.JNDIProviderAdapter
XAQueueConnectionFactory
XAQueueConnectionFactory
XATopicConnectionFactory

java.naming.factory.initial=com.tibco.tibjms.naming.TibjmsInitialContextFactory
java.naming.provider.url=tcp://server:port
java.naming.security.principal=username
java.naming.security.credentials=password




EMSQueueFactory
jms-ra.rar
org.jboss.resource.adapter.jms.JmsConnectionFactory
javax.jms.Queue
java:/EmsQueueProvider
20
username
password

Also add the following factories to your EMS Server as follows:

create factory XAQueueConnectionFactory xaqueue url=tcp://server:port
create factory XATopicConnectionFactory xatopic url=tcp://server:port
create factory XAConnectionFactory xageneric url=tcp://server:port

Sending messages to destinations
The following code snippet creates an Connection, creates a transacted session and sends a message to a queue. The message will appear in the queue when the container commits the transaction.

Context ctx = new InitialContext();
        // get the connection factory fron jndi and create a connection
        ConnectionFactory connectionFactory = (ConnectionFactory)ctx.lookup("java:/EMSQueueFactory");
        Connection connection = connectionFactory.createConnection();

        // create a session
        QueueSession session = connection.createQueueSession(true,javax.jms.Session.AUTO_ACKNOWLEDGE);
        Queue queue = session.createQueue(queueName);
        QueueSender sender = session.createSender(queue);

        // create an send a message
        msg = session.createTextMessage();
        msg.setText("Hello");
        sender.send(queue, msg);

        // close the connection
        connection.close();