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 version 1709. Although the support was added to version 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.
Resources
MSDTC support and configuration can be a bit confusing. Read this article on MSDTC Supported Configurations for more information.
You should also look at MSDTC Best Practices with an Availability Group.
I have seen a couple people on StackOverflow talk about slow S2D in Azure as well as S2D failover not quite ready for production. Any comment on either of those? I have never seen anyone elaborate on this, just throw it out.
So far I have not seen any issues regarding failover not working. As for slowness that depends on the configuration. Since you are using storage attached to Azure VMs, everything around performance tuning disks applies. So for instance, premium storage is expected to perform faster and better than standard storage. Another example is for standard storage you can add multiple drives to increase IOPS. These things all come into play.
I found one of the ServerFault (not stack, sorry) answers taht I saw here:
https://serverfault.com/a/894267/124742
Basically saying S2D on azure is slow, also another commenter called it a “daredevil” move to run this in production. I am just trying to evaluate a solution to migrate a cluster to the azure cloud, and I see comments like these. I am not seeing much in the way of real world examples.
I did run a test on our SQL FCI in azure for writes, and I saw 7500ms write latency for sql server as compared to 12ms in our current production enironment. The reads were on par at 12ms in the cloud. I did have the disks (2 P30s per vm striped in S2D) in read caching. I have set the cache mode to none in azure and will retest.
Thanks for answering!
Check out this article for some good S2D configuration performance tuning tips.
https://www.petri.com/storage-spaces-performance-tuning
Great information. Since last week, I am gathering details about the SQL experience.
There are some amazing details on your blog which I didn’t know. Thanks.
At the NTSSUG you mentioned that when combining premium storage there is a formula to calculate IOPS for S2D when using premium disks on Azure VMs. Do you have a link to any documentation about this?
Here are some links to information on how to performance tune S2D. At the end of the day we have to think about VM sizing, striping multiple disks in the VM, and S2D configuration. It is a 3 step process and is a pretty detailed and an in depth configuration. These articles should get you on the right track.
This article is an overview of premium storage in Azure and how it works with things like the difference between managed and unmanaged disks. Below the link, I pulled out a specific part that applies directly to S2D configuration.
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/premium-storage
• Data disks
You can use premium and standard disks in the same Premium Storage VM. With Premium Storage, you can provision a VM and attach several persistent data disks to the VM. If needed, to increase the capacity and performance of the volume, you can stripe across your disks.
Note
If you stripe premium storage data disks by using Storage Spaces, set up Storage Spaces with 1 column for each disk that you use. Otherwise, overall performance of the striped volume might be lower than expected because of uneven distribution of traffic across the disks. By default, in Server Manager, you can set up columns for up to 8 disks. If you have more than 8 disks, use PowerShell to create the volume. Specify the number of columns manually. Otherwise, the Server Manager UI continues to use 8 columns, even if you have more disks. For example, if you have 32 disks in a single stripe set, specify 32 columns. To specify the number of columns the virtual disk uses, in the New-VirtualDisk PowerShell cmdlet, use the NumberOfColumns parameter. For more information, see Storage Spaces Overview and Storage Spaces FAQs.
You can use multiple storage accounts to increase bandwidth.
https://docs.microsoft.com/en-us/azure/storage/common/storage-scalability-targets#scalability-targets-for-a-storage-account
This article describes storage spaces and specifically addresses Interoperability with Azure VMs.
https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/hh831739(v=ws.11)#interoperability-with-azure-virtual-machines
This article talks about S2D on an Azure VM Cluster for SQL Server Storage.
https://blogs.msdn.microsoft.com/dfurman/2014/04/27/using-storage-spaces-on-an-azure-vm-cluster-for-sql-server-storage/
This article talks about performance for premium storage and how you can get higher IOPS and/or thoughput. For instance, you can stripe multiple disks to increase performance. Take note to the multiple cautions about using a VM size that is larger than the total IOPS you plan to attach. You will always get throttled to which ever is the smallest, IOPS for disk or IOPS for the VM type.
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/premium-storage-performance
This briefly talks about performance for S2D in regards to caching and tiering.
https://docs.microsoft.com/en-us/windows-server/administration/performance-tuning/subsystem/storage-spaces-direct/
Hardware requirements for S2D.
https://docs.microsoft.com/en-us/windows-server/storage/storage-spaces/storage-spaces-direct-hardware-requirements
Thank you for such a detailed response.