14.2 Active Geo-Replication

Active Geo-Replication Architecture

Active geo-replication is an Azure SQL Database feature that maintains up to four readable secondary replicas of a single database, each in a different Azure region, continuously seeded from the primary's transaction log. Replication is asynchronous: the primary commits a transaction, then ships the log to each secondary, so the primary never waits on secondaries and never blocks. The practical RPO for committed transactions is approximately 0 seconds - any transaction that committed on the primary will eventually reach every healthy secondary; the RTO to redirect clients after a failover is typically minutes (DNS/connection-string update plus a brief connection replay).

The architecture is per-database: each geo-replication link is configured and managed independently for one database, not a server or instance. A primary can host up to four secondaries across any Azure regions (not limited to paired regions, though pairing is recommended for performance and billing). All secondaries are readable, which means you can offload read-only reporting and analytical workloads to a secondary without copying data, simply by pointing a connection string at the secondary's server.

Configuring Active Geo-Replication

You can configure active geo-replication through the Azure portal (database -> Geo-Replication -> select region -> Create secondary), PowerShell (New-AzSqlDatabaseSecondary), the Azure CLI (az sql db replica create), or T-SQL:

-- On the secondary server, partner the databases
ALTER DATABASE [Sales]
ADD SECONDARY ON SERVER [secondary-server] WITH (ALLOW_CONNECTIONS = ALL);

ALLOW_CONNECTIONS = ALL makes the secondary readable; NO restricts it to maintenance-only. The secondary is created in the target region at a service objective you specify; it can match or differ from the primary. The seeding - the initial copy of the database to the new secondary - is automatic and online but takes time proportional to database size and bandwidth between the regions.

Failover: Planned, Unplanned, and Forced

Planned failover swaps roles cleanly: the secondary catches up to the primary's last LSN, the primary quiesces new transactions, the roles reverse, and the old primary becomes a secondary of the new primary. No data is lost. In T-SQL it is initiated from the new intended primary:

ALTER DATABASE [Sales] SET FORCE_FAILOVER_ALLOW_DATA_LOSS;

Wait - that command is forced failover. For a planned failover, you use the portal's Force failover button with the secondary being current, or Set-AzSqlDatabaseSecondary -Failover in PowerShell which performs a planned role change. Unplanned (forced) failover is invoked only when the primary is unreachable; it promotes the secondary but may lose transactions that were committed on the primary but not yet replicated. The keyword FORCE_FAILOVER_ALLOW_DATA_LOSS makes that explicit in the command name so administrators do not accidentally use it for routine failovers.

After failover, the old primary becomes a secondary of the new primary if it is still reachable; if it is permanently gone, you can reconfigure geo-replication from the new primary. Application connection strings must be repointed to the new primary's server FQDN, because active geo-replication does not provide a listener endpoint - this is the most-tested difference from failover groups.

Read Scale-Out with Readable Secondaries

Because all secondaries are readable, active geo-replication doubles as a read scale-out mechanism. A reporting workload in another region can read from the local secondary with low latency, and a regional reporting workload can read from any secondary. However, secondaries are read-only - any write attempt fails. The replica lag depends on the log rate and cross-region bandwidth; you can monitor it and design read-only applications to tolerate near-real-time data.

On Azure SQL Database, the Business Critical and Premium tiers also include a free in-region readable secondary via the same Always On technology; active geo-replication is the cross-region extension of that capability. On General Purpose there is no in-region readable replica, but active geo-replication still provides cross-region readable secondaries.

SQL Managed Instance: A Different Model

Active geo-replication in this per-database form is not supported on SQL Managed Instance. SQL MI uses failover groups (Section 14.3) for automatic cross-region failover of one or more databases, or the Managed Instance link feature for hybrid replication to an on-premises SQL Server. An exam scenario that asks for active geo-replication on SQL MI is steering you toward failover groups, not ALTER DATABASE ... ADD SECONDARY.

Monitoring and Split-Brain Prevention

Two catalog views expose the replication link state on the primary and secondary servers respectively:

• sys.geo_replication_links - one row per replication link from the primary's perspective, including link_guid, partner_server, partner_database, replication_state, and role.
• sys.dm_geo_replication_link_status - runtime status: last_replication_date, last_lsn, replication_lag_sec, and redo_queue_size.

On the secondary, sys.dm_geo_replication_link_status shows the last hardened LSN and the replication lag. A healthy link has replication_state = 1 (SEEDING or ACTIVE); a forced failover transitions the link through PENDING before the new primary is online.

Split-brain - two primaries serving the same database concurrently - is prevented because the platform allows only one writable primary at a time and the link state is coordinated through Azure's control plane. Forced failover severs the old link before promoting the secondary; the old primary, if it recovers, finds its link severed and cannot accept writes until it is re-paired as a secondary. Application-level logic should still verify which server is primary before issuing writes, and you should alert on replication lag exceeding an SLA threshold so a forced failover decision is informed.

Region Pairing and Failover Group Relationship

Microsoft pairs Azure regions (e.g. East US 2 / Central US, North Europe / West Europe) for coordinated updates, data residency, and cross-region replication of platform storage. Active geo-replication does not require paired regions, but using pairs is recommended for predictable latency, jurisdictional compliance, and because paired regions are the default replication target for the LTR and automated backup storage that underpins recovery.

Active geo-replication is the building block of failover groups: a failover group wraps one or more geo-replicated databases on a logical server, adds a read-write listener endpoint and read-only listener endpoint, and provides automatic failover with a grace-period policy. The relationship to remember: a failover group's underlying replication is active geo-replication; the group layer adds listeners, multi-database coordination, and auto-failover.