Synchronizing subscriptions in SQL Server Express

I was recently asked about synchronizing a Merge pull subscription in SQL Server Express using RMO, the lack of a SQL Server Agent, and what to do about it.  Since SQL Server Express does not include the SQL Server Agent, pull subscriptions must be synchronized by some other means.  The problem becomes evident when trying to open the View Synchronization Status dialog for a pull subscription in SQL Server Express.

View Synchronization Status

The View Synchronization Status dialog depends on a SQL Server Agent job which does not exist in SQL Server Express.  To accommodate for the lack of a SQL Server Agent, SQL Server Express Merge pull subscriptions can also be synchronized by executing batch scripts, Windows Synchronization Manager, or Replication Management Objects (RMO).  While not optimal without the SQL Server Agent, plenty of options are available. This is where it really pays to know the different ways a subscription can be synchronized when tasked with an Express subscriber.

Batch script

The Merge Agent (replmerg.exe) executable can be run from the command line on-demand or from a batch script as a scheduled task.  To run from the command line, execute replmerg.exe from the COM folder.

replmerg.exe

Likewise, this can be saved as a batch file and run from Task Scheduler on a schedule.  This option alone provides a pretty good replacement for the SQL Server Agent and synchronizing subscriptions in Express.

Windows Synchronization Manager

Another option to synchronize pull subscriptions in SQL Server Express is Windows Synchronization Manager, or Sync Center.  Sync Center provides options for setting and viewing sync partnerships, setting subscription properties and sync schedules, and viewing sync results and conflicts.  This tool is ideal for having non-technical users synchronize SQL Server Express pull subscriptions on-demand as it offers a user-friendly interface.  Synchronization schedules can also be configured as needed.

Windows Synchronization Manager

RMO

For all the developers, Replication Management Objects (RMO) can be used to synchronize SQL Server Express Merge pull subscriptions through managed code access.  The RMO MergeSynchronizationAgent class exposes a Synchronize method which can be used to synchronize a subscription without an agent job — additional properties must be supplied.  A code sample demonstrating how to synchronize a SQL Server Express Merge pull subscription using RMO can be downloaded from the MSDN Code Gallery.

So

As we can see, there are options for synchronizing pull subscriptions in SQL Server Express.  That is because Replication was built with SQL Server Express subscribers in mind.  Pull subscriptions can be synchronized using batch scripts, Windows Synchronization Manager, and RMO which should be sufficient enough to synchronize the data.  If you happen to know of another way to synchronize SQL Server Express pull subscriptions and would like to share, feel free to leave a comment below.

Replication Agent Logging

To troubleshoot replication agent failures, replication agent profiles can be configured to output verbose agent logs to a text file.  All replication agents contain the parameters -Output output_path_and_file_name and -OutputVerboseLevel [0|1|2], which when specified, output the agent log to the file output_path_and_file_name and will contain varying degrees of information depending on the verbose level passed.  In general, the information contained in the verbose agent logs consists of progress report messages, error messages, procedure calls, and various session statistics.

Depending on the verbose level and the agent, some or all of this information will be provided.  For the purpose of troubleshooting replication agent failures, a verbose level of 2 should be specified, which prints all error and progress report messages.  It might also be worth mentioning that some agents can be passed undocumented output verbose levels 3 and 4 which provide even more information.  The information provided in the verbose agent logs can be useful when trying to diagnose and identify exactly where replication is failing as well as get detailed information as to the root cause of the failure.  In addition, the verbose agent logs are likely the first piece of diagnostic data that Microsoft Support will ask for when diagnosing a replication support incident.

Reading the logs takes a little getting used to but after utilizing them to troubleshoot a few times, it becomes second nature.  I often use them when replication failures are occurring and the error given in Replication Monitor and the View Synchronization Status window isn’t sufficient enough to diagnose the issue.  An example error message with Merge Replication that occasionally crops up is: The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200963), which isn’t very verbose.  Knowing how to output the verbose agent logs is a smart skill to have under these circumstances in order to identify the root cause.

The parameters to output the verbose agent logs can be passed to the Run Agent job step in the Job Properties dialog, Agent Profile dialog in Replication Monitor, via RMO, or by way of invoking the agents from the command line.  After adding the parameters, the replication agent must be restarted for the profile changes to take effect.  Once restarted, the replication agent will begin outputting the verbose log to the path and file name specified.  After reproducing the failure, the log can then be examined for further information.

The verbose agent log is outputted in the form of a text document and can be opened with your favorite text editor.  The -OutputVerboseLevel specified for the agent profile will dictate the contents of the log.  Typically, if a replication agent failure occurs, it will fail and the process will stop.  That means that the failure is likely to be found at the end of the log, in the last 20 or so lines in the output file.

Be sure to remove the agent parameters after reproducing the failure and collecting the log.  Not doing so will result in the log to be appended to on subsequent synchronizations and consume disk space.  Outputting agent logs can also impact replication performance and thus isn’t recommended to do unless troubleshooting replication failures.

Applying a Snapshot -AltSnapshotFolder Parameter

Did you know a snapshot can be applied using the Distribution and Merge Agent –AltSnapshotFolder parameter?  This means that after generating a snapshot, it can be copied to Subscribers manually using removable media for instance, and applied locally.  This can be useful with large snapshots and when replicating over slow, unreliable links.

This method of initialization can be performed by adding the -AltSnapshotFolder parameter to the agent job step, using the agent programming interfaces, or when invoking agent executables from the command prompt.  For more information on the –AltSnapshotFolder parameter, refer to Replication Distribution Agent and Replication Merge Agent in the MSDN Library.