Merge Replication Conflicts

What this check looks for

Conflict tables in each merge published database, looked at across every online database on the instance. Merge replication creates a MSmerge_conflict_<publication>_<article> table for each article that has had a conflict, and the check reports where rows exist in them.

It requires SQL Server 2012 or later, tested from the product version.

Why it matters

A conflict means the resolver already made a decision and threw a change away. This finding is not a warning that something might go wrong; it is a record that data was discarded.

Merge replication exists so that several sites can change the same data while disconnected. When two of them change the same row, somebody has to lose. The default resolver picks by priority: the publisher wins, or the higher priority subscriber wins, depending on the publication. The losing row is written into the conflict table and the winning value is what everybody ends up with.

That is the designed behaviour and it is correct. The problem is that the losing change was real work. Somebody in a branch office updated a customer record, the sync ran, and their edit no longer exists anywhere except a conflict table nobody reads. No error was raised to them. Their screen showed success.

So conflicts matter in two ways:

• Individually, because each one may be a business change that needs applying by hand.
• In aggregate, because a steady conflict rate means the application’s data partitioning is wrong. Merge replication works well when sites mostly edit their own rows. A high conflict count says they are editing each other’s, and no resolver setting fixes a design that has two places editing the same data.

Conflict tables also grow, and they are retained for the publication retention period.

How to confirm it yourself

Which databases are merge published:

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

The supported way to see conflicts for a publication:

USE [YourPublishedDatabase];
GO
EXEC sp_helpmergearticleconflicts @publication = N'YourPublication';

Then read the conflicts for one article:

USE [YourPublishedDatabase];
GO
EXEC sp_helpmergeconflictrows
     @publication    = N'YourPublication',
     @conflict_table = N'MSmerge_conflict_YourPublication_YourArticle';

Or find the conflict tables directly and see how many rows are in each, which is the quickest way to see the scale:

USE [YourPublishedDatabase];
GO

SELECT t.[name] AS [conflict_table],
       SUM(p.[rows]) AS [conflict_rows]
  FROM sys.tables AS t WITH (NOLOCK)
 INNER JOIN sys.partitions AS p WITH (NOLOCK)
         ON p.[object_id] = t.[object_id] AND p.[index_id] IN (0, 1)
 WHERE t.[name] LIKE 'MSmerge_conflict%'
 GROUP BY t.[name]
HAVING SUM(p.[rows]) > 0
 ORDER BY [conflict_rows] DESC;

And the conflict log, which says which side lost and why:

SELECT TOP (100) *
  FROM dbo.MSmerge_conflicts_info WITH (NOLOCK)
 ORDER BY [origin_datasource];

How to fix it

Two separate pieces of work, and the first is not optional.

Review the conflicts that have already happened. Each row in a conflict table is a change that was discarded. Somebody has to decide whether it mattered. That is a business decision rather than a database one, and the rows carry enough to make it: the losing values, the winning values, and which site each came from.

Do not simply clear the tables. They are the only record that the change existed.

Then reduce the rate, in this order:

1. Look at which articles conflict. It is nearly always a few tables, and usually shared reference data rather than the transactional tables merge replication is good at.
2. Partition the data better. If each site edits only its own rows, conflicts stop happening. Filtered articles, where each subscriber receives only its own partition, are the mechanism.
3. Move shared reference data to a different replication type. Data that only the publisher changes does not need merge replication; transactional or snapshot replication sends it one way and cannot conflict.
4. Consider column level tracking. By default merge replication detects conflicts at the row level, so two sites editing different columns of the same row still conflict. Column level tracking means they do not:

EXEC sp_changemergearticle
     @publication = N'YourPublication',
     @article = N'YourArticle',
     @property = N'column_tracking',
     @value = N'true',
     @force_invalidate_snapshot = 1,
     @force_reinit_subscription = 1;

Note what those last two parameters mean: changing this reinitializes the subscriptions.

5. A custom resolver is available where a business rule can decide better than priority can, such as taking the higher of two stock counts. It is real development work and it is the right answer for a small number of cases.

How long it takes

About half an hour to see the scale and which articles are involved. Reviewing the discarded rows and changing the partitioning are larger pieces of work.

Related reports

Related checks

Frequently asked questions

Conflicts are normal in merge replication, aren’t they? Occasional ones are expected and are what the resolver exists for. A steady stream means two sites are editing the same data, which is a partitioning problem rather than a replication one.

Can I just delete the conflict tables? They are cleaned up with the publication retention. Deleting them early destroys the only record of the discarded changes.

Which side wins by default? The one with the higher priority, which for a default publication is the publisher. It is set per subscription.

We only have one subscriber. Then conflicts mean the publisher and that subscriber are both editing the same rows, which is worth understanding regardless of the count.