Synchronize a Push Subscription

Applies to: SQL Server Azure SQL Database

This topic describes how to synchronize a push subscription in SQL Server by using SQL Server Management Studio, replication agents, or Replication Management Objects (RMO).

Note

Azure SQL Managed Instance can be a publisher, distributor, and subscriber for snapshot and transactional replication. Databases in Azure SQL Database can only be push subscribers for snapshot and transactional replication. For more information, see Transactional replication with Azure SQL Database and Azure SQL Managed Instance.

Using SQL Server Management Studio

Subscriptions are synchronized by the Distribution Agent (for snapshot and transactional replication) or the Merge Agent (for merge replication). Agents can run continuously, run on demand, or run on a schedule. For more information about specifying synchronization schedules, see Specify Synchronization Schedules.

Synchronize a subscription on demand from the Local Publications and Local Subscriptions folders in Microsoft SQL Server Management Studio and the All Subscriptions tab in Replication Monitor. Subscriptions to Oracle publications cannot be synchronized on demand from the Subscriber. For information about starting Replication Monitor, see Start the Replication Monitor.

To synchronize a push subscription on demand in Management Studio (at the Publisher)

1. Connect to the Publisher in Management Studio, and then expand the server node.

2. Expand the Replication folder, and then expand the Local Publications folder.

3. Expand the publication for which you want to synchronize subscriptions.

4. Right-click the subscription you want to synchronize, and then click View Synchronization Status.

5. In the View Synchronization Status - <Subscriber>:<SubscriptionDatabase> dialog box, click Start. When synchronization is complete, the message Synchronization completed is displayed.

6. Click Close.

To synchronize a push subscription on demand in Management Studio (at the Subscriber)

1. Connect to the Subscriber in Management Studio, and then expand the server node.

2. Expand the Replication folder, and then expand the Local Subscriptions folder.

3. Right-click the subscription you want to synchronize, and then click View Synchronization Status.

4. A message is displayed about establishing a connection to the Distributor. Click OK.

5. In the View Synchronization Status - <Subscriber>:<SubscriptionDatabase> dialog box, click Start. When synchronization is complete, the message Synchronization completed is displayed.

6. Click Close.

To synchronize a push subscription on demand in Replication Monitor

1. In Replication Monitor, expand a Publisher group in the left pane, expand a Publisher, and then click a publication.

2. Click the All Subscriptions tab.

3. Right-click the subscription you want to synchronize, and then click Start Synchronizing.

4. To view synchronization progress, right-click the subscription, and then click View Details.

Using Replication Agents

Push subscriptions can be synchronized programmatically and on-demand by invoking the appropriate replication agent executable file from the command prompt. The replication agent executable file that is invoked will depend on the type of publication to which the push subscription belongs.

To start the Distribution Agent to synchronize a push subscription to a transactional publication

1. From the command prompt or in a batch file at the Distributor, execute distrib.exe. Specify the following command-line arguments:

   • -Publisher

   • -PublisherDB

   • -Distributor

   • -Subscriber

   • -SubscriberDB

   • -SubscriptionType = 0

   If you are using SQL Server Authentication, you must also specify the following arguments:

   • -DistributorLogin

   • -DistributorPassword

   • -DistributorSecurityMode = 0

   • -PublisherLogin

   • -PublisherPassword

   • -PublisherSecurityMode = 0

   • -SubscriberLogin

   • -SubscriberPassword

   • -SubscriberSecurityMode = 0

     Important

     When possible, use Windows authentication.

To start the Merge Agent to synchronize a push subscription to a merge publication

1. From the command prompt or in a batch file at the Distributor, execute replmerg.exe. Specify the following command-line arguments:

   • -Publisher

   • -PublisherDB

   • -Publication

   • -Distributor

   • -Subscriber

   • -SubscriberDB

   • -SubscriptionType = 0

   If you are using SQL Server Authentication, you must also specify the following arguments:

   • -DistributorLogin

   • -DistributorPassword

   • -DistributorSecurityMode = 0

   • -PublisherLogin

   • -PublisherPassword

   • -PublisherSecurityMode = 0

   • -SubscriberLogin

   • -SubscriberPassword

   • -SubscriberSecurityMode = 0

     Important

     When possible, use Windows authentication.

Examples (Replication Agents)

The following example starts the Distribution Agent to synchronize a push subscription.

  
REM -- Declare the variables.  
SET Publisher=%instancename%  
SET Subscriber=%instancename%  
SET PublicationDB=AdventureWorks2022  
SET SubscriptionDB=AdventureWorks2022Replica   
SET Publication=AdvWorksProductsTran  
  
REM -- Start the Distribution Agent with four subscription streams.  
REM -- The following command must be supplied without line breaks.  
"C:\Program Files\Microsoft SQL Server\120\COM\DISTRIB.EXE" -Subscriber %Subscriber%   
-SubscriberDB %SubscriptionDB% -SubscriberSecurityMode 1 -Publication %Publication%   
-Publisher %Publisher% -PublisherDB %PublicationDB% -Distributor %Publisher%   
-DistributorSecurityMode 1 -Continuous -SubscriptionType 0 -SubscriptionStreams 4  
  

The following example starts the Merge Agent to synchronize a push subscription.

  
REM -- Declare the variables.  
SET Publisher=%instancename%  
SET Subscriber=%instancename%  
SET PublicationDB=AdventureWorks2022  
SET SubscriptionDB=AdventureWorks2022Replica   
SET Publication=AdvWorksSalesOrdersMerge  
  
REM -- Start the Merge Agent.  
REM -- The following command must be supplied without line breaks.  
"C:\Program Files\Microsoft SQL Server\120\COM\REPLMERG.EXE"  -Publisher %Publisher%   
-Subscriber  %Subscriber%  -Distributor %Publisher% -PublisherDB  %PublicationDB%   
-SubscriberDB %SubscriptionDB% -Publication %Publication% -PublisherSecurityMode 1   
-OutputVerboseLevel 3  -Output -SubscriberSecurityMode 1  -SubscriptionType 0   
-DistributorSecurityMode 1  
  

Using Replication Management Objects (RMO)

You can synchronize push subscriptions programmatically by using Replication Management Objects (RMO) and managed code access to replication agent functionalities. The classes that you use to synchronize a push subscription depend on the type of publication to which the subscription belongs.

Note

If you want to start a synchronization that runs autonomously without affecting your application, start the agent asynchronously. However, if you want to monitor the outcome of the synchronization and receive callbacks from the agent during the synchronization process (for example, if you want to display a progress bar), you should start the agent synchronously. For Microsoft SQL Server 2005 Express edition Subscribers, you must start the agent synchronously.

To synchronize a push subscription to a snapshot or transactional publication

1. Create a connection to the Distributor by using the ServerConnection class.

2. Create an instance of the TransSubscription class and set the following properties:

   • The publication database name for DatabaseName.

   • The name of the publication to which the subscription belongs for PublicationName.

   • The name of the subscription database for SubscriptionDBName.

   • The name of the Subscriber for SubscriberName.

   • The connection created in step 1 for ConnectionContext.

3. Call the LoadProperties method to get the remaining subscription properties. If this method returns false, verify that the subscription exists.

4. Start the Distribution Agent at the Distributor in one of the following ways:

   • Call the SynchronizeWithJob method on the instance of TransSubscription from step 2. This method starts the Distribution Agent asynchronously, and control immediately returns to your application while the agent job is running. You cannot call this method if the subscription was created with a value of false for CreateSyncAgentByDefault.

   • Obtain an instance of the TransSynchronizationAgent class from the SynchronizationAgent property, and call the Synchronize method. This method starts the agent synchronously, and control remains with the running agent job. During synchronous execution you can handle the Status event while the agent is running.

To synchronize a push subscription to a merge publication

1. Create a connection to the Distributor by using the ServerConnection class.

2. Create an instance of the MergeSubscription class, and set the following properties:

   • The publication database name for DatabaseName.

   • The name of the publication to which the subscription belongs for PublicationName.

   • The name of the subscription database for SubscriptionDBName.

   • The name of the Subscriber for SubscriberName.

   • The connection created in step 1 for ConnectionContext.

3. Call the LoadProperties method to get the remaining subscription properties. If this method returns false, verify that the subscription exists.

4. Start the Merge Agent at the Distributor in one of the following ways:

   • Call the SynchronizeWithJob method on the instance of MergeSubscription from step 2. This method starts the Merge Agent asynchronously, and control immediately returns to your application while the agent job is running. You cannot call this method if the subscription was created with a value of false for CreateSyncAgentByDefault.

   • Obtain an instance of the MergeSynchronizationAgent class from the SynchronizationAgent property, and call the Synchronize method. This method starts the Merge Agent synchronously, and control remains with the running agent job. During synchronous execution, you can handle the Status event while the agent is running.

Examples (RMO)

This example synchronizes a push subscription to a transactional publication, where the agent is started asynchronously using the agent job.

// Define the server, publication, and database names.
string subscriberName = subscriberInstance;
string publisherName = publisherInstance;
string publicationName = "AdvWorksProductTran";
string subscriptionDbName = "AdventureWorks2022Replica";
string publicationDbName = "AdventureWorks2022";

/// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

TransSubscription subscription;

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

    // Instantiate the push subscription.
    subscription = new TransSubscription();
    subscription.ConnectionContext = conn;
    subscription.DatabaseName = publicationDbName;
    subscription.PublicationName = publicationName;
    subscription.SubscriptionDBName = subscriptionDbName;
    subscription.SubscriberName = subscriberName;

    // If the push subscription and the job exists, start the agent job.
    if (subscription.LoadProperties() && subscription.AgentJobId != null)
    {
        // Start the Distribution Agent asynchronously.
        subscription.SynchronizeWithJob();
    }
    else
    {
        // Do something here if the subscription does not exist.
        throw new ApplicationException(String.Format(
            "A subscription to '{0}' does not exists on {1}",
            publicationName, subscriberName));
    }
}
catch (Exception ex)
{
    // Implement appropriate error handling here.
    throw new ApplicationException("The subscription could not be synchronized.", ex);
}
finally
{
    conn.Disconnect();
}

' Define the server, publication, and database names.
Dim subscriberName As String = subscriberInstance
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksProductTran"
Dim subscriptionDbName As String = "AdventureWorks2022Replica"
Dim publicationDbName As String = "AdventureWorks2022"

' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Dim subscription As TransSubscription

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Instantiate the push subscription.
    subscription = New TransSubscription()
    subscription.ConnectionContext = conn
    subscription.DatabaseName = publicationDbName
    subscription.PublicationName = publicationName
    subscription.SubscriptionDBName = subscriptionDbName
    subscription.SubscriberName = subscriberName

    ' If the push subscription and the job exists, start the agent job.
    If subscription.LoadProperties() And Not subscription.AgentJobId Is Nothing Then
        ' Start the Distribution Agent asynchronously.
        subscription.SynchronizeWithJob()
    Else
        ' Do something here if the subscription does not exist.
        Throw New ApplicationException(String.Format( _
         "A subscription to '{0}' does not exists on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Implement appropriate error handling here.
    Throw New ApplicationException("The subscription could not be synchronized.", ex)
Finally
    conn.Disconnect()
End Try

This example synchronizes a push subscription to a transactional publication, where the agent is started synchronously.

// Define the server, publication, and database names.
string subscriberName = subscriberInstance;
string publisherName = publisherInstance;
string publicationName = "AdvWorksProductTran";
string subscriptionDbName = "AdventureWorks2022Replica";
string publicationDbName = "AdventureWorks2022";

// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

TransSubscription subscription;

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

    // Define the push subscription.
    subscription = new TransSubscription();
    subscription.ConnectionContext = conn;
    subscription.DatabaseName = publicationDbName;
    subscription.PublicationName = publicationName;
    subscription.SubscriptionDBName = subscriptionDbName;
    subscription.SubscriberName = subscriberName;

    // If the push subscription exists, start the synchronization.
    if (subscription.LoadProperties())
    {
        // Check that we have enough metadata to start the agent.
        if (subscription.SubscriberSecurity != null)
        {
            // Synchronously start the Distribution Agent for the subscription.
            subscription.SynchronizationAgent.Synchronize();
        }
        else
        {
            throw new ApplicationException("There is insufficent metadata to " +
                "synchronize the subscription. Recreate the subscription with " +
                "the agent job or supply the required agent properties at run time.");
        }
    }
    else
    {
        // Do something here if the push subscription does not exist.
        throw new ApplicationException(String.Format(
            "The 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.", ex);
}
finally
{
    conn.Disconnect();
}

' Define the server, publication, and database names.
Dim subscriberName As String = subscriberInstance
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksProductTran"
Dim subscriptionDbName As String = "AdventureWorks2022Replica"
Dim publicationDbName As String = "AdventureWorks2022"

' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Dim subscription As TransSubscription

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Define the push subscription.
    subscription = New TransSubscription()
    subscription.ConnectionContext = conn
    subscription.DatabaseName = publicationDbName
    subscription.PublicationName = publicationName
    subscription.SubscriptionDBName = subscriptionDbName
    subscription.SubscriberName = subscriberName

    ' If the push subscription exists, start the synchronization.
    If subscription.LoadProperties() Then
        ' Check that we have enough metadata to start the agent.
        If Not subscription.SubscriberSecurity Is Nothing Then

            ' Synchronously start the Distribution Agent for the subscription.
            subscription.SynchronizationAgent.Synchronize()
        Else
            Throw New ApplicationException("There is insufficent metadata to " + _
             "synchronize the subscription. Recreate the subscription with " + _
             "the agent job or supply the required agent properties at run time.")
        End If
    Else
        ' Do something here if the push subscription does not exist.
        Throw New ApplicationException(String.Format( _
         "The subscription to '{0}' does not exist on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Implement appropriate error handling here.
    Throw New ApplicationException("The subscription could not be synchronized.", ex)
Finally
    conn.Disconnect()
End Try

This example synchronizes a push subscription to a merge publication, where the agent is started asynchronously using the agent job.

// Define the server, publication, and database names.
string subscriberName = subscriberInstance;
string publisherName = publisherInstance;
string publicationName = "AdvWorksSalesOrdersMerge";
string subscriptionDbName = "AdventureWorks2022Replica";
string publicationDbName = "AdventureWorks2022";

// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

MergeSubscription subscription;

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

    // Define push subscription.
    subscription = new MergeSubscription();
    subscription.ConnectionContext = conn;
    subscription.DatabaseName = publicationDbName;
    subscription.PublicationName = publicationName;
    subscription.SubscriptionDBName = subscriptionDbName;
    subscription.SubscriberName = subscriberName;

    // If the push subscription and the job exists, start the agent job.
    if (subscription.LoadProperties() && subscription.AgentJobId != null)
    {
        // Start the Merge Agent asynchronously.
        subscription.SynchronizeWithJob();
    }
    else
    {
        // Do something here if the subscription does not exist.
        throw new ApplicationException(String.Format(
            "A subscription to '{0}' does not exists on {1}",
            publicationName, subscriberName));
    }
}
catch (Exception ex)
{
    // Implement appropriate error handling here.
    throw new ApplicationException("The subscription could not be synchronized.", ex);
}
finally
{
    conn.Disconnect();
}

' Define the server, publication, and database names.
Dim subscriberName As String = subscriberInstance
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim subscriptionDbName As String = "AdventureWorks2022Replica"
Dim publicationDbName As String = "AdventureWorks2022"

' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Dim subscription As MergeSubscription

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Define push subscription.
    subscription = New MergeSubscription()
    subscription.ConnectionContext = conn
    subscription.DatabaseName = publicationDbName
    subscription.PublicationName = publicationName
    subscription.SubscriptionDBName = subscriptionDbName
    subscription.SubscriberName = subscriberName

    ' If the push subscription and the job exists, start the agent job.
    If subscription.LoadProperties() And Not subscription.AgentJobId Is Nothing Then
        ' Start the Merge Agent asynchronously.
        subscription.SynchronizeWithJob()
    Else
        ' Do something here if the subscription does not exist.
        Throw New ApplicationException(String.Format( _
            "A subscription to '{0}' does not exists on {1}", _
            publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Implement appropriate error handling here.
    Throw New ApplicationException("The subscription could not be synchronized.", ex)
Finally
    conn.Disconnect()
End Try

This example synchronizes a push subscription to a merge publication, where the agent is started synchronously.

// Define the server, publication, and database names.
string subscriberName = subscriberInstance;
string publisherName = publisherInstance;
string publicationName = "AdvWorksSalesOrdersMerge";
string subscriptionDbName = "AdventureWorks2022Replica";
string publicationDbName = "AdventureWorks2022";

// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

MergeSubscription subscription;

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

    // Define the subscription.
    subscription = new MergeSubscription();
    subscription.ConnectionContext = conn;
    subscription.DatabaseName = publicationDbName;
    subscription.PublicationName = publicationName;
    subscription.SubscriptionDBName = subscriptionDbName;
    subscription.SubscriberName = subscriberName;

    // If the push subscription exists, start the synchronization.
    if (subscription.LoadProperties())
    {
        // Check that we have enough metadata to start the agent.
        if (subscription.SubscriberSecurity != null)
        {
            // Synchronously start the Merge Agent for the subscription.
            subscription.SynchronizationAgent.Synchronize();
        }
        else
        {
            throw new ApplicationException("There is insufficent metadata to " +
                "synchronize the subscription. Recreate the subscription with " +
                "the agent job or supply the required agent properties at run time.");
        }
    }
    else
    {
        // Do something here if the push subscription does not exist.
        throw new ApplicationException(String.Format(
            "The 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.", ex);
}
finally
{
    conn.Disconnect();
}

' Define the server, publication, and database names.
Dim subscriberName As String = subscriberInstance
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim subscriptionDbName As String = "AdventureWorks2022Replica"
Dim publicationDbName As String = "AdventureWorks2022"

' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Dim subscription As MergeSubscription

Try
    ' Connect to the Publisher
    conn.Connect()

    ' Define the subscription.
    subscription = New MergeSubscription()
    subscription.ConnectionContext = conn
    subscription.DatabaseName = publicationDbName
    subscription.PublicationName = publicationName
    subscription.SubscriptionDBName = subscriptionDbName
    subscription.SubscriberName = subscriberName

    ' If the push subscription exists, start the synchronization.
    If subscription.LoadProperties() Then
        ' Check that we have enough metadata to start the agent.
        If Not subscription.SubscriberSecurity Is Nothing Then
            ' Synchronously start the Merge Agent for the subscription.
            ' Log agent messages to an output file.
            subscription.SynchronizationAgent.Output = "mergeagent.log"
            subscription.SynchronizationAgent.OutputVerboseLevel = 2
            subscription.SynchronizationAgent.Synchronize()
        Else
            Throw New ApplicationException("There is insufficent metadata to " + _
             "synchronize the subscription. Recreate the subscription with " + _
             "the agent job or supply the required agent properties at run time.")
        End If
    Else
        ' Do something here if the push subscription does not exist.
        Throw New ApplicationException(String.Format( _
         "The subscription to '{0}' does not exist on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Implement appropriate error handling here.
    Throw New ApplicationException("The subscription could not be synchronized.", ex)
Finally
    conn.Disconnect()
End Try

Related content

• Replication Management Objects Concepts
• Synchronize Data
• Replication Security Best Practices