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.

Discuss
8 Comments
  1. Jesse Monk says:

    We use a combination of Windows Sync Manager and the RMO Merge Synchronization Agent. Our published articles include only tables with partitions based on machine name to minimize data transfer. All other objects are compiled in scripts and located in a dbversion table and then called by the application layer if versions are out synch with the publisher. So, users are able to manually synchronize via Windows or, the application will automatically synchronize every 5 minutes via RMO. This allows for deployment of the application during machine setup and a seamless interface for users in the field.

  2. Jesse, that is very slick using a combination of both WSM and RMO. I also like how you’re filtering with HOST_NAME() to minimize data transfer. I would guess this also minimizes the chances of conflicts occurring.

  3. Prashanth says:

    Hi, I am trying to capture the output generated by the sync agent and display it in the UI in a windows form. Its showing the output when i set the outputverboselevel but when i tried to capture the Status event, its not. do you have any sample to share to capture this output?

    thanks a lot in advance

  4. Hi Prashanth – Yes, I will post a sample on the MSDN Code Gallery showing how to handle the Status event while the agent is running. I will post the link here once it is up.

  5. I’ve posted a sample on the MSDN Code Gallery demonstrating how to handle the MergeSynchronizationAgent.Status event and display the percent completed and agent messages in a progress bar and auto-scroll multiline textbox. I did my best to mimic the View Synchronization Status dialog. The sample can be found here: http://code.msdn.microsoft.com/SQL-Server-Express-Merge-4e01e119

  6. Prashanth – I’ve updated the project to include the sample in both C# and VB.NET. Let me know if you have any questions.

  7. [...] a previous post I provided a link to a code sample showing how to synchronize a Merge pull subscription in SQL Server Express using RMO.  Taking this a step further, for this post I will discuss how to implement a Merge [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>