Replication Synchronization Delay

What this check looks for

Databases where is_merge_published = 1, checked for a recent synchronization. The interval defaults to 60 minutes and is configurable: if a DBHealthHistory database exists with a Settings table, the check reads its interval from there, so an instance whose subscribers legitimately sync less often can raise the bar rather than living with a finding that never clears.

The check is skipped on Amazon RDS.

Why it matters

Merge replication has a deadline that most other things do not, and missing it is not recoverable by simply catching up.

Every merge publication has a publication retention period, 14 days by default. Merge replication tracks changes in metadata tables, and that metadata is cleaned up after the retention period. A subscriber that has not synchronized within it expires: the metadata needed to reconcile its changes is gone, and the only way back is a full reinitialization, which means a fresh snapshot pushed to that subscriber.

For merge replication, which is typically used for laptops, branch offices, field devices and point of sale systems, a reinitialization is expensive in exactly the places it is hardest to arrange: over a slow link, to a machine that is rarely connected, belonging to somebody who is not a DBA.

Before the deadline, a subscriber that is not syncing is:

• Serving stale data to whoever uses it, with no indication that it is stale.
• Accumulating local changes that have not reached anyone else, and which are lost if the device fails.
• Growing the publisher’s metadata, because the change tracking cannot be cleaned up past the oldest subscriber that still needs it.

That last one is the part that turns one stuck subscriber into a publisher-wide problem.

How to confirm it yourself

Which databases are merge published:

SELECT [name], [is_merge_published], [is_published], [is_subscribed]
  FROM sys.databases WITH (NOLOCK)
 WHERE [is_merge_published] = 1 OR [is_published] = 1 OR [is_subscribed] = 1;

The subscriptions and when each last synchronized, run in the published database:

USE [YourPublishedDatabase];
GO

SELECT s.[subscriber_server],
       s.[db_name],
       s.[publication],
       s.[last_sync_date],
       DATEDIFF(MINUTE, s.[last_sync_date], GETDATE()) AS [minutes_since_sync],
       s.[status],
       s.[subscription_type]
  FROM dbo.sysmergesubscriptions AS s WITH (NOLOCK)
 WHERE s.[subscriber_server] IS NOT NULL
 ORDER BY s.[last_sync_date];

And the deadline, which is the number that decides urgency:

USE [YourPublishedDatabase];
GO

SELECT [name], [retention], [retention_period_unit]
  FROM dbo.sysmergepublications WITH (NOLOCK);

retention_period_unit is 0 for days, 1 for weeks, 2 for months, 3 for years. Compare the retention against minutes_since_sync above: a subscriber past it has already expired.

Agent history, which says whether the merge agent is failing or simply not running:

SELECT TOP (50) [time], [comments], [runstatus], [delivery_rate]
  FROM distribution.dbo.MSmerge_history WITH (NOLOCK)
 ORDER BY [time] DESC;

How to fix it

Establish whether the subscriber has expired, because that decides everything:

• Within retention: the subscriber can catch up. Get the merge agent running and it reconciles by itself.
• Past retention: it has to be reinitialized with a new snapshot. There is no shortcut.

Then find why it stopped. In rough order of likelihood:

1. The merge agent job is not running or is disabled. Check the Agent jobs on whichever side runs it, which for a pull subscription is the subscriber.
2. The subscriber is unreachable. For laptops and field devices this is normal and the question is how long is too long.
3. The agent is failing on a conflict or a constraint. Read MSmerge_history; the comments column carries the error.
4. Credentials. The agent’s account or its password changed.

Then reconsider the retention period. If subscribers legitimately go weeks without connecting, 14 days is the wrong number and raising it is a supported configuration change:

USE [YourPublishedDatabase];
GO
EXEC sp_changemergepublication
     @publication = N'YourPublication',
     @property = N'retention',
     @value = 30;

Longer retention costs more metadata on the publisher, which is a real trade rather than a free win.

How long it takes

About half an hour to determine whether the subscriber has expired and restart the agent. A reinitialization takes as long as the snapshot takes to reach the subscriber.

Related reports

Related checks

Frequently asked questions

Our subscribers are laptops that connect weekly. Then 60 minutes is the wrong threshold for you. Set ignoreLessThanCheck234Minutes in the DBHealthHistory Settings table to something that reflects your real pattern.

What happens exactly when a subscription expires? It is marked as expired and dropped from the publication by the expired subscription cleanup job. Resubscribing means a fresh snapshot.

Can I stop the cleanup to save an expired subscriber? Not usefully after the fact. The metadata it needed has already been removed.

We use transactional replication, not merge. This check only looks at merge published databases. Transactional replication has its own latency characteristics and its own tracer token mechanism for measuring them.