Azure SQL auto-failover groups: minimal geo-replication design for a single database

The problem you are solving

You need regional resilience for a single Azure SQL Database without forcing the application to change connection strings during an outage. Active geo-replication gives you an asynchronously replicated readable secondary. An auto-failover group adds a stable read-write listener that moves via DNS when failover occurs.

The useful takeaway is that the smallest production-ready design is one primary database plus one secondary in a paired region inside an auto-failover group, with the app pointed at the group listener. Anything smaller loses automatic cutover; anything larger adds cost before you have proven RPO/RTO needs.

Requirements for this design

Service tier and engine version must support auto-failover groups. The primary and secondary logical servers must be in Azure paired regions. The database must be a single database, not a cross-database elastic pool in the same failover group configuration.

The application must tolerate transient connection errors and retry. Failover drops in-flight connections even on a planned cutover because the listener DNS record is updated and clients reconnect to the new primary.

Network access must be consistent on both servers. Firewall rules, virtual network rules, and private endpoints are server-scoped. If the secondary server blocks the app, failover will succeed but the app will be unable to connect.

Smallest suitable design

One primary logical server in Region A with one database. One secondary logical server in paired Region B. One auto-failover group linking the two servers. The database is replicated via active geo-replication to the secondary, which is readable.

Application connection string uses the group read-write listener name, not the server name. Example listener format is <group-name>-<primary-server>.database.windows.net. The read-only listener is <group-name>-ro-<primary-server>.database.windows.net for offloading reads.

Creation is done at the server level, not per database. Run from a machine with Azure CLI installed and a principal with Contributor on the resource group. Meaningful placeholders are used below.

az sql failover-group create \
  --resource-group <rg-name> \
  --server <primary-server> \
  --name <failover-group-name> \
  --partner-server <secondary-server> \
  --partner-resource-group <secondary-rg> \
  --failover-policy Automatic \
  --grace-period <minutes>

Risk: creating the group enables automatic failover. Set grace-period to control how long Azure waits before failing over on detected primary unavailability. Do not assume the secondary is ready for writes until the group reports healthy.

Trust and data boundaries

Replication copies database data only. Server-level objects do not replicate automatically. Logins, contained database users, firewall rules, and transparent data encryption settings must be kept consistent across both servers.

Data boundary: the secondary inherits the primary's encryption configuration, but keys and CMK settings are server scoped. If a failover occurs and the secondary cannot decrypt, the database will be inaccessible.

Operational boundary: the secondary database is read-only until it becomes primary. Any application that writes directly to the secondary will fail. Use the read-only listener explicitly for read workloads.

Operational checks

Monitor replication lag. On the primary, query the geo-replication DMV as a user with VIEW DATABASE STATE or higher on the database.

SELECT database_name, replica_server_name, synchronization_state_desc, synchronization_health_desc
FROM sys.dm_geo_replication_link_status;

Expected check: synchronization_state_desc is SYNCHRONIZED and health is HEALTHY before a planned failover. Do not assume portal green means zero lag.

Periodically test read-only access against the read-only listener from the application network. This validates firewall rules and routing.

Rehearse a planned failover in a non-production group. Planned failover waits for the secondary to catch up and avoids data loss, unlike forced failover. Confirm the application reconnects without connection-string changes and that retry logic handles the brief connection drop.

Failure modes

Asynchronous replication means forced failover can lose committed transactions. RPO is nonzero, typically seconds under normal conditions. Planned failover reduces RPO but increases RTO.

DNS propagation for the listener can take time. Clients with connection pooling may hold stale endpoints. Implement retry with exponential backoff.

Configuration drift. A secondary that is never exercised can have missing logins or stale firewall rules even though replication reports healthy. Failover will surface access failures.

Failover group limits are tier-sensitive. Confirm supported service tiers and replication limits for your SKU before committing.

When to change the design

Strict zero data loss requirement. Active geo-replication is asynchronous. Consider zone-redundant configuration for zone-level resilience or evaluate synchronous options where available, acknowledging different cost and latency trade-offs.

Multi-region write needs. Auto-failover groups provide one writable primary at a time. Active-active writes require a different architecture.

Cost constraints. A warm standby secondary doubles compute cost for the database. If RTO can be hours, a backup-based geo-restore strategy is cheaper and slower.

Elastic pools or Hyperscale. Auto-failover group behavior and limits differ for pools and Hyperscale. Verify current documentation for your engine version before applying the single-database pattern.

Limitation note: behavior, supported tiers, and replication limits are version- and tier-sensitive. Confirm specifics against current Azure SQL Database documentation before committing to production.