LLI Wiki — My Digital Knowledge Base

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.

  1. Pick a quiet window and warn the application teams.
  2. Check beforehand: lag near zero, all databases synchronised.
  3. Planned failover (no data loss):
Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName 'rg-dba-prd' `
    -ServerName 'sql-contoso-prd-neu' -FailoverGroupName 'fog-contoso-prd-01'
  1. Check that roles are swapped, test applications through the group endpoint.
  2. 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

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.

Mastodon LLIMastodon SocialFosstodon