How to use Merge Replication Centralized Logging Feature

Eric Lucht | Microsoft SQL Server Escalation Services

Centralized troubleshooting refers to the ability to generate detailed Merge agent log files on both client and Web server (for Web Synchronization), and gather the log files into a central location on the publisher as part of the sync process, no matter where the reconciler is run.  When this is enabled:

• The log files are generated by default at a minimum logging level
• Replmerg.log is created in the 100\COM directory when the Merge agent is run.
• If using Web Sync, a websync.log is also generated in the virtual directory with the minimum logging.
• If using ActiveX, then SQLMergex.log is created.
• At the end of the reconciliation, the log file for that sync is automatically uploaded to the publisher and stored in a table in a varbinary (max) column.

For background information on Merge Agent logging options see BLOG posting: How to change the reporting level for the Merge Replication ReplMerg.log

This BLOG posting covers the stored procedures that enable Centralized logging feature.

Supportability Modes

Centralized logging feature makes use of supportability mode to determine the kind of logging and data collection that will be done. Supportability modes are specific to each subscriber, and they can be set for individual subscribers by calling certain administrative stored procedures.

The following are the different supportability modes supported by the centralized logging infrastructure

Enabling Supportability Modes

Let’s look at Exercise 1 from the scenario and see what happens when we add a supportability setting for the subscriber

1. Create a publication on a database in Server A.

2. Create a pull subscription on Server B for this publication (set the agent to run “On Demand;” this makes it easier to observe the behavior).

3. Check the 100\COM folder on the subscriber and make sure there is no existing replmerg.log. If there is one, delete it.

4. Add a supportability setting for this subscriber by calling sp_addmergelogsettings with the appropriate parameters, for example, if we take the ‘TradingPub’ scenario.

When this stored procedure is executed on the published database, it adds a row into the MSMerge_log_settings table, which will be used later by merge agent to determine what kind of logging needs to be done.

5. Run the Merge agent.

What difference do you notice with the logging? What files are created, and where are they created?

A few key things happen when you enable supportability mode for a subscriber:

1. A new log file is created in the directory specified in the sp_addmergelogsettings procedure @log_file_path=’C:\Logs’, @log_file_name=’replsupportlog.txt’ at the severity level mentioned.

2. At the end of the sync, the log file is uploaded to the publisher, loaded into the table Msmerge_log_files, and the log is stored in a varbinary(Max) column.

3. The replmerg.log in the 100\COM folder is still created, but the logging is stopped in that file and continued in the above file. The last entry in the replmerg.log will indicate this fact.

Here is the replmerg.log in 100\COM:

Here is the replsupportlog.txt created in the c:\log directory. Notice that this file is a continuation from the replmerg.log. If supportability mode was not specified, all the information below would have been included in replmerg.log.

Uploading the file to the publisher

At the end of the sync, the above file is automatically uploaded to the publisher database, and an entry is added to the Msmerge_log_files table where this file is stored in a varbinary(max) column.

So if you query the table Msmerge_log_files, you will see new entries being added at the end of each sync, depending on the @support_options value.

What is the purpose of uploading the file?

The idea behind this is to have a central repository for all the log files, and this can be particularly useful if there are a large number of subscribers in the topology.

Once the file has been uploaded to the table, it can be used for further analysis.

The following is the process for reading the data out of the varbinary (max) column: (yes it is a bit messy)

Take a look at temp1.log. What information do you see? The replsupportlog.txt.

Important: Now that you have a fixed length text file, you can load this data into an analysis database and run analysis queries.

When to use centralized logging and replmerg.log

When facing a Merge replication problem the above logs should provide you with an idea of what the problem is and where to start looking.

Once you have a fair idea about the problem from the above logs, determine if you need the logging using the centralized troubleshooting, and determine the level required based on the complexity of the problem, frequency of occurrence, etc.

A good guideline for using the highest level of centralized logging is if the problem is reproducible consistently. If that is the case, enable centralized logging at the highest level and get the data and disable the logging immediately so that there is no performance impact.

On the contrary, if the issue is not reproducible and is a random occurrence, you might want to start off with enabling centralized logging at a lower level of logging, and see if that data provides enough information to troubleshoot the issue.

Edited and Posted by: Chris Skorlinski