Configure SQL Server Failover Cluster Instance on Azure Virtual Machines with MSDTC

  • Article

Configure SQL Server Failover Cluster Instance

If you are running a SQL Server Failover Cluster Instance on premises and looking to migrate to Azure, you might be a little intimidated. This architecture is a bit complex because it uses quite a few different technologies all working together. My goal is to help clarify the pieces and steps needed to build this out.

There is already a great article that walks through the configuration. It has links to supplemental information as well, so it's an excellent reference guide. There is, however, one thing it does not cover. It does not show how to configure a clustered MSDTC. In this article I will list the pieces required to make this work and give you a video walk through on the entire configuration. Read this article first to become familiar with the steps.

Technologies Involved

  • Windows Server
  • Windows Clustering
  • Storage Spaces Direct (S2D)
  • Cluster Shared Volumes
  • SQL Server
  • MSDTC

Configuration Steps

  • Create Virtual Machines
    • Must be in an Availability Set
    • Use Standard IPs for MSDTC
    • Have at least 2 data disks
  • Add Windows Failover Cluster Feature
  • Create Windows Failover Cluster
    • Create a witness if needed
  • Make sure data disk have no partitions
  • Enable Storage Spaces Direct (S2D)
  • Create volumes from S2D pool
  • Install SQL Server Failover Cluster Instance
  • Install a clustered MSDTC
  • Create an Internal Azure Load Balancer
    • Needs to be a Standard Load Balancer for MSDTC (VM IPs and Load Balancers must match SKUs)
    • Front end for SQL and one for the MSDTC
    • Health probe for SQL and the MSDTC
    • Load balancing rule for SQL and the MSDTC
  • Configure SQL IP for probe port
  • Configure MSDTC IP for probe port

MSDTC Requirements

To make a clustered MSDTC work in this architecture we need to make sure of a couple things.  We need to use a Standard Load Balancer instead of a Basic Load Balancer.  Since we have to use a Standard Load Balancer that means the IPs for our VMs also have to be Standard IPs.  You can only add Basic IPs to a Basic Load Balancer and Standard IPs to a Standard Load Balancer.

The other requirement is the Windows version.  The MSDTC did not support using a Cluster Shared Volume as its shared storage when Windows Server 2016 was released.  Support for that was not introduced until Windows Server 2016 Release Build Version 1709.  Although the support was added to release build versions 1709 and 1803, neither of those builds include the desktop experience (No GUI). In Windows Server 2019 we get both the Desktop Experience as well as support for the MSDTC to use Cluster Shared Volumes.

Configuration Walk Through

Here is a video where I walk through the entire build process of this architecture including both SQL and the MSDTC.  Note that this is just for demonstration purposes and not following all best practices for building Windows Server Failover Clusters.  For instance, I do not even configure a witness which you would absolutely do for a production build.

https://youtu.be/GS12sfOdC1o

Resources

MSDTC support and configuration can be a bit confusing.  Read this article on MSDTC Supported Configurations for more information.

Comments

  • Anonymous
    February 15, 2019
    Great video. For earlier versions of SQL Server that don't support S2D you can use the same guidance when your SQL Server FCI uses iSCSI or 3rd party disk resources like SIOS DataKeeper.
    • Anonymous
      February 19, 2019
      We need to mention for full disclosure and transparency that David works for SIOS.