Configuring Microsoft SQL Server for XA

SimpleDataSourceBean example

SimpleDataSourceBean ds = new SimpleDataSourceBean();
ds.setUniqueResourceName("mssql");
ds.setXaDataSourceClassName("com.microsoft.sqlserver.jdbc.SQLServerXADataSource");
ds.setXaDataSourceProperties("user=informix;password=informix;serverName=localhost-mssql");
ds.setConnectionPoolSize(5);

Notes

SQL Server version 2005 Express with Microsoft JDBC driver version 1.2 is known to be working fine. The Microsoft JDBC driver version 1.1 has a lot of problems with XA support and is not supported.

Getting XA support in SQL server is no easy task.

You have to copy the sqljdbc_xa.dll inside SQL Server's binn folder then execute xa_install.sql as user sa as documented. You must also enable MS DTC's XA support and grant SqlJDBCXAUser role to your user as explained here: http://msdn2.microsoft.com/en-us/library/aa342335.aspx

Note to Windows XP users: Windows XP lacks some internal components related to DTC as discussed here: http://forums.microsoft.com/msdn/showpost.aspx?postid=262525. Microsoft has fixed this issue and released a Hotfix: http://support.microsoft.com/kb/922668. Unfortunately you cannot freely download it, you must call Microsoft support to get it. Also, it refuses to install on any non-English version of Windows. Windows 2000 and Windows Server 2003 are said to be unaffected by this problem.

-- LudovicOrban - 04 Nov 2007