DR Scenario: SQL Failover Group Failover¶

The SQL-specific procedure behind a region failover: promote the secondary server of a geo-redundant SQL failover group to primary. This is the database half of scenario-region-failover.md, isolated so it can be run on its own (a SQL-only incident — corruption confined to the primary, a primary server outage, or a planned maintenance cutover) without moving the whole workload. Built on the Cross-Region Replication Module (L2). Copy per workload and fill every <PLACEHOLDER>.

Field	Value
Workload	`<WORKLOAD>`
Failover group	`<FG_NAME>`
Primary server	`<primary-sql-server>` (region `<PRIMARY_REGION>`)
Partner server	`<partner-sql-server>` (region `<SECONDARY_REGION>`)
Env	`<dev / staging / prod>`

Objective¶

Make <partner-sql-server> the read-write primary for the failover group <FG_NAME>, with the application reconnecting transparently via the failover group's read-write listener — no connection-string change. Then re-establish the old primary as a healthy secondary (reseed) and, when appropriate, fail back.

RTO / RPO target¶

RTO: <≤ 1h> — a failover-group switch is seconds-to-minutes at the database layer; the budget covers detection, decision, and app reconnection.
RPO:
Planned failover (set-primary without --allow-data-loss): zero — Azure synchronizes before switching.
Forced failover (--allow-data-loss): bounded by the geo-replication lag at the moment of failover (typically seconds; confirm before deciding).

Planned vs forced — pick deliberately¶

	Planned (`set-primary`)	Forced (`set-primary --allow-data-loss`)
Use when	Primary is reachable (planned cutover, maintenance, rolling back a bad primary)	Primary is unreachable and you cannot wait for the grace period
Data loss	None — synchronizes first	Up to the replication lag
Where you run it	Against the secondary server you want to promote	Against the secondary server you want to promote
Grace period	N/A (manual, immediate)	N/A — `--allow-data-loss` overrides the automatic grace

The env's automatic failover posture (L2: dev Manual / staging Automatic 60m / prod Automatic 120m) governs unattended failover only. A human running the commands below performs an explicit failover regardless of that grace window.

Preconditions¶

The L2 SQL failover group exists; <partner-sql-server> shows as a healthy Secondary replica.
You understand which server is currently primary (don't assume).
App uses the failover-group listener (<FG_NAME>.database.windows.net for read-write, <FG_NAME>.secondary.database.windows.net for read-only), not a hard-coded server FQDN. If it hard-codes a server, a failover will not reroute it — fix the connection string first.
You have the IDs/names:

PRIMARY_RG="<rg-primary>"
DR_RG="<rg-secondary>"
FG_NAME="<sql-failover-group-name>"
PRIMARY_SQL_SERVER="<primary-sql-server>"
PARTNER_SQL_SERVER="<partner-sql-server>"
SUB="<subscription-id>"
az account set --subscription "$SUB"

Recovery steps¶

Declare & start the clock. Record the declaration time per the DR plan (dr-plan-template.md §5). For a planned cutover, open a change window.

Confirm current roles and replication health. The replicationLag (when exposed) bounds your data loss for a forced failover:

az sql failover-group show \
  --name "$FG_NAME" \
  --server "$PRIMARY_SQL_SERVER" \
  --resource-group "$PRIMARY_RG" \
  --query "{role:replicationRole, state:replicationState, partners:partnerServers[].replicationRole}"

Decide planned vs forced using the table above. If the primary is reachable, always prefer planned (zero data loss).
Fail over — run against the server you are promoting ($PARTNER_SQL_SERVER):

Planned (zero data loss):

az sql failover-group set-primary \
  --name "$FG_NAME" \
  --server "$PARTNER_SQL_SERVER" \
  --resource-group "$DR_RG"

Forced (primary down; accept replication-lag loss):
```
az sql failover-group set-primary \
  --name "$FG_NAME" \
  --server "$PARTNER_SQL_SERVER" \
  --resource-group "$DR_RG" \
  --allow-data-loss
```
PowerShell equivalent: Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName $DR_RG -ServerName $PARTNER_SQL_SERVER -FailoverGroupName $FG_NAME [-AllowDataLoss].

Verify the new primary. The promoted server must report Primary:

az sql failover-group show \
  --name "$FG_NAME" --server "$PARTNER_SQL_SERVER" \
  --resource-group "$DR_RG" \
  --query "{role:replicationRole, state:replicationState}"

Reconnect the application. If the app caches DNS or pooled connections to the old primary, recycle it so it re-resolves the listener:
```
kubectl rollout restart deployment/<app> -n <namespace>
kubectl rollout status  deployment/<app> -n <namespace>
```
Confirm the old primary reseeds as a secondary (when it returns). Azure re-establishes replication automatically once it is reachable; verify rather than assume.
Update status per the comms plan (dr-plan-template.md §6).

Validation checklist¶

<partner-sql-server> reports replicationRole: Primary, replicationState: ... healthy.
Application can write (not just read) via the read-write listener.
Read-only listener (*.secondary.database.windows.net) still serves reads if the app uses it.
No data-integrity errors in the app's critical transactions.
For a forced failover: replication lag at failover time recorded as the realized RPO.
Old primary, once reachable, shows as a healthy Secondary (reseed complete).
Measured RTO recorded vs target.

Fail-back / cleanup¶

Fail back as a planned failover during a window so it is zero data loss.

Confirm the original primary region is healthy and <primary-sql-server> has re-synced as a Secondary.

Planned failover back (no --allow-data-loss), run against the original primary:

az sql failover-group set-primary \
  --name "$FG_NAME" --server "$PRIMARY_SQL_SERVER" \
  --resource-group "$PRIMARY_RG"

Recycle the app again so it re-pins the read-write listener to the original primary.
Re-run the validation checklist; file the post-DR review (dr-plan-template.md §8).

Sign-off¶

Field	Value
DR event / drill ref	`<id>`
Scenario	SQL failover-group failover
Direction	`<failover / fail-back>`
Failover type	`<planned / forced (data-loss accepted)>`
Date / time (UTC)	`<YYYY-MM-DD HH:MM>`
Operator	`<NAME>`
Measured RTO / RPO	`<actual>` / `<actual>`
Validation passed	☐ yes ☐ no — notes: `<…>`
Reviewed by (IC)	`<NAME>`