Azure SQL Database Active Geo‑Replication: Minimal Viable Architecture and Operational Checks

Requirements

Before deploying active geo‑replication, verify that the primary database resides in a service tier that supports the feature: Premium, Business Critical, or General Purpose. The secondary can be the same or a lower tier, but it must be provisioned in a different Azure region. Both databases must be in the same subscription, and the logical server hosting the secondary must allow inbound traffic from the primary’s region over the geo‑replication endpoint (port 1433).

Smallest Viable Design

The minimal configuration consists of one primary database and a single active geo‑secondary. Write workloads stay on the primary; read‑scale workloads can be directed to the secondary by setting ApplicationIntent=ReadOnly in the connection string. Data flows continuously over an encrypted TLS 1.2 channel, and the secondary remains a read‑only, transactionally consistent copy with typical lag under five seconds when network conditions are normal.

Example: Creating the Geo‑Secondary via PowerShell

# Variables – replace placeholders with your values
$resourceGroup   = "myResourceGroup"
$primaryServer   = "primary-sqlsrv"
$primaryDb       = "myDatabase"
$secondaryServer = "secondary-sqlsrv"
$secondaryLocation = "West US"

# Ensure the secondary server exists (same edition as primary or lower)
New-AzSqlServer -ResourceGroupName $resourceGroup -Location $secondaryLocation -ServerName $secondaryServer -SqlAdministratorCredentials $(Get-Credential)

# Create the geo‑secondary link
Set-AzSqlDatabaseSecondary -ResourceGroupName $resourceGroup -ServerName $primaryServer -DatabaseName $primaryDb -PartnerResourceGroupName $resourceGroup -PartnerServerName $secondaryServer -AllowConnections "All"

Run the script in a PowerShell session with the Az module installed and sufficient permissions (e.g., Contributor on the resource group). The command creates a read‑only secondary; no data is written directly to it.

Trust and Data Boundaries

The replication channel is considered a trusted path: data is encrypted in transit with TLS 1.2, and the secondary cannot accept write operations. However, the trust boundary ends at the secondary’s storage layer; logical corruption (e.g., a dropped table) propagates to the secondary because it is a physical copy of the primary’s data pages. Therefore, geo‑replication does not replace point‑in‑time restore for user error or logical corruption.

Operational Checks

• Replication lag monitoring: Query the dynamic management view on the primary:

  SELECT partner_server, replication_lag_sec, replication_state_desc
  FROM sys.dm_geo_replication_links
  WHERE database_id = DB_ID('myDatabase');
  

  The column replication_lag_sec should show a low, stable value (e.g., <5 seconds) during normal operation. Set an Azure Monitor alert on this metric with a threshold appropriate for your SLA (e.g., >10 seconds for 5 minutes).
• Planned failover drill: Initiate a geo‑failover to validate RTO/RPO:

  Set-AzSqlDatabaseSecondary -ResourceGroupName $resourceGroup -ServerName $primaryServer -DatabaseName $primaryDb -Failover
  

  After the command completes, the former secondary becomes the new primary. Verify that application connections can be redirected (update the connection string to point to the new primary server) and that write transactions succeed within your expected recovery time objective.
• Encryption verification: Use Azure Diagnostics or a network capture tool to confirm that traffic on the geo‑replication endpoint (port 1433) is TLS 1.2. No additional configuration is required; the service enforces encryption by default.

Failure Modes and Remediation

• Primary region outage: Trigger a geo‑failover (planned or forced). After failover, the former secondary assumes the primary role. Monitor application reconnection and verify that no data loss exceeds the observed replication lag at the moment of failure.
• Network partition causing lag spikes: If replication_lag_sec rises sharply, investigate connectivity between the regions (e.g., Azure ExpressRoute or VPN health). Temporary throttling may occur; consider scaling up the secondary tier if sustained lag threatens your RPO.
• Secondary out‑of‑sync due to long‑running transactions: A long‑running transaction on the primary can block log truncation, increasing lag. Identify and kill or optimize the offending query, then verify that lag returns to baseline.

Conditions That Would Change the Design

• Regulatory constraints that prohibit data from leaving a specific jurisdiction: in such cases, consider zone‑redundant configuration within the same region or use Azure SQL Database’s built‑in backup geo‑redundancy instead of active geo‑replication.
• Workload exceeds the secondary’s resources: if the primary’s write throughput consistently saturates a lower‑tier secondary, either upgrade the secondary to match the primary’s service objective or off‑load read‑scale workloads to a dedicated read‑scale pool.
• Need for near‑zero RPO: active geo‑replication’s asynchronous nature means a small data loss window is possible. For applications requiring zero loss, evaluate synchronous alternatives such as Azure SQL Managed Instance auto‑failover groups or always‑on availability groups on SQL Server virtual machines.

Practical Verification Checklist

1. Confirm primary tier is Premium, Business Critical, or General Purpose.
2. Verify secondary is in a different Azure region and that the logical server allows inbound traffic from the primary’s region.
3. Run the lag query (sys.dm_geo_replication_links) and note the value; ensure it stays below your defined threshold during a representative load window.
4. Perform a planned failover using the PowerShell command above and record the time until applications can write successfully.
5. Check network traces or Azure Diagnostics for TLS 1.2 on the replication endpoint.
6. Document any observed lag spikes and correlate them with network or resource metrics to inform future scaling decisions.

By following this minimal viable design and the associated operational checks, you can establish a reliable active geo‑replication setup for Azure SQL Database while understanding its limits and the scenarios that would necessitate a redesign.