Enable XA transactions in Microsoft SQL Server database

You must enable XA transactions in Microsoft SQL Server database and run Microsoft Distributed Transaction Coordinator (MSDTC) Service by following the instructions depending on your operating system:

This page discusses:

Enable XA distributed transactions on Windows

  1. Launch and connect SQL Server Management Studio or use sqlcmd utility
  2. Execute this stored procedure on master database EXEC sp_sqljdbc_xa_install

Configure MSDTC service for XA transactions on Windows

  1. Click the Start button, type dcomcnfg in the Start Search box, and then press ENTER to open Component Services. You can also type %windir%\system32\comexp.msc in the StartSearch box to open Component Services.
  2. Expand Component Services, Computers, My Computer, and then Distributed Transaction Coordinator.
  3. Right-click Local DTC and then select Properties.
  4. Click the Security tab on the Local DTC Properties dialog box.
  5. Select the Enable XA Transactions check box, and then click OK. This action will cause an MS DTC service restart.
  6. Click OK again to close the Properties dialog box, and then close Component Services.
  7. Stop and then restart SQL Server to make sure that it syncs with the MS DTC changes.
For more details refer: https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-xa-transactions?view=sql-server-ver15

Enable XA distributed transactions on Linux

  1. Launch and connect SQL Server Management Studio or use sqlcmd utility
  2. Execute this stored procedure on master database EXEC sp_sqljdbc_xa_install

Configure MSDTC service for XA transactions on Linux

  1. Configure RPC and MSDTC ports
    • Use mssql-conf to set the network.rpcport value. The following example sets it to 13500

      sudo /opt/mssql/bin/mssql-conf set network.rpcport 13500

    • Set the distributedtransaction.servertcpport value. The following example sets it to 51999.

      sudo /opt/mssql/bin/mssql-conf set distributedtransaction.servertcpport 51999

    • Restart SQL Server

      sudo systemctl restart mssql-server

  2. Configure the firewall

    Configure the firewall to allow communication on servertcpport and port 135. This enables the RPC endpoint-mapping process and MSDTC process to communicate externally to other transaction managers and coordinators

    sudo firewall-cmd --zone=public --add-port=51999/tcp --permanent

    sudo firewall-cmd --zone=public --add-port=135/tcp --permanent

    sudo firewall-cmd –reload

  3. Configure port routing

    Configure the Linux server routing table so that RPC communication on port 135 is redirected to SQL Server's network.rpcport

    sudo firewall-cmd --permanent --add-forward-port=port=135:proto=tcp:toport=13500

    sudo firewall-cmd –reload

  4. Verify

    At this point, SQL Server should be able to participate in distributed transactions. To verify that SQL Server is listening, run the netstat command (you might have to first install the net-tools package):

    sudo netstat -tulpn | grep sqlservr

    You should see output similar to the following:

    tcp 0 0 0.0.0.0:1433 0.0.0.0:* LISTEN 13911/sqlservr
tcp 0 0 127.0.0.1:1434 0.0.0.0:* LISTEN 13911/sqlservr
tcp 0 0 0.0.0.0:13500 0.0.0.0:* LISTEN 13911/sqlservr
tcp 0 0 0.0.0.0:51999 0.0.0.0:* LISTEN 13911/sqlservr
tcp6 0 0 :::1433 :::* LISTEN 13911/sqlservr
tcp6 0 0 ::1:1434 :::* LISTEN 13911/sqlservr
tcp6 0 0 :::13500 :::* LISTEN 13911/sqlservr
tcp6 0 0 :::51999 :::* LISTEN 13911/sqlservr

  5. Restart SQL Server

    sudo systemctl restart mssql-server

    However, after a restart, SQL Server does not start listening on the servertcpport until the first distributed transaction. In this case, you would not see SQL Server listening on port 51999 in this example until the first distributed transaction.

For more details refer: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-msdtc?view=sql-server-ver15