Azure SQL failover groups: pitfalls and lessons learned
Database left out of the group, undersized secondary pool, wrong listener, lagging inventory, primary-only jobs and differences with SQL Managed Instance.
Failover groups work very well as long as everything around them is consistent. The incidents I have met almost always come from a detail around the group, not from the mechanism itself. Here are my pitfalls and what I put in place so I do not live them twice.
flowchart TD
A["New database created"] --> B{"Added to the group?"}
B -- "no" --> C["No regional protection"]
B -- "yes" --> D{"Secondary pool large enough?"}
D -- "no" --> E["Seeding fails or lag grows"]
D -- "yes" --> F{"Connection through the listener?"}
F -- "no" --> G["Application broken after failover"]
F -- "yes" --> H["Healthy group"]
1. A new database left out of the group
A group does not automatically include databases created afterwards. Result: after a failover the database is missing on the secondary side, or worse, you find out during a real outage. Fix: adding to the group is part of the database creation script, and a periodic check compares the server's databases with the group's (see the monitoring article).
2. Secondary pool too small
The secondary of a pooled database is created in the pool with the same name, which needs enough free capacity. Too small, it refuses databases (maximum databases per pool) or saturates at failover, when it suddenly carries production. Fix: the secondary pool has the same size as the primary, and the add script checks free room before adding. On big pools of databases, rising lag is the first symptom.
3. Wrong listener in a connection string
A string pointing at the physical server name works every day... until the failover. After it, it lands on a read-only database. The reverse trap exists too: using the secondary listener for a process that must write. Fix: only listener names are allowed in configuration; a config review looks for hard-coded server names. And I remember that a listener only says "who is primary right now": to administer, I resolve the group through the Azure API.
4. An inventory lagging behind reality
My role inventory is refreshed by a collection. Between two collections, a failover makes the inventory wrong: a process reading it targets the wrong server. Same for a deleted server that stays active in the inventory until the nightly collection ends. Fix: a freshness guard (I refuse a row that is too old for a write), frequent role refreshes, and, for any sensitive action, a direct check against the Azure API. Another detail: two databases can share a name on two servers; filter on the resource id, never on the name alone.
5. Jobs that must only run on the primary
Index maintenance, statistics, purges: on a read-only secondary these jobs fail, or worse, run on the wrong copy after a failover. Fix: every process starts by testing that it is on the writable copy.
IF DATABASEPROPERTYEX(DB_NAME(), 'Updateability') <> 'READ_WRITE'
THROW 50001, 'Not the writable copy: skipping.', 1;
Exports and reports, on the other hand, benefit from targeting the readable secondary, which relieves the primary.
6. SQL Managed Instance: what changes
| Topic | SQL Database | SQL Managed Instance |
|---|---|---|
| Group scope | part of the server's databases | all user databases of the instance |
| Groups per resource | several per server | only one per instance |
| Partial failover | yes (separate groups) | no |
| Listener | fog.database.windows.net |
fog.<zone>.database.windows.net (shared DNS zone) |
| Logins, jobs, linked servers | server-level logins | system databases not replicated: recreate them, with the same SIDs |
| Network | firewall | connectivity between subnets (global peering) |
Instance-level objects (logins, agent jobs, linked servers) do not follow: a linked server whose definition differs between the two instances breaks after a failover. Listeners cannot be reached through the public endpoint.
Lessons learned
- Almost every group problem comes from the periphery: application configuration, inventory, jobs, pool.
- I test failovers regularly: that is where these oversights surface, not in production.
- Automating the add-to-group step and its verification costs less than a single incident.