How to troubleshoot SQL Server merge replication problems

This article serves as a basic guide for troubleshooting Microsoft SQL Server merge replication problems. The article provides guidelines and directions to determine the cause and resolution of some merge replication problems. However, this is not a complete list, and some problems may be specific to your environment.

Because of the complexity and distributed nature of merge replication, it is important to isolate the problem in a specific part of a merge replication topology. After you have isolated the problem, you can either correct the problem yourself or contact Microsoft Product Support Services (PSS) for help.

BASIC TROUBLESHOOTING

The following information will help you troubleshoot merge replication agent failures:

Check the Agent history to determine what task failed and the reason for the failure. To view the Agent history, follow these steps:

1. Run SQL Server Enterprise Manager.
2. Expand a SQL Server Group folder, and then expand the Server folder.
3. Expand Replication Monitor.
4. Click to select Merge Agents.
5. To view the history, right-click the appropriate merge publication, and then click Agent History.

Note The steps in Microsoft SQL Server 2005 are different. For more information about how to view the Agent history in SQL Server 2005, read see SQL Server 2005 Books Online.

The majority of merge replication failures involve some kind of errors reported by the Merge Agent. Frequently, the top-level error reported by the agent is not enough to find the root cause of the problem. This top-level error is likely to be followed by a more specific replication error such as a server-side error reported by the SQL Server database engine or by the operating system. Assuming you are not using the merge replication ActiveX controls, you can turn on the Merge Agent output logging facility to obtain extended error information by referring to the steps in the following Microsoft Knowledge Base article:

Some common errors the Merge Agent may experience, and possible solutions to those errors are:

• You may receive this error message:
  Timeout expired.

• You may receive this error message:
  General network error.
  To identify the cause of the error, follow these steps:

1. Make sure that the snapshot folder is shared and that the domain account on which the SQL Server Agent Service is running has permission to access the folder.
2. Set up a Microsoft Network Monitor tool trace to record the activity during the synchronization process. This will give you information about the packet losses or network instability.

• You may receive this error message:
  Could not connect to (%s).

• You may receive the following error message if the configuration of SQL Server replication is over the Internet.:
  The process could not enumerate changes at the '%1' (publisher / subscriber)
  Make sure that the configuration is correct. For more information about configuring a proxy server for SQL Server 7.0 replication over the Internet, visit the following Microsoft Web site:
  
  Configuring Proxy Server for SQL Server 7.0 replication over the Internet (http://msdn2.microsoft.com/en-us/library/aa226169(SQL.70).aspx)
  
• You may receive the following error message:
  The agent is suspect. No response within the last 10 minutes.
  By default, the inactivity threshold is set to 10 minutes. Inactivity threshold is the time for which an agent has not logged any activity and is considered as not functioning. If replication is involved in large transactional volumes, increase the time limit to a large value. This value can be set through the replication monitor refresh rate and settings in SQL Server Enterprise Manager.
  
• SQL Server Enterprise Manager and SQL Server Agent provide a way to monitor events, such as replication agent errors, by using alerts. For more information about Replication alerts, visit the following Microsoft Web site:
  
  Configuring Proxy Server for SQL Server 7.0 replication over the Internet (http://msdn2.microsoft.com/en-us/library/aa237180(SQL.80).aspx)
  
• Problems you experience during replication may occur if data at the Subscriber is not synchronized with data at the Publisher. Microsoft SQL Server 2000 replication can validate the replicated data at a Subscriber to make sure that data at the Subscriber matches data at the Publisher. For more information about validating replicated data, visit the following Microsoft Web site:
  
  Validating replicated data (http://msdn2.microsoft.com/en-us/library/aa178831(SQL.80).aspx)

How to troubleshoot performance problems

A merge replication application must provide the functionality required by the business rules of the application and also complete its operations in a timely manner, even if the volume of data and the number of users increase over time.

Because merge replication is implemented by using tables and queries in Microsoft SQL Server 2000, performance depends on how efficiently SQL Server handles the processing needs of replication. This,in turn, depends on how well the database is designed and tuned to perform. For information about diagnosing and troubleshooting the performance of partition processing and addresses, and for some general database design principles that affect performance, visit the following Microsoft Web site:

Diagnosing and troubleshooting slow partitioned merge processes (http://msdn2.microsoft.com/en-us/library/aa902685(SQL.80).aspx)

You can enhance the performance of the merge replication application. To fine tune merge replication, visit the following Microsoft Web site:

Enhancing merge replication performance (http://msdn2.microsoft.com/en-us/library/aa237440(SQL.80).aspx)

How to troubleshoot replication conflicts

When Publishers and Subscribers are reconnected and synchronization occurs, the Merge Agent detects conflicts and determines what data will be accepted and propagated to other sites. This is based on what resolver was specified when merge publication was implemented. For more information about Merge Replication Conflict Detection and Resolution, visit the following Microsoft Web site:

Merge replication conflict detection and resolution (http://msdn2.microsoft.com/en-us/library/aa256310(SQL.80).aspx)

For more information about the Microsoft Replication Interactive Resolver, visit the following Microsoft Web site:

Microsoft Replication Interactive Resolver (http://msdn2.microsoft.com/en-us/library/aa216919(SQL.80).aspx)

For more information about the Microsoft Replication Conflict Viewer, or the Merge Publication, visit the following Microsoft Web site:

Microsoft Replication Conflict Viewer, Merge Publication (http://msdn2.microsoft.com/en-us/library/aa216918(SQL.80).aspx)

If you want to contact Microsoft Product Support Services (PSS) for assistance, the PSS Support Professional working on your case may request additional information that is specific to your environment. For more information, see the following article in the Microsoft Knowledge Base:


For more information about how merge replication works, visit the following Microsoft Web site:

How merge replication works (http://msdn2.microsoft.com/en-us/library/aa256294(SQL.80).aspx)

For more information about how to plan for merge replication, visit the following Microsoft Web sites:

Planning for merge replication (http://msdn2.microsoft.com/en-us/library/aa237486(SQL.80).aspx)


Merge replication or updatable subscriptions (http://msdn2.microsoft.com/en-us/library/aa256086(SQL.80).aspx)