LLI Wiki — My Digital Knowledge Base

Azure SQL failover groups explained for a DBA

Read-write and read-only listeners, failover policy, grace period, what moves with the group, elastic pools on both sides and limits.

A geo-replicated Azure SQL database protects you from a regional outage, but managing dozens of replication links one by one quickly becomes unmanageable. Failover groups (FOG) add a declarative layer on top of active geo-replication: a stable DNS name, a failover policy and a set of databases that move together. Here is what I need to understand, as a DBA, before running them in production. (For basic replication and a first failover exercise, see my article on geo-replication and the failover drill: here I focus on the group itself.)

The idea in one sentence

A FOG is a named group of databases from one logical server, replicated to a logical server in another region. The group is declared on the primary server, its name must be unique within database.windows.net, and one server can host several groups.

flowchart LR
    App["Application"] --> RW["Read-write listener: fog.database.windows.net"]
    Rpt["Reporting"] --> RO["Read-only listener: fog.secondary.database.windows.net"]
    RW --> SrvA
    RO --> SrvB
    subgraph R1["Region A"]
        SrvA["Primary logical server"] --> PoolA["Elastic pool pool-elp-01"]
        PoolA --> DbA["AppDb primary"]
    end
    subgraph R2["Region B"]
        SrvB["Secondary logical server"] --> PoolB["Elastic pool pool-elp-01"]
        PoolB --> DbB["AppDb secondary"]
    end
    DbA -- "asynchronous replication" --> DbB

The two listeners

After a failover only DNS changes. The TTL of these records is 30 seconds: new connections follow quickly, but connections that are already open must be dropped and recreated. For the read-only listener, failover to the primary is disabled by default (AllowReadOnlyFailoverToPrimary): if the secondary is down, read-only sessions have no fallback, which protects primary performance.

Failover policy and grace period

Policy CLI/PowerShell value Who decides
Customer managed (recommended) Manual You, when you see the outage
Microsoft managed Automatic Microsoft, for all groups of the affected region, after the grace period

The grace period (GracePeriodWithDataLossHours) cannot be set below 1 hour. I only consider the automatic policy when the application can tolerate at least an hour of downtime and an unknown amount of data loss: the exact moment of the failover is not under your control and ignores synchronization state. In practice I stay on Manual.

What moves with the group

Elastic pools on both sides

If the primary database is in a pool, the secondary is created in a pool with the same name on the secondary server. That pool must exist and have enough free capacity before you add the database. So I size the secondary pool like the primary one, and I check it every time a database is added.

Limits worth knowing

Lessons learned

Mastodon LLIMastodon SocialFosstodon