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
- Read-write:
<fog>.database.windows.net, a CNAME pointing to the current primary. - Read-only:
<fog>.secondary.database.windows.net, a CNAME pointing to the secondary.
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
- Databases in the group switch roles together (all secondaries become primaries).
- The listeners are redirected.
- Do not move: server-level logins, server firewall rules, server properties, surrounding resources (applications, storage, Key Vault). All of that must exist on both sides (see the article on building a 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
- The secondary cannot be in the same region as the primary; paired regions perform better.
- The number of databases affects the duration of a planned failover: databases are prepared in batches. Several moderate groups beat one huge group (each group fails over independently).
- Pools with 8 vCores or fewer (or 800 DTUs) holding more than 250 databases can see replication lag grow and planned failovers get longer.
- Initial seeding can reach about 500 GB/hour on Azure SQL Database; it is the longest and most expensive phase.
- Secondaries created by the group do not inherit zone redundancy (except Hyperscale): enable it afterwards.
Lessons learned
- I always put the listener in connection strings, never a physical server name: that is the whole point of the group.
- I prefer many reasonably sized groups per server pair: an outage or a drill only touches a subset.
- The listener only tells you who is primary right now; to administer, I go through the Azure API (
Get-AzSqlDatabaseFailoverGroup), not DNS. - A group is an abstraction: monitoring is still per database (see the monitoring article).