This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
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):
- Main links are:
- Failover cluster instances - SQL Server on Azure VMs | Microsoft Learn
- Create an FCI with Azure shared disks - SQL Server on Azure VMs | Microsoft Learn
- Migrate failover cluster instance - SQL Server on Azure VMs | Microsoft Learn
- If you need MSDTC – you need Windows Server 2019 or later.
- Some more links:
- This is a great guide
sql-docs/failover-cluster-instance-azure-shared-disks-manually-configure.md at live · MicrosoftDocs/sql-docs (github.com) - HADR Best practices
https://github.com/MicrosoftDocs/sql-docs/blob/live/azure-sql/virtual-machines/windows/hadr-cluster-best-practices.md - Build Guide
https://github.com/MicrosoftDocs/sql-docs/blob/live/azure-sql/virtual-machines/windows/availability-group-manually-configure-prerequisites-tutorial-multi-subnet.md - SQL Performance guide
https://github.com/MicrosoftDocs/sql-docs/blob/live/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist.md - [Video] Run clustered applications on Azure with shared disks
- SQL FCI with Load balancer
Configure an Azure load balancer for an FCI VNN - SQL Server on Azure VMs
- This is a great guide
Always-On availability group (AG):
- Main links are:
- Overview of SQL Server Always On availability groups - SQL Server on Azure VMs | Microsoft Learn
- Use Azure quickstart templates to configure an availability group for SQL Server on Azure VM
- Note that managing metadata objects (users, logins, permissions, SQL Agent jobs etc.) at the availability group level in addition to the instance level starting with SQL server 2022 with the contained availability group feature
- Some more links:
- Multi Zone configuration
Tutorial: Configure an availability group across regions - SQL Server on Azure VMs | Microsoft Learn
- Multi subnet configuration
Tutorial: Configure a multi-subnet availability group across regions - SQL Server on Azure VMs | Microsoft Learn
- Multi-region N-tier application – architecture guidance
Multi-region N-tier application - Azure Architecture Center | Microsoft Learn
- Multi Zone configuration