SQL Server : Merge replication fails due to timeout errors  

Solution for : Merge replication fails due to timeout errors

QUESTION:

You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN).

Many of the tables involved in replication use the XML and varchar (max) data types.Occasionally, merge replication fails due to timeout errors.

You need to reduce the occurrence of these timeout errors. What should you do?

Answer:

When you synchronize data rows with a large amount of data, such as rows with LOB columns, Web synchronization can require additional memory allocation and hurt performance. This occurs when the Merge Agent generates an XML message that contains too many data rows with large amounts of data. If the Merge Agent is consuming too many resources during Web synchronization, reduce the number of rows sent in a single message in one of the following ways:
  • Use the slow link agent profile for the Merge Agent.
  • Decrease the -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch parameters for the Merge Agent to a value of 10 or less. The default value of these parameters is 50.
Note: Merge Agent has a “slow link” profile designed for low bandwidth connections.

How to allow merge agent to use “slow link profile” : Change Existing Agents: 

Select a profile (On the General page of the Distributor Properties – <Distributor> dialog box, click Profile Defaults), and then click Change Existing Agents to specify that all existing jobs for an agent of a given type should use the selected profile. For example, if you have created a number of subscriptions to a merge publication, and you want to change the profile to specify that the Merge Agent job for each of these subscriptions should use the Slow link agent profile, select that profile, and then click Change Existing Agents.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s