Study guide
14.2 Active Geo-Replication
DP-300: Administering Microsoft Azure SQL Solutions Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources
2 Planning and Deploying Azure SQL Solutions
2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching
3 Configuring Resources for Scale and Performance
3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression
4 Planning and Implementing a Migration Strategy
4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting
5 Configuring Database Authentication and Authorization
5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management
6 Implementing Security for Data at Rest and in Transit
6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints
7 Implementing Compliance Controls for Sensitive Data
7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL
8 Monitoring Resource Activity and Performance
8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events
9 Monitoring and Optimizing Query Performance
9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights
10 Configuring Database Solutions for Optimal Performance
10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing
11 Creating and Managing SQL Server Agent Jobs
11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates
12 Automating Deployments and Database Tasks in Azure
12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks
13 Planning an HA/DR Strategy and Backup and Restore
13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations
14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups
14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups
15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting
15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions
14.2 Active Geo-Replication
Key Takeaways Explain This Topic AI
Active geo-replication on Azure SQL Database supports one primary plus up to four readable asynchronous secondaries in paired or any regions, with an RPO of approximately 0 seconds for committed transactions and an RTO measured in minutes
It is a per-database feature with no listener endpoint; applications that fail over must redirect their connection strings to the secondary's server, and failover is initiated manually with ALTER DATABASE ... SECONDARY or FORCE_FAILOVER_ALLOW_DATA_LOSS for unplanned events
Readable secondaries can serve read-only workloads for read scale-out, but writes are only accepted on the current primary; the link is asynchronous so secondaries may lag slightly
Active geo-replication in this form exists only on Azure SQL Database; SQL Managed Instance uses failover groups (or the Managed Instance link feature) rather than per-database active geo-replication
Monitoring relies on sys.geo_replication_links and sys.dm_geo_replication_link_status, which expose replication state, last-LSN, and lag; split-brain is prevented by promoting the old primary only after the link is severed
Last updated: August 2026
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.
- Property Active geo-replication
- Scope Single database
- Secondary count Up to 4
- Secondary readable Yes (read-only)
- Replication Asynchronous (Always On technology)
- RPO (committed) ~0 seconds
- RTO Minutes (manual redirect)
- Listener endpoint None - redirect connection string
- Failover Manual (planned or forced)
- Supported on Azure SQL Database only (in this form)
- 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.