Business apps require constant availability and no downtime. SQL Server customers can utilize Always On Availability Groups (AGs) for high availability, disaster recovery, and read-scale performance solutions.

SQL Server 2012 introduced AGs, which build on Windows Server Failover Clustering (WSFC) to provide continuous access to mission-critical databases. Not only do AGs allow an enterprise to achieve uptime during unforeseen outages, but they also allow the enterprise to scale and keep performance while offloading read workloads.

This valuable document will help you understand Always On Availability Groups, their benefits, and how to configure, monitor, and maintain them.

1. What are Always On Availability Groups?

• Always On Availability Groups allow you to group a set of user databases, and replicate the databases across many servers (replicas). Each replica can be one of:
• Primary Replica: accepts read/write requests.
• Secondary Replica(s): accept read requests that can be used for failover or read-scale offloading.

When Primary goes down, SQL Server automatically (or manually) will failover to the secondary replica and keep applications running without interruption.

Key Features include:

• automatic or manual failover between replicas
• up to 8 secondary replicas
• read-scale using read-only routing
• backups run against secondary replicas
• high availability and disaster recovery construction in WSFC

2. Advantages of Always-On Availability Groups

• High Availability – Automatic failover capability will greatly reduce downtime, keeping hosts online.
• Disaster Recovery – True geo-redundancy can be achieved by placing replicas in different datacenters or in different cloud regions.
• Scalability – Secondary replicas can serve only reports and read-only workloads.
• Better Performance – Offload report queries and all backups to secondary replicas and relieve the primary of some the workload.
• Business Continuity – If an entire datacenter, host, OS, or network goes down, critical systems remain operational.

3. Pre-requisites & Requirements

Before you implement Always-On AG’s, you will need:

• SQL Server Enterprise Edition (Basic AG’s are supported in the Standard Edition).
• A Windows Server Failover Cluster (WSFC) that has been completed and is fully functional.
• A proper DNS Name and Quorum configured for the cluster to be reliable.
• All participating databases need to be in FULL recovery model with recent backups.

4. Configure Always-On Availability Groups

• Enable the Always-On AG feature using SQL Server Configuration Manager.
• Establish a Windows Server Failover Cluster with available nodes.
• Ensure that a back up has been taken of the database/s and is in FULL recovery mode.
• Create the Availability Group from SQL Server Management Studio (SSMS) or using T-SQL.
• Add Secondary Replicas and choose the preferred synchronization option:
• Synchronous commit for zero data loss and high availability.
• Asynchronous commit for disaster recovery with moderate data loss based on the amount of data since the last backup.
• Create an Availability Group Listener to simplify application connection strings.

5. Failover Modes within Always On AGs

• Automatic Failover – Streamlined and instant failover with synchronous commit and automatic failover targets.
• Manual Failover – For example, during scheduled maintenance or controlled switchovers.
• Asynchronous Commit – A particularly suitable option for disaster recovery scenarios which occur over long distances, and for which certain data losses are acceptable.

6. Monitoring and Maintenance

• For AGs to operate smoothly, ongoing vigilance must be in place:
• Use SSMS AG dashboards to observe synchronization and failover readiness for the replicas.
• Use alerts for synchronization failures or cluster node failures.
• Perform periodic failover drills to ensure organizations are prepared.
• Automate secondary replica backups to distribute workload from the primary replica.

7. Always On Availability Group Best Practices

• Separate replicas across availability zones or datacenters.
• Utilize all available bandwidth for synchronous replication.
• Ensure that all replicas have the same SQL Server build, patch version, and edition.
• Leverage read-only routing to use resources more efficiently.
• Periodically test disaster recovery playbooks to ensure that organizations can continue.

8. How Empirical Edge Can Help

Empirical Edge can assist you with the design, implementation and optimization for SQL Server solutions best suited to your business requirements and goals. If you are thinking about a new Always On deployment, a flip from legacy clustering and HA/DR technologies or you need to manage and monitor your environment, our SQL Server professionals provide zero downtime, compliant and scalable database solutions.

Our complete range of Microsoft SQL Server Development & Management Services includes:

• SQL Server installations, upgrades and performance tuning,
• High Availability & Disaster Recovery (HADR) implementations,
• Always On AGs deployments and optimization,
• Monitoring & proactive maintenance on SQL Servers,
• Database migration with compliance & security scope.

With us, you can be assured your SQL Server solution is secure, resilient and poised for growth.

Conclusion

SQL Server Always On Availability Groups are more than just a basic High Availability feature – they are an organization-wide framework for Disaster Recovery and scalability success. The successful implementation of AGs will allow the organization to keep their critical workloads protected to protect the organization from disruption, controlling downtime and providing consistency in performance across workloads and regions.

When working with the right partner, your organization can utilize the full potential of Always On AGs. By combining proven implementations with a deep understanding of SQL Server, Empirical Edge offers organizations with SQL Databases that are always on, always optimal, and future freshwater.