Replication: Unable to Synchronize subscribers via Web Synchronization

reposting from Claudia Silva
Microsoft SQL Escalation Services United Kingdom
http://blogs.technet.com/b/claudia_silva/

Problem:

The upload message to be sent to Publisher ‘XXX’ is being generated

The merge process is using Exchange ID ‘XXXXX’ for this web synchronization session.

No data needed to be merged.

Request message generated, now making it ready for upload.

Upload request size is 1024 bytes.

Uploaded a total of 1 chunks.

The request message was sent to ‘https://xxxxx/replisapi.dll’

Downloaded a total of 100 chunks.

Downloaded a total of 200 chunks.

Downloaded a total of 300 chunks.

. . .

Downloaded a total of 3000 chunks.

Downloaded a total of 3100 chunks.

The response message was received from ‘https://xxxxx/replisapi.dll’ and is being processed.

SynchronizeSubscriptionFull:ComErrorException: -2147467259_The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Cause:

To help avoid potential memory problems on Subscriber computers, Web synchronization uses a default maximum size of 100 MB for the XML file used to transfer changes.

Workaround/Solution:

Best option is to look at the amount of data that is being transferred over. Make sure pre-computed partitions is enabled. If this issue still persists than the solution is to do the following:

PLEASE NOTE:

This registry key ONLY works on SQL 2008 and SQL 2008 R2. If your experiencing this issue in SQL 2005 than an upgrade will be necessary.

http://msdn.microsoft.com/en-us/library/ms151255(v=SQL.100).aspx

The limit can be raised by setting the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Replication

WebSyncMaxXmlSize DWORD 2000000

The range of acceptable values for this key is 100 MB to 4GB. The value is specified in KB. Setting this parameter to a high value does not guarantee that you can synchronize that amount of data. The effective limit is constrained by how much contiguous memory is available on the Subscriber computer. If you must have a value larger than 100 MB, we recommend that you increase the value incrementally and test memory consumption with a typical workload on the Subscriber.

The maximum size for the XML file is 4 GB, but replication synchronizes the changes from that file in batches. The maximum batch size of data and metadata is 25 MB. You must ensure that the data in each batch does not exceed approximately 20 MB, which allows for metadata and any other overhead. This limit has the following implications:

· You cannot replicate any column that causes the data and metadata to exceed 25 MB. This might be an issue when you are replicating rows that contain large data types, such as varchar(max).

· If you replicate large volumes of data, you might have to adjust the Merge Agent batch size.

Batch size for merge replication is measured in generations, which are collections of changes per article. The number of generations in a batch is specified by using the –DownloadGenerationsPerBatch and –UploadGenerationsPerBatch parameters of the Merge Agent. For more information, see Replication Merge Agent.

For large volumes of data, specify a small number for each of the batching parameters. We recommend that you start with a value of 10, and then tune based on application needs and performance. Typically, these parameters are specified in an agent profile. For more information about profiles, see Replication Agent Profiles.

This key needs to be added on the IIS server and the server where the merge agent is running.