Always On Availability Groups

Always On Availability Groups is Microsoft SQL Server’s enterprise-grade high-availability and
disaster-recovery (HADR)
solution, introduced in SQL Server 2012 and enhanced in later versions. It allows a set of user databases—known as an
availability group—to fail over together as a single entity to one or more secondary replicas. Each replica can be
configured for high availability, disaster recovery, or read-only workloads.

At its core, an availability group consists of:

  • A primary replica: where read-write traffic is directed.
  • Up to eight secondary replicas: these can serve read-only queries or act as failover targets.
  • A Windows Server Failover Clustering (WSFC) cluster to manage automatic failover policies and health checks.

Key benefits include:

  • High availability: automatic or manual failover ensures minimal downtime.
  • Disaster recovery: secondary replicas can reside in remote datacenters.
  • Read-scale out: offload read-only workloads like reporting to secondary replicas, reducing load
    on the primary.
  • Backup flexibility: backups can run on secondary replicas without affecting primary
    performance.

A few important concepts:

  • Synchronization modes: Synchronous-commit ensures zero data loss but may raise latency;
    asynchronous-commit trades off
    potential data loss for higher performance across distant networks.
  • Readability settings: You can specify whether a secondary is readable or not, and whether
    queries on it are committed or
    uncommitted.
  • Listener: a virtual network name (and IP) that client applications use to connect, without
    knowing which replica is primary—facilitating seamless failover.

Implementation involves careful planning of networking, replicas, disk resources, failover policies, and backup
strategies. Many DBAs use Availability Groups to support 99.99% uptime SLAs while also enabling reporting, analytics,
and backup offloading. Since their introduction, they’ve largely superseded database mirroring and log shipping in
mission-critical environments.