Implementing a replication agent progess bar

Using Replication Management Objects, SQL Server subscriptions can be synchronized programmatically without using SQL Server Agent or SQL Server Management Studio.  Common uses include:

  • Express edition subscribers
  • Sync from within an application on-demand without an agent job
  • Display agent status information in an application

In 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 Agent progress bar during synchronous execution, handling the MergeSynchronizationAgent.Status event and displaying the results in a Windows Form.

Synchronously synchronize asynchronously

When using the MergeSynchronizationAgent.Synchronize method to synchronize the Merge Agent it is important to realize that this starts the agent synchronously and control remains with the running agent until it completes.  This can make updating UI controls a bit tricky as UI events can be delayed until the Merge Agent finishes synchronizing, which is not very useful.  The key to making this work smoothly is to use a BackgroundWorker to synchronize the agent on a separate thread asynchronously and report progress back to the main UI thread when the MergeSynchronization.Status event is raised.

From here the synchronization BackgroundWorker.DoWork event handler can subscribe to the MergeSynchronizationAgent.Status event and start the Merge Agent synchronously for a specified subscription.

Status Event

The MergeSynchronizationAgent.Status event handler reports the Merge Agent progress passing StatusEventArgs PercentCompleted and Message back to the main UI thread which is handled by the synchronization BackgroundWorker.ProgressChanged event handler.

ProgressChanged Event

Finally, the synchronization BackgroundWorker.ProgressChanged event handler smoothly updates the progress bar and text box controls according to the Merge Agent status.

Sample

This code sample can be downloaded from the MSDN Code Gallery:

If you have any questions about the sample, or would like help integrating this into your application, feel free to ping me or leave a comment below.

-Brandon Williams (blog | linkedin | twitter)

Discuss
12 Comments
  1. 乔艾 says:

    博主的文章很有帮助

  2. [...] Implementing a Replication Agent Progress Bar - Brandon Williams (Blog|Twitter) [...]

  3. Eckard Meyer says:

    I am working through the Sample Code for RMO, to synchronize data on sql express with a publication on sql server;

    I receive an error : Class not Loaded @ this step:

    agent = subscription.SynchronizationAgent;

    What can cause this ?

  4. Vasuki says:

    Hi Brandon,

    After searching on the internet I found your site which does provide the answer to my question – which I have posted in here http://stackoverflow.com/questions/15186548/how-to-get-merge-replication-status-via-c-sharp

    I followed the steps provided in the article and all the methods that are suggested. I am facing this issue.

    1. After a few minutes I get the following error – The merge process could not connect to the Publisher ‘xxxxxxxxxxxxxxx’. Check to ensure that the server is running.

    I am currently using my notebook which acts as both subscriber and publisher but I am still get this error message.

    I am stuck with this issue. The main reason why I most keen on this is to get the current status of the subscription and if the status is not started then I can start the sync and only upon completion I am able to continue with my other task.

    Please HELP!!!

    Kind regards,
    Vasuki

  5. Hi Vasuki,

    The error message “The merge process could not connect to the Publisher…” sounds like a permissions issue with Merge Agent process account. Ensure the account is a user login in the publication database, db_owner in the distribution and subscription databases, is a member of the PAL, and has read permissions on the snapshot share.

    If still no luck, enable verbose agent logging and inspect the log for additional details.

    Also, feel free to contact me offline if you require immediate assistance: http://www.sqlrepl.com/contact/

    • Anuj Sharma says:

      Hi Brandon,

      I am also trying to do merge replication through web sync in SQL server 2008 express edition. I tried both of the solutions provided by you for merge replication using RMO (Console and UI) which you suggested but still i am getting the security related error.

      my Publisher and distribution are on SQL server 2008 R2 Standard edition,
      I created subscription on SQL server 2008 express edition.

      SSL cerificate installed on all the machine (server, hosted server and subscriber machine.)

      here is the error description.

      The upload message to be sent to Publisher ‘PRINTSERVER’ is being generated
      The merge process is using Exchange ID ’0B23EBE2-D502-4D92-A9A1-88B8F738CFFC’ for this web synchronization session.
      A security error occurred

      A security error occurred

      The Merge Agent could not connect to the URL ‘https://ANUJVMWIN7.prospecta.in/WebSyncTest/replisapi.dll’ during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.

      Please help.
      Thanks
      Anuj Sharma

  6. Roberto Guerzoni says:

    I found your article really interesting and I was trying it.
    After resolving some errors I was able to run the program.
    I get this error:

    The initial snapshot for publication ‘VideoRentPublication’ is not yet available. Start the Snapshot Agent to generate the snapshot for this publication. If this snapshot is currently being generated, wait for the process to complete and restart the synchronization.
    Done!

    The remote server is a SQL Developer 2012 to a local SQLExpress 2012

    You have a few suggestions?
    Thanks so much

    Roberto Guerzoni

  7. Hi Roberto,

    You need to generate a snapshot to provide the initial set of data. Right-click your publication -> View Snapshot Agent Status and click Start. Once the snapshot is generated you should be able to sync using your application.

    I hope this helps.

    • Roberto Guerzoni says:

      Thanks for the tip Brandon.
      The solution seemed too obvious to be true and in fact the problem of non-snapshot were performing rights.

      If I may suggest two things to help:
      1 – to configure the snapshot agent run through an administrator account
      2 – make sure that the folder in which you created the snapshot is shared with the client.

      Sincerely

      Roberto Guerzoni

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>