If you have been following my blog, you probably know that I write a lot of step-by-step guides for building SQL Server Failover Cluster Instances (FCI) on Azure. Today, I want to address a topic that I haven’t covered yet – what to do about MSDTC (Microsoft Distributed Transaction Coordinator).

Microsoft has provided some guidance on this topic in a recent article, but it only covers SQL Server 2016 and later versions. However, most of the guidance can be applied to SQL Server 2008/2012/2014 as well. While I plan to create a detailed step-by-step guide in the future, I wanted to share some basic notes that can help you configure MSDTC for your SQL Server FCI in Azure.

Before we begin, please note that the steps below assume you have already created a SQL Server FCI in Azure and clustered the DTC resource. If you need help with these steps, refer to my previous guides for detailed instructions.

Create Load Balancer for MSDTC

The first step is to create a load balancer specifically for the MSDTC resource. Instead of creating a new load balancer, we will add a new frontend to the existing load balancer that is already configured for the SQL Server FCI. Make sure the frontend IP address matches the cluster IP address associated with the clustered MSDTC resource.

For the backend pool, you can reuse the existing pool that contains the SQL cluster nodes. However, you will need to create a new health probe dedicated to the MSDTC resource. Use a different port than the one used for the SQL resource, such as 49999.

Finally, create a load balancing rule for MSDTC. This rule should reference the MSDTC frontend that we just created and the existing backend.

Update MSDTC Cluster IP Resource

Similar to the SQL Server Cluster IP address, we need to run a PowerShell command to configure the MSDTC cluster IP resource. This command will make the resource respond to the health probe we created earlier, which probes port 49999. It will also set the subnet mask of the MSDTC cluster IP address to 255.255.255.255 to avoid IP address conflicts with the load balancer frontend.

Here is an example PowerShell command:

# Define variables
$ClusterNetworkName = ""
# the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 or higher to find the name of the MSDTC resource)
$IPResourceName = ""
# the IP Address resource name of the MSDTC resource
$ILBIP = ""
# the IP Address of the Internal Load Balancer (ILB) and MSDTC resource

Import-Module FailoverClusters

# If you are using Windows Server 2012 or higher:
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{Address=$ILBIP;ProbePort=49999;SubnetMask="255.255.255.255";Network=$ClusterNetworkName;EnableDhcp=0}

# If you are using Windows Server 2008 R2 use this:
#cluster res $IPResourceName /priv enabledhcp=0 address=$ILBIP probeport=59999  subnetmask=255.255.255.255

Confirm it is working!

After completing the above steps, it’s important to verify that MSDTC is functioning correctly. You can use DTCPing or go into Component Services and look under Computers > My Computers > Distributed Transaction Coordinator. Here, you should see a local DTC and a clustered DTC. Any distributed transactions should appear in the clustered DTC, not the local DTC.

For a practical example of how to create a distributed transaction for testing, you can refer to this video.

Next Steps

While this guide provides a quick overview of configuring MSDTC for SQL Server Failover Cluster Instances on Azure, I understand that experienced users may require more detailed instructions. Therefore, I will be publishing a comprehensive step-by-step guide in the near future. In the meantime, if you encounter any issues or have any questions, feel free to reach out to me on Twitter @daveberm.