How can I determine the last synchronization time for a SQL Server replication subscription?

Question

I'm using SQL Server replication to mirror a few tables from an on-premises SQL Server instance to an Azure SQL database. We had an issue where the replication encountered an error, and we incorrectly assumed that the data in the read-only replica was current. I'm trying to setup an alert system that will notify me if the replica database is more than 30 minutes out of sync with the distributor.

In Replication Monitor, I can see the "Last Synchronization", but that timestamp doesn't seem to be in any of the dbo.MS* system tables on Azure SQL. I'm looking to get this information from the subscriber instead of the distributor.

Answer 1

From the subscriber side, you should be able to run EXEC dbo.sp_helppullsubscription to get some level of detail.

As an example:

use [SomeDB]
EXEC dbo.sp_helppullsubscription 
    @publisher = N'MYSERVER'
    , @publisher_db = N'SomeDB'
    , @publication = N'SomeDBPublication';

One of the many columns returned will be last updating time, which is in this format: yyyymmdd hh:mm:ss.mmm. That column indicates the time the subscription information was updated.

If you look at this query from the subscriber database:

SELECT *
FROM dbo.MSreplication_subscriptions s
WHERE s.publisher = 'MYSERVER'
    AND s.publisher_db = 'SomeDb'
    AND s.publication = 'SomePublication';

The time column contains the time of the last update by the Distribution Agent.

You can setup threshold warnings in Replication Monitor; Microsoft Docs shows how to do that. Also, this Microsoft Docs page shows how to programmatically monitor various aspects of Replication.

@SqlWorldWide has a good article on monitoring replication on their site.