Snapshot replication on a slow connection

Publisher: SQL 2005 standard

Subscriber: SQL 2005 Standard

Merge replication (Pull)

DB size at Publisher is about 6GB. Snapshot file is about 400MB.

Hi, We have a total of 8 subscribers, 7 of which are all currently synchronizing fine every 30mins. 1 of the sites is on a slow connection and I cannot get the tables initialized. About 1 hour into the replication there will be a very slight (<3sec) network outage, and the replication job fails.

Error Message: The Merge Agent failed to enumerate retry information. This can occur when there is too much activity on the tempdb system database. Increase the -QueryTimeOut parameter and restart the synchronization.

I have set the Query Timeout to '0', but the situation is still the same. Is there anyway, I can make the initialization persistent to overcome bad network connection.

more ▼

asked Feb 08, 2012 at 03:08 AM in Default

Jay_SQL gravatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

The slow network is really hard to deal with. There are some general guidelines, but what combination would suit you depend upon your environment.

Consider setting lower values for the following Merge Agent parameters: -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch, -SrcThreads and -DestThreads. Lower values for -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch can decrease throughput, but can result in a smaller number of retries over an unreliable network. These parameters are specified in the agent profile titled slow link and can also be specified on the agent command line. In addition to setting these parameters, consider using Web synchronization, which typically uses fewer network roundtrips. For more information, see [Web Synchronization for Merge Replication][1].

If the network connection is highly latent but is reliable, consider using the default values for -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch, and specifying higher values for -LoginTimeOut and -QueryTimeOut.

[1]: http://technet.microsoft.com/en-us/library/ms151763(SQL.90).aspx
more ▼

answered Feb 08, 2012 at 05:44 AM

robbin gravatar image

1.6k 1 3 5

Thanks for your pointers Robbin.

After spending the last few hours with eyes glued to the screen, I can see that the replication goes as far as to finish bulk copying the various tables into DB.

Then it starts on one "Downloading changes to table...." and encounters an error like, "The merge process is retrying a failed operation made to article 'xyz' - Reason: The merge agent was unable to synchronize the row due to one or more unanticipated errors in the batch of changes. ..."

And during the network timeout period of a few seconds, I notice that the sync actually continues once the connection is available again.

I wonder if this is not really a network connection / timeout issue, or rather one that is based on the quality os the transferred snapshot... Could it be a problem with the snapshot itself?

Thanks again.
Feb 08, 2012 at 08:21 AM Jay_SQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 08, 2012 at 03:08 AM

Seen: 1651 times

Last Updated: Feb 08, 2012 at 09:11 AM