Achieving High Availability with Azure SQL Server on VM: Choosing the Best Solution for Your Needs

Microsoft

May 08, 2023

Introduction:

Achieving high availability is crucial for businesses that rely on their SQL Server databases. With SQL Server on Azure virtual machines, there are two popular deployment architectures to consider: SQL Server Always-On Availability Groups (AG) and SQL Server Always-On Failover Clustering (FCI). However, choosing the right solution for your specific scenario requires careful consideration of various factors. In this article, we will provide a comprehensive guide to help you decide which approach to use, especially when deployed in the Azure environment.

Main Considerations:

Managed instance plans: If you plan to use Managed Instance (MI) in the future, AG might be a better option since you can add MI as an additional replica, making migration easier. While FCI can also work with MI, it changes the type of solution and requires additional steps.
Please read about Managed Instance link feature for more details.
Number of databases: FCI is an ideal solution when dealing with multiple databases (at least 10), as the configuration is per instance and not per database like in AG. However, AG allows you to prioritize critical databases for higher availability and performance.
Having more than 100 DBs in the same database server is not recommended when using AG.
Recovery models: If some of your databases require the simple recovery model, AG is not a feasible option, making FCI the only choice. AG requires all databases to use the full recovery model.
Multi-Subnet: For both AG and FCI is the recommended to deploy your SQL Server VMs to multiple subnets on the same virtual network whenever possible to avoid the dependency on an Azure Load Balancer or a distributed network name (DNN) to route traffic to your availability group listener.
AG can be deployed on multiple virtual network with network peering.
You can find more info in the following blog and video.
Zone and Region Redundancy: FCI requires shared storage support, so if you need to deploy on a multi-zone, you must check the supportability of Zone Redundant Storage (ZRS) on your data center. Note that ZRS for managed disks has limitations, such as DC support.
FCI cannot be deployed in multiple regions, while AG can be configured for cross-region replication.
Team Skillset: FCI is primarily an infrastructure solution, while AG is a database solution. You should verify that your team has the necessary skills to support the chosen solution.
Consumption fees and Licensing: The costs are similar for both FCI and AG, but you should check if Azure Hybrid Benefit applies to your case. Additionally, you might use enterprise or standard editions, with significant feature differences.
Note, the pricing cost of the storage might be different when using ZRS.

Conclusion:

Choosing between SQL Server Always-On Availability Groups and SQL Server Always-On Failover Clustering depends on various parameters, and each solution has its strengths and weaknesses. By considering the factors outlined in this article, you can make an informed decision and choose the best solution for your business needs.

Appendix – Read links for FCI and AG:

Always-On Failover Cluster with Shared storage cluster (FCI):