SQL Server 2019 (and 2017 CU 16 onwards) – installing the distributed transaction components
Starting with SQL Server 2019 and SQL Server 2017 CU 16 onwards, the JDBC XA distributed transaction components are included in the SQL Server engine, and can be enabled or disabled with a system stored procedure. To enable the required components to perform XA distributed transactions using the JDBC driver, execute the following stored procedure:
-
EXEC sp_sqljdbc_xa_install
To disable the previously installed components, execute the following stored procedure.
-
EXEC sp_sqljdbc_xa_uninstall
SQL Server – enabling the distributed transaction components by copying the DLL
As an alternative option, instead of using the preferred solution with the stored procedure as explained in the previous section, it is still possible to use “sqljdbc_xa.dll” provided by the JDBC driver in the way that was suggested by Microsoft previously. However, this has the disadvantage that the DLL must also be upgraded on the MS SQL server whenever the JDBC driver is updated on the client.
Sometimes it was also reported that executing the stored procedure as described above is not enough, and the following errors were caused in the ImageMaster log file:
Could not load the DLL SQLJDBC_XA.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found).
In such a scenario, copy the required driver file “sqljdbc_xa.dll” to the “Binn” directory of each SQL Server instance that will be used in distributed transactions. The target directory of a 64 bit database instance for MS SQL 2019 could typically look like this:
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Binn
This example represents the same step that is done as described for SQL Server 2017 below but targeting the 2019 installation directory instead.
SQL Server 2017 – enabling the distributed transaction components by copying the DLL
Copy the file “sqljdbc_xa.dll” from the JDBC driver installation directory to the “Binn” directory of each SQL Server instance that will be used in distributed transactions. For example the BINN directory of a 64 Bit db instance is C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\BINN. Note that the file “sqljdbc_xa.dll” must be copied only in the specified directory (in the example above the “MSSQL14” is the version of the SQL server (SQL Server 2017), “MSSQLSERVER” is the Instance ID, can be set to any value during the installation process).
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.