Planned vs forced failover of an Azure SQL failover group: a runbook
Failover runbook, checks before and after, application impact, failback, finding the current primary from PowerShell or T-SQL, and a drill checklist.
Two operations are called "failover" and they have little in common. A planned failover synchronizes everything before switching roles: zero data loss. A forced failover switches roles immediately without waiting for replication: data loss is possible, so it is reserved for real outages. Here is the runbook I follow, with checks before and after (for a first exercise on plain geo-replication, see my article on the failover drill).
| Planned | Forced | |
|---|---|---|
| Primary reachable | yes (required) | no, or outage in progress |
| Data loss | none | possible |
| Use | drill, workload move, failback | regional outage |
| Cmdlet | Switch-AzSqlDatabaseFailoverGroup |
same with -AllowDataLoss |
Both are issued against the secondary server of the group (the one that is about to become primary).
sequenceDiagram
participant DBA
participant ARM as "Azure API"
participant P as "Primary server A"
participant S as "Secondary server B"
participant App as "Application via listener"
DBA->>P: Checks before: state, lag, pool B
DBA->>ARM: Switch-AzSqlDatabaseFailoverGroup on B
ARM->>P: Full synchronization
ARM->>S: Promote to primary
ARM->>ARM: Update listener DNS
App->>S: New connections after 30 s TTL
DBA->>S: Checks after: roles, writes, jobs
Before
- The group is synchronizing (no seeding in progress) and replication lag is low.
- The secondary pool is sized like the primary: it is about to carry production.
- Logins, users and firewall rules exist on B; application identities connect.
- The application really uses the listener (see the pitfalls article).
- Jobs and batch processes are paused or know how to stop cleanly.
Planned
Get-AzSqlDatabaseFailoverGroup -ResourceGroupName rg-dba-prd -ServerName sql-contoso-prd-b `
-FailoverGroupName fog-contoso-app-01 |
Switch-AzSqlDatabaseFailoverGroup
Without -AllowDataLoss the command waits until both roles are swapped and, at worst, fails and rolls back: nothing is lost.
Forced
Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName rg-dba-prd -ServerName sql-contoso-prd-b `
-FailoverGroupName fog-contoso-app-01 -AllowDataLoss
With -AllowDataLoss the command only waits for the new primary to take its role. When the old primary comes back, its databases reconnect as secondaries. Before forcing, I note the last known lag value: it is the estimate of what will be lost.
After
-- Connected through the listener: am I on the right server, writable?
SELECT @@SERVERNAME AS server_name,
DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS mode; -- READ_WRITE expected
Finding the current primary without ambiguity:
(Get-AzSqlDatabaseFailoverGroup -ResourceGroupName rg-dba-prd -ServerName sql-contoso-prd-a `
-FailoverGroupName fog-contoso-app-01) | Select-Object ReplicationRole, PartnerServerName
-- In a database: my role and my partner
SELECT role_desc, partner_server, replication_state_desc, replication_lag_sec
FROM sys.dm_geo_replication_link_status;
Then: check that writes succeed, that jobs are re-enabled only on the new primary, that the role inventory is refreshed, and that lag falls back.
Application impact
DNS switches with a 30-second TTL, but connections that are already open still point to the old primary: they fail or become read-only. Applications must reconnect (retry with backoff) and never cache an IP address. I expect a short interruption, not an outage.
Failback
Once the incident is over, I fail back with a planned failover, never a forced one. On SQL Managed Instance a full backup is automatically taken on the new primary after a failover, so a failback right afterwards can be slower.
Drill checklist
- Announced window, business informed, jobs paused.
- Snapshot before: roles, lag, pool sizes.
- Planned failover, measure total duration.
- Application tests on the listener, read and write.
- Planned failback, same checks.
- Write-up: duration, errors, what was forgotten.
Lessons learned
- A drill mostly reveals what you forgot: a login with a mismatched SID, a job running on the wrong server, an undersized pool.
- I drill with a planned failover; a forced one "just to test" has no guardrail.