Home / Azure

AzureRunbookIntermediate

Azure SQL auto-failover groups: cross-region DR with stable connection strings

Set up failover groups for Azure SQL Database or Managed Instance, use the listener endpoints in applications, choose the failover policy, and run planned and forced failovers.

Deepak Bhardwaj2 min read

Applies toAzure SQL Database, Azure SQL Managed Instance

Why failover groups (not just geo-replication)

Feature Active geo-replication Failover group
Databases One at a time A group failing over together
Connection endpoint Changes after failover Stable listeners: <fog>.database.windows.net and <fog>.secondary.database.windows.net
Managed Instance Not supported Supported

Replication is asynchronous: some recent transactions can be lost on a forced failover. Typical replication lag is seconds.

1. Create the secondary server and the group

az sql server create -g rg-sql-sin -n sql-prod-sin -l southindia --enable-ad-only-auth \
  --external-admin-principal-type Group --external-admin-name sql-admins --external-admin-sid <group-object-id>

az sql failover-group create -g rg-sql-cin -s sql-prod-cin -n fog-sales \
  --partner-server sql-prod-sin --partner-resource-group rg-sql-sin \
  --add-db Sales Orders --failover-policy Manual

The secondary database should have the same service tier and size as the primary, or it can't keep up after failover.

2. Failover policy

  • Manual (recommended for most): your team decides; no surprise failovers for partial outages.
  • Automatic with a grace period (minimum 1 hour): Microsoft initiates failover only for major regional outages after the grace period. Don't expect it to act quickly.

3. Applications

Read-write: Server=tcp:fog-sales.database.windows.net,1433;Database=Sales;...
Read-only:  Server=tcp:fog-sales.secondary.database.windows.net,1433;Database=Sales;ApplicationIntent=ReadOnly;...
  • Private endpoints: create them for both servers, in both regions, with DNS records in the central private zone. The failover group listener resolves through CNAMEs to the current primary.
  • Logins: with Entra ID authentication, nothing to sync. With SQL logins in master, create them on the secondary server with the same SID.
  • App tier in the DR region should be ready (or deployable) to use the listener.

4. Planned failover (no data loss)

az sql failover-group set-primary -g rg-sql-sin -s sql-prod-sin -n fog-sales

Fully synchronises before switching. Use it for DR drills and planned region moves.

5. Forced failover (disaster)

az sql failover-group set-primary -g rg-sql-sin -s sql-prod-sin -n fog-sales --allow-data-loss

Check replication lag beforehand when possible:

SELECT partner_server, partner_database, replication_state_desc, replication_lag_sec, last_replication
FROM sys.dm_geo_replication_link_status;   -- run in the primary database

6. After failover

  • The old primary becomes secondary automatically when it comes back.
  • Fail back with a planned failover once the original region is healthy.
  • Re-check firewall rules, auditing and alerting on the new primary server.

Tip

Run a planned failover twice a year in production during a maintenance window. It's the only way to prove that applications, private DNS and logins really work in the other region.

Related fixes