Replication: My merge agent keeps “dropping and creating” my newly added articles!!!

  • Article

Problem:

Consider the following scenario:

  • You add a new article(s) to an existing publication in your merge replication environment and
  • Your merge agent uses '-ParallelUploadDownload' parameter with a value of 1 for synchronization.

Note: Merge Agent profile High Volume Server-to-Server Profile enables parameter -ParallelUploadDownload

In this scenario, you will notice that the merge agent enumerates the already replicated schema changes during each sync operation causing performance problems and other issues.

Cause / Workaround:

The schemaversion column in sysmergesubscriptions table at the subscriber (and publisher) stores the version of the last schema that is received. Due to this issue, this value does not get updated and always points to a version that has already been replicated over to Subscriber during the previous synchronization operation.

You can workaround this issue by either disabling or removing the Merge Agent parameter -ParallelUploadDownload for that subscription. Disabling this parameter causes Merge Agent to update schemaversion to the correct value during next synchronization.

To remove or disable this parameter edit either 'Agent job' or 'Agent Profile' using one of the following procedures that is applicable to your situation:

Agent Job:

  1. Open Merge Agent Job properties.
  2. Select "Steps" page.
  3. Select "Run agent" step and press "Edit" button.
  4. In "Command", look for parameter "-ParallelUploadDownload". If it has value 1, disable it by setting it to 0 (i.e. "-ParallelUploadDownload 0"). OR Remove the parameter and its value altogether from Merge Agent command.

Agent Profile:

  1. Open Replication Monitor.
  2. Select Merge Publication.
  3. Right click Subscription and go to "Agent Profile".
  4. Make sure Profile "High Volume Server-to-Server Profile" is NOT selected.
  5. If custom profile is used and if parameter "-ParallelUploadDownload" is available, make sure it is set to value 0.

Note: Merge Agent parameter "-ParallelUploadDownload" is deprecated in SQL Server 2008.

https://support.microsoft.com/kb/2003609