Geo-Replication for Azure SQL

A second region is useful only if the application can reach it. Geo-replication for Azure SQL keeps another database copy available when a region has a serious problem. The copy, the connection route, and the recovery objective all need separate tests.

Name the Failure You Need to Survive

A local service interruption and a whole-region outage are different events. Built-in high availability within a region addresses one class of failure. A geo-secondary in another region addresses another. Define recovery time and acceptable data loss before selecting a feature. Backups still matter for accidental deletion or corruption that replication carries forward.

I ask which user transaction must work first after a regional failover. That gives the team a concrete test. A secondary database that is online but unreachable from the application is a decorative replica, and decoration is not a recovery plan.

Understand Active Geo-Replication

Active geo-replication creates a readable secondary for an individual Azure SQL Database. Changes flow asynchronously from the primary. The secondary can serve suitable reads, but it can lag behind recent commits. A forced failover during an outage can therefore lose changes not yet hardened on the secondary.

Choose the partner region with network, data residency, and application dependencies in mind. I monitor lag during ordinary peaks, not just on a quiet setup day. A design that meets its recovery point objective only when nobody writes is not ready.

Use Failover Groups on Geo-Replication for an Endpoint

A failover group builds on geo-replication and can move a set of databases together. It provides stable listener endpoints for read-write and read-only connections. That reduces connection-string changes during role swaps. It does not make the application immune to transient failures; clients still need reconnection and safe transaction retry logic.

An application with several related databases needs a group-level plan. I verify which databases belong to the group and which do not. A forgotten configuration database can keep the service down after the main database has moved. The endpoint is helpful only when the full application dependency set is prepared.

Measure Geo-Replication Lag

Azure SQL exposes replication link status through a database DMV. Query it on the primary for partner and lag information. The exact columns and permission requirements depend on service context. Pair queue or lag metrics with an application-visible marker if reports require a freshness promise.

The query is read-only. A healthy status at one moment does not prove the link will keep up during a large load. I collect trends across the busy interval and alert when lag exceeds the agreed threshold.

SELECT partner_server, partner_database,
       role_desc, replication_state_desc,
       replication_lag_sec, last_replication
FROM sys.dm_geo_replication_link_status;

Prepare Authentication and Network Access

The secondary logical server needs appropriate firewall or private endpoint access, identity configuration, and permissions. Those server-level settings do not automatically match every primary setting. Check DNS resolution and route from the application’s failover location. A local test from an administrator machine is not enough.

I keep an access checklist for both regions. It includes application identity, monitoring identity, and the restore or failback operator. A secondary can hold current data while rejecting every production client. That is an outage with excellent replication metrics.

Choose a Drill That Matches Risk

A planned failover can synchronize before changing roles, while a forced failover during an actual regional loss has different data-loss exposure. Practice in an isolated copy of the application when production data loss is unacceptable. Record role-change time, first successful connection, first successful write, and data state.

Do not test only the portal action. I use an application transaction with a known identifier and check it on the new primary. Then I test reports, scheduled work, and user login. The service is recovered when its work completes, not when the button finishes spinning.

Inspect the Database Role

This query reports database name and updateability on the current connection. It helps confirm that a client reached a writable primary or a read-only secondary after routing. It does not prove the data is fresh. Use it together with a marker row and the link-status trend.

I run the check from the actual application connection path after every topology change. A connection pool can hold stale connections even when DNS and listeners are correct.

SELECT DB_NAME() AS database_name,
       DATABASEPROPERTYEX(DB_NAME(), 'Updateability')
           AS updateability,
       SYSUTCDATETIME() AS checked_at_utc;

Plan Failback Separately

When the original region returns, decide whether to keep the new primary or move back. Failback is another service transition, with its own synchronization and client behavior. Avoid an automatic rush to the old region before understanding data state and capacity. The recovered site must be able to receive the current database copy.

I write the failback criterion before an incident. It includes link health, business timing, application readiness, and a rollback plan. A region returning to green does not oblige the team to change roles during the busiest hour.

Keep Backups in the Design

Replication copies data changes, including mistaken deletes and bad updates. Point-in-time restore and longer retention protect different recovery cases. Check backup settings on the secondary side and test access to restored copies. Replication and backup form separate layers.

Geo-replication for Azure SQL is valuable when the application can tolerate or handle asynchronous lag and can route to the partner region. The durable result is a rehearsed user workflow with a known recovery point. Two dots on a map are only the start.

Test the client path, not only the database role change. A failover group listener helps with endpoint routing, but clients still need connection retries and correct authentication on the new primary. Check firewall rules, private networking, DNS behavior, and secrets before the drill. A successful role change with a disconnected application is not a successful recovery.

I record the last confirmed write before a planned drill and compare it after failover. Asynchronous replication has a possible data gap during an unplanned event. The business needs to know which loss window it accepts. After the drill, fail back as a separate planned operation and verify that monitoring and backup expectations follow the current primary. A single green failover button tells only part of the story.

After failover, can a user sign in and complete a write through the normal application endpoint?

Related reading on this blog: Database Disaster Recovery Process and SQL Server: T-SQL: Add and Remove IP Address to Azure Firewall.

A geo-secondary is not a complete failover, it is a data copy that an application must use correctly.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.