LLI Wiki — My Digital Knowledge Base

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

  1. The group is synchronizing (no seeding in progress) and replication lag is low.
  2. The secondary pool is sized like the primary: it is about to carry production.
  3. Logins, users and firewall rules exist on B; application identities connect.
  4. The application really uses the listener (see the pitfalls article).
  5. 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

Lessons learned

Mastodon LLIMastodon SocialFosstodon