Merge Replication Validation Failure

Question

We are getting a bizarre error with some of our users while syncing with SQL Server 2008 R2 Service Pack 3. Validation fails even though the row count and expected row count actually match!

Error messages:

Data validation failed for one or more articles.
Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200953
Article 'Quote' failed data validation (rowcount and checksum).
Rowcount actual: 973, expected: 973.

Would it cause a problem disabling the validation? Is there a good example on how to create your own validation?

Answer 1

The error could say:

Article 'Quote' failed data validation (rowcount only).

However your error states:

Article 'Quote' failed data validation (rowcount and checksum).

The validation of the merge performs a binary checksum like this:

select count_big(*), sum (convert(numeric, binary_checksum(*) ) ) 
from [dbo].[tablename] WITH (TABLOCK HOLDLOCK)

As stated by others, the validation during merge can be a bit buggy and you may find it better to roll your own validation.

You can turn off validation by removing the -Validate x parameter from the merge agent.

Regarding your error: Microsoft recommends trying again later, as if the error is temporary. As for code to validate it, you could try the code I link to, but I think you will need to write your own to validate it based on business rules for your data. There shouldn't be harm in disabling the validation, if there was inherent harm the option wouldn't exist. But I would try it with a temporary/test server first.