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.

Hope this will help you .

Brgds,

Chhavinath Mishra

Microsoft Certified IT Professional (MCITP)

SQL Server : Remove/Delete Articles from Transactional Replication in SQL Server 2008/R2

While working on a project ,I've come across situations where I had to remove article from replication. AS soon as you remove the articles from publication,Replication will stop replicating the changes in data from publisher to subscriber but it will not remove tables from the subscriber database, you have to do it manually after removing object from replication. Also wish to highlight that if there are dependency on tables which you would like to delete could not be dropped unless dependency is removed or dependent tables are dropped. 

Please follow below mentioned steps to remove articles from transactional replication. 

1.) On publisher server, Navigate to replication option under your SSMS and move to the publication, right click on publication and click on “Properties” : 

2.) On Publication Properties dialog box, go to “Articles” option from the menu in left hand side and uncheck the checkbox of article you want to remove from replication. click on “ OK” button. Same can be used if you would like to stop replication. For this, uncheck all check-boxes which will ensure that no data will be replicated.

3.) Once you will click on OK button from Publication Properties dialog box above, replication will stop populating new data change to the underlying table but now I want to update my snapshot of replication so right click on “Replication” option and click on “Launch Replication Monitor” option from the popup menu.

4.) From the replication monitor, you can find “Agent” tab under your publication, you have to right click on “Snapshot Agent” and click on “Start Agent” from popup menu. This action will update snapshot which takes time based on the size of database.



All these four steps will successfully remove articles from replication now if you wish; you can manually go to subscriber database and drop objects which you have removed from replication. 

Thanks/

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : could not connect to the Distributor using the specified password - Transactional Replication

While working on a project to configure Transactional replication on Production database as a reporting solution, where requirement was to setup remote distributor (in my case: On subscriber server), I was able to successfully configure the distributor but when I was trying to configure the publication using remote distributor, I faced below mentioned error and that was causing me to move forward.

As I found the solution so it forces me to share with all if this can save you in critical time if you are in trouble due to this error.

Error: SQL Server could not connect to the Distributor using the specified password.

By seeing error message , it seems that it is simple error and perhaps I mentioned wrong password but I was sure that this is not the case.

There are couple of things which should be validated in order to resolve this issue and last option C worked for me.

A) I have tried changing password for distributor account by following below mentioned steps but this also didn't help me as expected.

1- Connect to the server where your distribution db resides
2- Right click on the replication folder and select distributor
properties.
3- Select publishers. If you have already entered your publisher
uncheck and select OK
4- Add a new publisher
5- Enter a password for the administrative link
6- Connect to your publishing server and and create a new publication
using the remote distributor.
7- Enter the password you used earlier when assigning the publisher in
the distribution properties

B) Check if the remote access parameter in sp_configure is enabled on the Publisher. In my case it was already enabled so no luck this time as well. Finally I tried the option C and that worked for me like a magic.

C) Check if SQL Server is running in fiber mode (Check if ‘Lightweight Pooling’  parameter in sp_configure is enabled on all involved instances) . In my case it was so I turned off ‘Lightweight Pooling’ parameter and issue was resolved.

P.S: SQL Server can run in one of two modes: thread mode or fiber mode. By default, SQL Server runs in thread mode in which a SQL Server worker is associated with a Windows thread throughout all phases of its execution. This can be changed with the sp_configure option ‘Lightweight Pooling’. When Lightweight Pooling is turned on, SQL Server runs in fiber mode in which a SQL Server worker is associated with a user-mode Windows construct called fiber. Switching among fibers is handled in the user mode with the objective of reducing the cost of calling into the kernel for context switches.

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)