Always On Availability Groups

Always On Availability Groups (AGs) is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Availability Groups enable you to run multiple databases on a primary server (the primary replica) and maintain multiple synchronized copies of those databases on secondary servers (the secondary replicas).

Key Concepts

• Availability Replica: A hosting server instance that hosts an instance of the SQL Server Database Engine and contains a set of availability databases.
• Availability Database: A database that is part of an availability group and is fully tracked and managed by the availability group.
• Availability Group Listener: A Windows Server Failover Clustering (WSFC) resource that provides a SQL Server virtual network name (VNN), DNS name, and port number that clients connect to.
• Failover: The process of transferring the primary role from a primary replica to a secondary replica.

Benefits

• High Availability: Minimizes downtime for critical databases by providing automatic or manual failover to secondary replicas.
• Disaster Recovery: Protects data from site-level failures by maintaining copies of databases in different geographical locations.
• Read-Scale Workloads: Offloads read-only workloads to secondary replicas, improving the performance of the primary replica.
• Backup Offloading: Perform backups on secondary replicas without impacting the performance of the primary.

Architecture

An Availability Group consists of a primary replica and one to eight secondary replicas. Replicas can be configured for synchronous or asynchronous data movement.

Replica Modes

• Synchronous-commit mode: Transactions are committed on both the primary and secondary replicas before being acknowledged to the client. This provides higher data protection but may introduce higher latency.
• Asynchronous-commit mode: Transactions are committed on the primary replica and sent to the secondary replica, but without waiting for confirmation. This offers lower latency but carries a higher risk of data loss in a failover scenario.

Failover Modes

• Automatic Failover: Supported only for synchronous-commit mode with automatic failover configured. The failover is performed without any data loss.
• Manual Failover: Requires an administrator to initiate the failover. Can be performed with or without data loss, depending on the replica mode.

Configuring Availability Groups

The configuration of Availability Groups involves several steps:

1. Ensure your SQL Server instances are properly configured for WSFC.
2. Create a new Availability Group in SQL Server Management Studio (SSMS) or using Transact-SQL.
3. Add databases to the Availability Group.
4. Configure primary and secondary replicas, including replica modes and failover settings.
5. Create an Availability Group Listener for client connectivity.

Further Reading

• Microsoft Docs: Always On Availability Groups
• SQLServerCentral: Always On Availability Groups Explained

CREATE AVAILABILITY GROUP MyAG
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    DB_FAILOVER = ON
)
FOR REPLICA ON
    'Server01' WITH (
        ENDPOINT_URL = 'TCP://Server01.example.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        ROLE = PRIMARY
    ),
    'Server02' WITH (
        ENDPOINT_URL = 'TCP://Server02.example.com:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        ROLE = SECONDARY
    );