Sql Server 2012 Merge Replication Report

Question

We have about 30 users with local installs of SQL Server that have to replicate merge their local data once a month with a central SQL Server 2012 repository.

Is there a SQL Server first-party or third-party tool that will allow us to see when various users last replicated (or even a history of all replications)? Ideally in report format (but a SQL Query will work as well), so that the application admins can make sure their users are synchronising at least once per month?

Does Replication Monitor include a history report so we can see when the other databases have last synced, going back 2-3 months?

Answer 1

Have you tried Replication Monitor?

Answer 2

For things like this rather than using the Replication Monitor I have a script (this one is for the merge replication)

--====================================================================================
-- merge replication- check the history
-- to be run either in one of the subscriptions or the main publication
-- marcello miorelli 
-- 15-Dec-2016
--====================================================================================

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT

smp.publisher
,smp.publisher_db
,smp.name
,smp.distributor
,sma.Name
,sma.destination_object
,sma.destination_owner
,sms.subscriber_server
,sms.db_name
,subscriber_type= CASE sms.subscriber_type 
                       WHEN 1 THEN 'Global'
                       WHEN 2 THEN 'Local'
                       WHEN 3 THEN 'Anonymous'
                       ELSE 'Unknown'
                  END

,subscription_type = CASE sms.subscription_type 
                       WHEN 0 THEN 'Push'
                       WHEN 1 THEN 'Pull'
                       WHEN 2 THEN 'Anonymous'
                       ELSE 'Unknown'
                  END

,sync_type         = CASE sms.sync_type
                       WHEN 1 THEN 'Automatic'
                       WHEN 2 THEN 'No synchronization'
                       ELSE 'Unknown'
                  END
,last_sync_date = REPLACE(LEFT(CONVERT(VARCHAR(30),sms.last_sync_date,113),20),' ','-')
,last_sync_status= CASE sms.sync_type
                       WHEN 0 THEN 'All jobs are waiting to start'
                       WHEN 1 THEN 'One or more jobs are starting'
                       WHEN 2 THEN 'All jobs have executed successfully'
                       WHEN 3 THEN 'At least one job is executingy'
                       WHEN 4 THEN 'All jobs are scheduled and idle'
                       WHEN 5 THEN 'At least one job is attempting to execute after a previous failure'
                       WHEN 6 THEN 'At least one job has failed to execute successfully'
                       ELSE 'Unknown'
                  END

FROM

sysmergearticles sma
inner join sysmergepublications smp
        on sma.pubid=smp.pubid

inner join sysmergesubscriptions sms
        on sms.pubid=smp.pubid

That produces something like this: