Date: April 4, 2019
Tags: SQL Server Failover Cluster Instance
Configure SQL Server Failover Cluster Instance On Azure Virtual Machines With Msdtc #Sql #Azure #Msdtc
You probably know that we have included plenty of step-by-step guides for building SQL Server Failover Cluster Instances (FCI) on Azure, from SQL Server 2008 through the latest. Here are some links to get you started. But really there is very little difference in the configuration between the different versions of Windows and SQL Server. So I think you will be able to figure it out regardless of what versions you use.
STEP-BY-STEP: HOW TO CONFIGURE A SQL SERVER 2008 R2 FAILOVER CLUSTER INSTANCE IN AZURE
What I have not addressed is what to do about MSDTC. Microsoft addressed that in this article posted here.
However, that article/video only addresses SQL Server 2016 and later. The good news is that most of that guidance can be applied to SQL Server 2008/2012/2014. Until I have time to do a proper step-by-step guide I wanted to jot down some basic notes, more as a reminder to myself. However, you might find this information useful as well in the meantime.
The steps below assume you have already created a SQL Server FCI in Azure and clustered the DTC resource. Reference the guides above for the details on those steps. The steps below really just detail the load balancer configuration required in Azure to make this work.
Create Load Balancer For MSDTC
The MSDTC resource will require its own load balancer. Instead of creating a new load balancer, we will add a new frontend to the load balancer that should already be configured for the SQL Server FCI. Of course this frontend IP address should match the cluster IP address associated with the clustered MSDTC resource.
For the backend pool just reuse the existing pool that you created that contains the SQL cluster nodes.
You will need to create a new health probe dedicated to the MSDTC resource. The port you use has to be different than the one you used for the SQL resource. Don’t use 59999. Maybe use something like 49999.
The final step is to create the load balancing rule for MSDTC. Create a new rule and reference the MSDTC frontend that we just created and the existing backend. Next we need to create a new load balancing rule. MSDTC uses ephemeral ports, which is a big range of ports. As you create the rule, you have to select the box that says “HA Ports”. Finally, make sure Direct Server Return is enabled.
Update MSDTC Cluster IP Resource
Works like the SQL Server Cluster IP address. We need to run a Powershell command that will for the MSDTC cluster IP resource to respond to the health probe we just created that probes port 49999. It also sets the subnet mask of that MSDTC cluster IP address to 255.255.255.255 to avoid IP address conflicts with the load balancer frontend we setup that shares the same address.
# Define variables $ClusterNetworkName = “”
# the cluster network name (Use Get-ClusterNetwork on
Windows Server 2012 of 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.255Confirm It Is Working!
You can use DTCPing or go into Component Services and look under Computers>My Computers>Distributed Transaction Coordinator where you should see a local DTC and a clustered DTC. Any distributed transactions should appear in the clustered DTC, not the local DTC. Check out this video for an example of how to create a distributed transaction for testing.
Next Steps
This is a quick and dirty guide. For the experienced user it should get your MSDTC resource up and running in Azure. I’ll be publishing a detailed step-by-step guide in the near future. In the meantime, if you get stuck don’t hesitate to reach out to me on Twitter @daveberm