Azure SQL: qualify a failover group before a forced failover

A region is responding poorly, Azure SQL connections are timing out, and the failover group exists for exactly this scenario. The immediate temptation is to promote the secondary with --allow-data-loss. That command may shorten the outage, but it also turns an availability incident into an irreversible data decision.

The running case is a transactional API with several databases in one failover group. The primary is degraded without being completely unreachable. The team must establish replication state, prove that the application uses the listener, validate the secondary region, and choose between waiting, planned failover, and forced failover. The runbook ends with a documented decision and a failback path, not an isolated portal action.

Freeze the decision contract

Before any write operation, record the UTC impact start, affected databases, accepted RPO, and the point at which continued downtime costs more than potential data loss.

incident_id: INC-2026-0916-sql
failover_group: fog-orders-prod
primary_server: sql-orders-weu
secondary_server: sql-orders-neu
read_write_listener: fog-orders-prod.database.windows.net
affected_databases:
- orders
- payments
rpo_max_seconds: 30
rto_max_minutes: 15
write_freeze_requested: true
forced_failover_approvers:
- incident-commander
- data-owner
rollback: planned-failback-after-primary-recovery-and-validation

Do not restate an RPO as “a few seconds.” Compare it with observed replication_lag_sec and with the business transactions created inside that window. If the primary still accepts writes, stop them cleanly when possible: each new transaction expands the divergence envelope.

Prove the topology and current role

Query the group from the server the team believes is primary. Do not infer the role from a resource name, an old diagram, or the region that is normally active.

PRIMARY_RG="rg-data-weu"
PRIMARY_SERVER="sql-orders-weu"
SECONDARY_RG="rg-data-neu"
SECONDARY_SERVER="sql-orders-neu"
FOG="fog-orders-prod"

az sql failover-group show \
--resource-group "$PRIMARY_RG" \
--server "$PRIMARY_SERVER" \
--name "$FOG" \
--query "{role:replicationRole,databases:databases,partners:partnerServers,readWrite:readWriteEndpoint,readOnly:readOnlyEndpoint}" \
--output json

az sql failover-group show \
--resource-group "$SECONDARY_RG" \
--server "$SECONDARY_SERVER" \
--name "$FOG" \
--query "{role:replicationRole,databases:databases,partners:partnerServers}" \
--output json

Both views must describe the same group and database set with opposite roles. A missing database, unexpected partner, or group visible from only one side is a topology incident to qualify before failover.

Measure replication database by database

A failover group changes all member databases together, but their lag can differ. Start with Azure Resource Manager state, then measure the link inside every database that remains reachable.

for DB in orders payments; do
az sql db replica list-links \
  --resource-group "$PRIMARY_RG" \
  --server "$PRIMARY_SERVER" \
  --name "$DB" \
  --query "[].{database:databaseName,partner:partnerServer,role:role,replicationState:replicationState,percentComplete:percentComplete}" \
  --output table
done

From each primary database, the dynamic management view provides evidence closest to the data-loss risk:

SELECT
  DB_NAME() AS database_name,
  partner_server,
  partner_database,
  replication_state_desc,
  last_replication,
  replication_lag_sec
FROM sys.dm_geo_replication_link_status;

Run the query separately in every database, not in master. Retain the collection time and raw output. CATCH_UP with low lag makes planned failover plausible; SUSPENDED, an old last_replication, or an unreachable database requires an explicit loss assessment. An average hides the slowest database, so decide on the observed maximum and its business value.

Validate the target before promotion

An up-to-date replica is not yet a ready recovery region. Confirm that the secondary server and its dependencies can absorb writes: service tier and capacity, access rules, Microsoft Entra identities, managed keys, Private DNS when the path is private, quotas, and regional application components.

A Private Endpoint, when present, is only one segment of that path. The useful check is whether future active workloads can resolve and reach the listener, then authenticate to the secondary with their runtime identity without using the direct server name.

Control                     Expected proof
Server and database capacity    Same approved tier or tested DR capacity
Replication                    Every database present; worst lag recorded
Listener path                  Application uses failover-group listener
DNS                            Listener resolves from future active workloads
Identity                       Login succeeds with production runtime identity
Dependencies                   App, queue, storage and secrets ready in target region
Write protection               No uncontrolled dual writer remains active
Observability                  Errors, latency and business checks visible by region

If the application connects to sql-orders-weu.database.windows.net instead of the listener, Azure failover will not move that traffic. Correct or explicitly handle that dependency before relying on the group.

Qualify application and DNS behavior

The read-write listener keeps its name while Azure updates its target after failover. That does not guarantee that every process promptly opens a new connection. Existing pools, DNS caches, and poorly bounded retries can extend the incident or multiply ambiguous writes.

From every execution zone, capture before failover:

• the redacted connection string, especially hostname and database;
• listener resolution and observed TTL;
• active connections and the ability to renew them;
• retry policy for transient errors;
• a business identifier for the latest confirmed write.

The canary must open a new connection through the listener. A SQL session that remained open before role change is not evidence of rerouting.

Choose the failover mode

When primary and secondary can still communicate, use planned failover. The service synchronizes the databases before swapping roles, targeting a data-loss-free move.

az sql failover-group set-primary \
--resource-group "$SECONDARY_RG" \
--server "$SECONDARY_SERVER" \
--name "$FOG"

If the primary is unavailable and the RTO requires recovery, --allow-data-loss permits failover when synchronization cannot complete. Run it only after recording the worst observed lag, the last verifiable business transaction, the approvers, and expected consequences.

az sql failover-group set-primary \
--resource-group "$SECONDARY_RG" \
--server "$SECONDARY_SERVER" \
--name "$FOG" \
--allow-data-loss

The --try-planned-before-forced-failover option attempts planned failover first and forces the operation if that fails. It still authorizes data loss. Do not treat it as a harmless safer variant without an explicit stop condition.

Validate the new primary before closing

After the command, read the role again on both servers and wait until every database has changed role. Then open new connections through the listener, never through a direct hostname.

Minimum validation covers:

1. the read-write listener reaches the new primary from every active workload;
2. a read finds the expected latest transaction or precisely bounds the gap;
3. an idempotent write canary commits and can be read back;
4. connection errors, retries, and latency return to the approved range;
5. no component left in the old region continues writing directly.

Retain the canary ID, UTC timestamps, CLI output, and business result. An Azure Primary status without an application transaction is insufficient.

Decide hold, failback, or recovery

MAINTAIN THE NEW PRIMARY
Listener, canary and business checks pass
Data-loss envelope is measured and accepted
Old-region writers are stopped or fenced

HOLD AND INVESTIGATE
Roles changed but one database, dependency or workload is unhealthy
Keep writes bounded; do not stack another failover on uncertain state

PLANNED FAILBACK
Original region is stable and replication has caught up
Execute a planned role reversal during an approved window
Repeat listener, write and business validation

RESTORE OR RECONCILE
Forced failover lost confirmed transactions
Preserve both timelines and reconcile from business evidence
Never merge by replaying an unbounded queue or old writer

Rollback is not a reflexive second forced failover. Once the original region returns, wait for healthy replication, fence residual writers, and perform a planned failback. If confirmed transactions are missing, the path is restore or business reconciliation, not another role swap.

Conclusion

An Azure SQL failover group provides the role-change mechanism; it does not make the data-loss decision for the team. An operable runbook connects the actual role, worst lag per database, application listener, target capacity, and business evidence.

The decision is then explicit: wait when loss risk exceeds downtime impact, fail over in planned mode while synchronization remains possible, or force only with an accepted loss envelope. After recovery, validate with a fresh connection and a write canary, then prepare a planned failback or documented reconciliation.