This post has been republished via RSS; it originally appeared at: SQL Server Support articles.
Recently worked on an interesting case. Customer had an existing Merge Replication and it was working fine.
They added a new article to the existing Publication and ran the Snapshot Agent.
Now Every time Merge agent runs we see that Schema changes were getting re-applied.
At the start of a synchronization, the schemaversion of the subscription is checked against sysmergeschemachange by the Merge Agent, and schema changes are applied to the subscriber accordingly.
Ran these queries on the subscriber
>>>
select max(schemaversion) from sysmergeschemachange
--22
select schemaversion, pubid, subid from sysmergesubscriptions
where pubid<>subid
schemaversion pubid subid
15 A0B0FAA8-22B9-4CC5-8AED-547F2EBCEB83 85CD9161-5659-42CC-A501-BB43072B0045
Here we have new schema versions after 15. So we will apply all the schema changes until schemaversion becomes 22.
What we saw was schemaversion was not getting updated on the subscriber. It remained 15 after every Merge agent completion. That was the reason schema changes were getting applied repeatedly.
We Checked what profile is used by the Merge agent.
>>>
Right click on the subscription and click on Agent Profile:
In this case we are using "High Volume Server to Server Profile" and with this ParallelUploadDownload is set to 1.
ParallelUploadDownload is a deprecated feature. There is a known issue where we don’t update schema water marks if ParallelUploadDownload is set to 1. We changed Merge agent profile to use "Default agent profile" or change Merge agent Job and add "-ParallelUploadDownload 0".
Ran the Merge agent again.
Now the schema watermark was updated successfully on the subscriber and the schema changes were not getting reapplied.
select max(schemaversion) from sysmergeschemachange
--22
select schemaversion, pubid, subid from sysmergesubscriptions
where pubid<>subid
schemaversion pubid subid
22 A0B0FAA8-22B9-4CC5-8AED-547F2EBCEB83 85CD9161-5659-42CC-A501-BB43072B0045