Availability Groups: What’s Right for Your Business?
I’ve had several companies inquire about SQL Server’s built-in high availability options over the years. This conversation usually indicates a need to ensure that their database systems remain operational and accessible with minimal downtime.
But why now? They’ve been in business for years and are just now interested in HA/DR. What changed?
Reasons vary, but the following are very common:
- A new cyber insurance policy requiring a HA/DR or business continuity plan.
- A need for HA/DR capability in order to create or develop a new revenue stream.
- General curiosity due to SQL Server marketing materials.
- A new client or business group who has asked about HA/DR.
- Concerns raised during a recent audit.
- Recent outage that impacted revenue.
SQL Server has several options to choose from when it comes to HA/DR. These include:
- Log Shipping: No automatic failover capabilities. Available on SQL Server Standard and Enterprise editions.
- Database Mirroring: Deprecated, don’t use it in new environments.
- Failover Cluster Instances: Available on SQL Server Standard and Enterprise editions.
- Availability Groups
Since you’re here to learn more about availability groups, I’ll introduce you to the two main types.
Availability Groups
Availability Groups were introduced in SQL Server 2012 Enterprise Edition. They provide advanced high availability and disaster recovery capabilities.
- Edition Support: Available in SQL Server 2012 Enterprise Edition and later. FYI: Official support for SQL Server 2012 ended in 2022.
- Number of Replicas: SQL Server 2022 specs – One primary and up to 8 secondary replicas (under asynchronous-commit mode or up to 5 can run under synchronous-commit mode).
- Supports readable secondaries: Yes
- Failover: Supports automatic failover for synchronous-commit replicas and manual failover for asynchronous-commit replicas.
- Use Cases:
- Ideal for mission-critical applications requiring high availability and disaster recovery across multiple databases.
- Suitable for enterprises needing advanced features like read-only secondary replicas for offloading read workloads.
Requirements:
- Same OS version on all nodes.
- Proper network infrastructure to support data replication with minimal latency.
- Windows Server Failover Clustering (WSFC). There are some exceptions to this that I’ll cover in a later post.
- SQL Server 2019 Enterprise Edition or later. Available with earlier versions, but those are out of mainstream support. I wouldn’t want you building a new AG on unsupported versions. In fact, that would almost certainly raise flags in other areas of your cyber insurance policy.
Basic Availability Groups
Up until SQL Server 2016, AGs were only available in Enterprise Edition. However, in 2016, Microsoft introduced us to Basic Availability Groups. Basic AGs provide an introduction to high availability and disaster recovery solutions for a single database.
- Edition Support: Available in SQL Server 2016 Standard Edition and later.
- Number of Replicas: Supports two replicas (one primary and one secondary).
- Supports readable secondaries: No. See the limitations here.
- Failover: Supports automatic failover, but only in a single-database environment.
- Use Cases:
- Ideal for small businesses or environments where budget constraints prevent the use of Enterprise Edition.
- Suitable for applications that require minimal HA/DR capabilities.
Requirements:
- Same OS version on both nodes.
- SQL Server 2016 Standard Edition or later.
- Windows Server Failover Clustering (WSFC)
- Proper network infrastructure to support data replication with minimal latency.
Which should you choose?
Getting AGs right takes practice. When done wrong, performance and availability can both suffer. Budgets can be destroyed by licenses. Understanding your options, including their requirements and differences, can help you choose the right solution for your environment. Whether you’re driven by audits, insurance policies, or those uncomfortable “could not open a connection to SQL Server” errors, investing in HA/DR solutions is crucial for maintaining business continuity and minimizing downtime.
So, which would I choose? If I had the option to use shared storage, I’d opt for building a failover clustered instance instead of using a Basic AG. FCIs support failover of multiple databases and can be used with Standard Edition. However, if shared storage isn’t an option and you need automatic failover capabilities for a single database or if you’re working in a hybrid environment (Azure and on-premises), Basic AGs may be a good choice for you.
Have questions about HA/DR? Here are two ways we can help.
- Schedule a call with us to connect with our consultants. We’ve helped numerous clients just like you meet their uptime objectives.
- Consider SEROShield, our DBA team as a service. Not only will we help build the AG, but we’ll also support it. It’ll be like driving around in a new car (you know, the one with the new car smell) that you know is covered by the best possible insurance.
Next in this series
There are a few lesser-known availability group types that we didn’t discuss today but will in upcoming posts: Contained AGs, Distributed AGs, and Domain-independent AGs.
Follow us on LinkedIn to get notified when we release new blog posts.