Azure SQL geo-replication: setup, lag monitoring and failover drill
Failover groups, state and lag monitoring, a planned failover drill and pitfalls to avoid.
Azure SQL Database geo-replication copies a database to another server, usually in another region, to survive a regional outage. It is only worth anything if it is monitored and tested. Here is how I set it up, monitor it, rehearse a failover, and the pitfalls I met.
Two building blocks
| Block | Use |
|---|---|
| Active geo-replication | One primary and up to four readable secondaries, managed one database at a time |
| Failover group | A set of databases that fail over together, with read-write and read-only endpoints that follow the primary |
In production I use failover groups: applications connect to the group endpoint, never to the server name, and need no change after a failover.
flowchart LR
A["Application"] --> B["Group read-write endpoint"]
B --> C["Primary server (region 1)"]
C -->|"asynchronous replication"| D["Secondary server (region 2)"]
A -.->|"read-only"| E["Read-only endpoint"]
E --> D
Setting it up
Secondary side prerequisites: a server in the other region, equivalent elastic pools (same names, comparable capacity) and the network rules.
New-AzSqlDatabaseFailoverGroup -ResourceGroupName 'rg-dba-prd' `
-ServerName 'sql-contoso-prd-weu' -PartnerServerName 'sql-contoso-prd-neu' `
-FailoverGroupName 'fog-contoso-prd-01' -FailoverPolicy Manual
$db = Get-AzSqlDatabase -ResourceGroupName 'rg-dba-prd' -ServerName 'sql-contoso-prd-weu' -DatabaseName 'AppDb'
Add-AzSqlDatabaseToFailoverGroup -ResourceGroupName 'rg-dba-prd' `
-ServerName 'sql-contoso-prd-weu' -FailoverGroupName 'fog-contoso-prd-01' -Database $db
I pick a manual policy: deciding a production failover is a human call, not an automatism triggered by a partial regional incident. Adding a database starts seeding the copy, which takes a while for large databases.
Monitoring state and lag
In the database, the reference view is sys.dm_geo_replication_link_status:
SELECT partner_server, partner_database, role_desc,
replication_state_desc, replication_lag_sec, last_replication
FROM sys.dm_geo_replication_link_status;
On the metrics side, replication_lag_seconds is available in Azure Monitor, hence in Log Analytics; I plot it as a per-database heatmap and alert on sustained lag (a few seconds for several minutes), and on any state other than a healthy, caught-up one. Lag that climbs and never comes back usually points to an undersized secondary.
The failover drill
A recovery plan that was never run is not a plan.
- Pick a quiet window and warn the application teams.
- Check beforehand: lag near zero, all databases synchronised.
- Planned failover (no data loss):
Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName 'rg-dba-prd' `
-ServerName 'sql-contoso-prd-neu' -FailoverGroupName 'fog-contoso-prd-01'
- Check that roles are swapped, test applications through the group endpoint.
- Fail back, or stay: but record the measured duration.
Forced failover (-AllowDataLoss) is reserved for a real outage: it can lose the last unreplicated transactions.
Pitfalls
- Undersized secondary: the secondary's log throughput limits the primary; lag explodes during big batch loads.
- Users and logins: database users follow, server-level logins do not; use contained users or Entra identities, otherwise access breaks after failover.
- Network and firewall: rules and private endpoints must be duplicated on the secondary.
- What is not replicated: scheduled jobs, alerts, diagnostic settings, server configuration.
- Inventories and scripts that reference a physical server name: after a failover they point at the wrong role. Go through the group endpoint, and check real roles at resource level, not through a listener that hides the secondary.
- Moving a database to another pool while replicated: handle both sides and verify both.
Lessons learned
Lag monitoring and a regular drill catch 90 % of problems before the real outage. Write the runbook, time it, and update it after each exercise.