Azure SQL Database offers business continuity capabilities to recover quickly from regional disasters.  Features such as active geo-replication and failover groups provide continuous replication of the data in your primary database to a secondary database in a different Azure region.  In the event of a regional disruption, these features allow you to perform quick disaster recovery to your secondary database to meet your business' recovery time objective (RTO) and recovery point objective (RPO).  RTO for Azure SQL Database is typically less than 60 seconds, but RPO depends on the amount of data changes before the disruptive event that have not been replicated.  Consequently, monitoring the replication lag between the primary and secondary databases is critical in ensuring your RPO goals are met.

Until now, the main way to measure the replication lag between the primary and secondary databases was with the replication_lag_sec column of the dynamic management view (DMV), sys.dm_geo_replication_link_status from your primary database.  With the introduction of the Replication lag metric, you can now monitor lag with respect to RPO in near real time in the Azure portal in addition to using the DMV.  Replication lag is a new Azure monitor metric that is emitted at a one-minute frequency and stored up to 93 days.  You can visualize the metric in Azure monitor and set up alerts too.

The replication lag metric measures the time span in seconds from the point of transaction commit on the primary database and acknowledgement by the secondary database that the transaction log update has been persisted.  The replication lag metric is applicable for a database in DTU or vCore purchasing model and in all service tiers (Basic, Standard, Premium, General Purpose, Business Critical & Hyperscale).  Both singleton and elastic pool deployments are supported. You can monitor the metric by adding Replication lag (preview) from your primary database in the portal as shown below:

The metric provides three dimensions, Partner Database Name, Partner Server Name, and Partner Resource ID that you can use to further filter or split the data to view specific replication links.

If your database is configured to send Metrics to Log Analytics under “Diagnostic settings”, you can also query the Replication lag metric data as shown below:

Next Steps

Learn more about monitoring geo-replication and other commonly used metrics in Azure SQL Database.

See how you can achieve your business continuity goals with Azure SQL Database using Active geo-replication and Failover groups.

Prepare for disasters with the Disaster recovery checklist

Frequently Asked Questions

1. What is the Replication lag and what does it measure?
   
   The Replication lag is a new metric in Azure monitor that measures the time span in seconds between the transactions committed on the primary and hardened to the transaction log on the secondary.
   
   
2. How do you view the metric in the portal?
   
   In the Azure portal, select your primary SQL database and under "Monitoring", select "Metrics".  In the "Metrics" dropdown, choose "Replication lag (preview)".
   
   
3. What is the granularity of the Replication lag metric?
   
   One minute.
   
   
4. What is the latency in displaying the Replication lag data in the Metrics screen?
   
   Typically, the latency to display the replication lag is less than three minutes.
   
   
5. Are there dimensions available for the Replication lag metric?
   
   Yes, there are three dimensions available for the metric - Partner Database Name, Partner Server Name, and Partner Resource ID.  These dimensions can be used for filtering and splitting the view in the Metrics screen for easier comparison of multiple secondary geo-replicas.