Statusbar for replication

Question

Hi,

I am using Pull Subscription to do SQL merge replication. I know we can monitor the progress of merge in the Replication Monitor on the SQL server itself.

How to show the progress/estimated time to the user in the application.

Please note the code i am using below.

 Dim subscription As MergePullSubscription

        subscriberConn = New ServerConnection(_SubscriberName)

        Try
      
            OpenSubscriberConn()

      
            subscription = New MergePullSubscription()
            subscription.ConnectionContext = subscriberConn ' conn
            subscription.PublisherName = _PublisherName
            subscription.PublicationName = _PublicationName
            subscription.PublicationDBName = _PublicationDBName

            subscription.UseWebSynchronization = True
            subscription.InternetUrl = _InternetURL  ' "https://nafdabi.testing.com.au/Replication/replisapi.dll" ' webSyncUrl"

      
            If _Settings.wsUseDefaultCredentials = True Then
                subscription.InternetSecurityMode = AuthenticationMethod.WindowsAuthentication
            Else
      
                subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
      
                subscription.InternetLogin = _InternetLogin ' 
                subscription.InternetPassword = _InternetPassword ' 

            End If


            subscription.CreateSyncAgentByDefault = True

            subscription.DatabaseName = _SubscriptionDBName
            subscription.SubscriberType = MergeSubscriberType.Anonymous
            subscription.HostName = _HostName '"PARNB02" ' GlobalBranchID

            If subscription.LoadProperties() Then
                agent = subscription.SynchronizationAgent
                agent.FileTransferType = FileTransferOption.Ftp

                agent.UseWebSynchronization = True
                '**********************************
                agent.InternetUrl = _InternetURL ' "https://nafdabi.testing.com.au/replication/replisapi.dll" 'webSyncUrl


                If _Settings.wsUseDefaultCredentials = True Then
                    agent.InternetSecurityMode = SecurityMode.Integrated
                Else


                    agent.InternetSecurityMode = SecurityMode.Standard
                    agent.InternetLogin = _InternetLogin ' "testing\asaunders" ' winLogin
                    agent.InternetPassword = _InternetPassword '"asdf1234" 'winPassword

                    '***************************************
                    ' Settings for using a Proxy
                    '***************************************
                    If _Settings.UseProxyServer Then
                        agent.InternetProxyServer = _Settings.ProxyServerAddress & ":" & _Settings.ProxyServerPort
                        agent.InternetProxyLogin = _Settings.ProxyServerUsername
                        agent.InternetProxyPassword = _Settings.ProxyServerPassword
                    End If
                    '***************************************


                End If


                agent.InternetTimeout = 10000
                '**********************************
                Dim bMustReInit As Boolean = False
                Dim RedoCtr As Integer = 0
RedoInit:
                RedoCtr += 1
                Try
                    agent.Synchronize()
                Catch ex As Microsoft.SqlServer.Replication.ComErrorException
                    '            'Throw New ApplicationException([String].Format("A subscription to '{0}' does not exist on {1}", publicationName, subscriberName))
                    If ex.Message = [String].Format("Publication '{0}' does not exist.", _PublicationName) Then
                        _Success = False
                        _Response = ex.Message
                        _PublicationDoesNotExist = True
                    End If
                    If InStr(ex.Message.ToUpper, "YOU MUST REINITIALIZE") >= 1 Then
                        subscription.Reinitialize(False)
                        If RedoCtr <= 4 Then
                            bMustReInit = True
                        Else
                            _Success = False
                            _Response = ex.Message
                        End If
                        '>>D426
                    Else
                        _Response = ex.Message
                        '<<D426
                    End If

                Catch ex As Exception
                    'MsgBox(ex.Message & If(Not IsNothing(ex.InnerException), vbCrLf & ex.InnerException.Message, ""))
                    _Success = False
                    _Response = "2) " & ex.Message & If(Not IsNothing(ex.InnerException), vbCrLf & ex.InnerException.Message, "")
#If DEBUG Then
                    MsgBox("clsMergeRepl.Create_and_ExecutePull (EXCEPTION) 1 :" & vbCrLf & _
                           ex.Message & _
                           If(Not IsNothing(ex.InnerException), vbCrLf & ex.InnerException.Message, "") & _
                           ex.StackTrace)
#End If
                End Try

                If bMustReInit = True Then
                    GoTo RedoInit
                End If

            Else

                Try
                    subscription.Create()
                    Create_and_ExecutePull()
                Catch ex As Exception

#If DEBUG Then
                    MsgBox("clsMergeRepl.Create_and_ExecutePull (EXCEPTION) 2:" & vbCrLf & _
                           ex.Message & _
                           If(Not IsNothing(ex.InnerException), vbCrLf & ex.InnerException.Message, "") & _
                           ex.StackTrace)
#End If

                    Throw New ApplicationException("The subscription could not be created. Verify that the subscription has been defined correctly.", ex)
                End Try

            End If
        Catch ex As Exception
            MsgBox("1) " & ex.Message & If(Not IsNothing(ex.InnerException), vbCrLf & ex.InnerException.Message, ""))

Answer 1

Per Programmatically Monitor Replication (RMO Programming) we can create an instance of the MergeSubscriberMonitor class and call the GetLastSessionSummary method which returns an array of MergeSessionSummary objects containing a PercentComplete property which gets the progress of a running session, as a percentage.  This percentage can be used for a status bar.

---

Brandon Williams (blog | linkedin)

Answer 2

Actually, I see you are synchronizing synchronously using the MergeSynchronizationAgent.Synchronize method.  During synchronous execution you can handle the MergeSynchronizationAgent.Status event while the agent is running to obtain the percent completed.

For example, before calling the MergeSynchronizationAgent.Synchronize method, do this:

agent.Status += new AgentCore.StatusEventHandler(agent_Status);

Then in the agent_Status event handler the progress bar value can be set like this:

private void agent_Status(object sender, StatusEventArgs e)
{
    myProgressBar.Value = e.PercentCompleted;
}

This way when MergeSynchronizationAgent.Synchronize is called the progress bar will be increment accordingly.

---

Brandon Williams (blog | linkedin)

Answer 3

I have checked the function GetSessionSummary() of the MergeSubscriberMonitor class.

It does not gives the estimated time remaining for the synch to complete.

I only find the message a bit helpful,but the percentage complete is not returned , also time remaining is always zero.

as stated before, I do not get the agent.Status ....i mean .status is not available..........

Thanks for replying

Shalini

Answer 4

If agent is of the type MergeSynchronizationAgent then a Status event is available to handle.

---

Brandon Williams (blog | linkedin)

Answer 5

I was able to get this working by handling the MergeSynchronizationAgent.Status event with the following code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;

// These namespaces are required.
using Microsoft.SqlServer.Replication;
using Microsoft.SqlServer.Management.Common;

namespace ReplicationStatusBar
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            pictureBoxStatus.Visible = true;
            pictureBoxStatus.Enabled = true;

            SynchronizeMergePullSubscriptionViaRMO();
        }

        public void SynchronizeMergePullSubscriptionViaRMO()
        {
            // Define the server, publication, and database names.
            string subscriberName = "WIN8CP\\SQLEXPRESS";
            string publisherName = "WS2008R2_1";
            string distributorName = "WS2008R2_1";
            string publicationName = "TestMergePub2";
            string subscriptionDbName = "TestSubDB1";
            string publicationDbName = "AdventureWorksLT";

            // Create a connection to the Subscriber.
            ServerConnection conn = new ServerConnection(subscriberName);

            MergePullSubscription subscription;
            MergeSynchronizationAgent agent;

            try
            {
                // Connect to the Subscriber.
                conn.Connect();

                // Define the pull subscription.
                subscription = new MergePullSubscription();
                subscription.ConnectionContext = conn;
                subscription.DatabaseName = subscriptionDbName;
                subscription.PublisherName = publisherName;
                subscription.PublicationDBName = publicationDbName;
                subscription.PublicationName = publicationName;

                // If the pull subscription exists, then start the synchronization.
                if (subscription.LoadProperties())
                {
                    // Get the agent for the subscription.
                    agent = subscription.SynchronizationAgent;

                    // Set the required properties that could not be returned
                    // from the MSsubscription_properties table.
                    agent.PublisherSecurityMode = SecurityMode.Integrated;
                    agent.DistributorSecurityMode = SecurityMode.Integrated;
                    agent.Distributor = publisherName;

                    // Enable verbose merge agent output to console.
                    agent.OutputVerboseLevel = 4;
                    agent.Output = "C:\\TEMP\\mergeagent.log";

                    // Handle the Status event
                    agent.Status += new AgentCore.StatusEventHandler(agent_Status);

                    // Synchronously start the Merge Agent for the subscription.
                    agent.Synchronize();
                }
                else
                {
                    // Do something here if the pull subscription does not exist.
                    throw new ApplicationException(String.Format(
                        "A subscription to '{0}' does not exist on {1}",
                        publicationName, subscriberName));
                }
            }
            catch (Exception ex)
            {
                // Implement appropriate error handling here.
                throw new ApplicationException("The subscription could not be " +
                    "synchronized. Verify that the subscription has " +
                    "been defined correctly.", ex);
            }
            finally
            {
                conn.Disconnect();
            }
        }

        public void agent_Status(object sender, StatusEventArgs e)
        {
            pbStatus.Value = Convert.ToInt32(e.PercentCompleted);
            tbLastStatusMessage.Text = e.MessageStatus.ToString();
        }
    }
}

---

Brandon Williams (blog | linkedin)

Answer 6

Thanks for your Response Brandon.

This sample only tells the synch is InProgress, but does not tells the "time remaining" or the download "percentage complete".

Thanks

Answer 7

Actually, the StatusEventArgs Class exposes a PercentCompleted property which is what I use to set the ProgressBar Value.  This is in my code sample.

---

Brandon Williams (blog | linkedin)