同步推送订阅

2017-06-13

本主题介绍如何使用 SQL Server Management Studio、复制代理或复制管理对象（RMO）在 SQL Server 2014 中同步推送订阅。

使用 SQL Server Management Studio

订阅由分发代理（用于快照和事务复制）或合并代理（用于合并复制）同步。代理可以连续运行、按需运行或按计划运行。有关指定同步计划的详细信息，请参阅 “指定同步计划”。

从Microsoft SQL Server Management Studio 中的 本地发布 和 本地订阅 文件夹以及复制监视器中的“ 所有订阅 ”选项卡按需同步订阅。 Oracle的出版物订阅不能由订阅者按需同步。有关启动复制监视器的信息，请参阅 “启动复制监视器”。

在 Management Studio 中按需同步推送订阅（在发布服务器上）

在 Management Studio 中连接到发布服务器，然后展开服务器节点。
展开 “复制 ”文件夹，然后展开 “本地发布” 文件夹。
展开您想要同步订阅的出版物。
右键单击要同步的订阅，然后单击“ 查看同步状态”。
在 “视图同步状态 - <订阅服务器>：<SubscriptionDatabase> ”对话框中，单击“ 开始”。同步完成后，将显示消息 “同步已完成 ”。
单击 “关闭” 。

在 Management Studio 中按需同步推送订阅（在订阅服务器上）

在 SQL Server Management Studio 中连接到订阅者，然后展开服务器节点。
展开 “复制 ”文件夹，然后展开 “本地订阅 ”文件夹。
右键单击要同步的订阅，然后单击“ 查看同步状态”。
将显示一条消息，说明如何建立与分发服务器的连接。单击 “确定” 。
在 “视图同步状态 - <订阅服务器>：<SubscriptionDatabase> ”对话框中，单击“ 开始”。同步完成后，将显示消息 “同步已完成 ”。
单击 “关闭” 。

在复制监视器中按需同步推送订阅

在复制监视器中，展开左窗格中的发布者组，展开发布者，然后单击发布。
单击“ 所有订阅 ”选项卡。
右键单击要同步的订阅，然后单击“ 开始同步”。
若要查看同步进度，请右键单击订阅，然后单击“ 查看详细信息”。

使用复制代理

可以通过从命令提示符调用相应的复制代理可执行文件，以编程方式按需同步推送订阅。调用的复制代理可执行文件将取决于推送订阅所属的发布类型。

启动分发代理以将推送订阅同步到事务发布

在分发服务器的命令提示符或批处理文件中，执行 distrib.exe。指定以下命令行参数：
- -发行人
- -PublisherDB
- -分配器
- -订户
- -SubscriberDB
- -SubscriptionType = 0
如果使用 SQL Server 身份验证，则还必须指定以下参数：
- -DistributorLogin
- -经销商密码
- -DistributorSecurityMode = 0
- -PublisherLogin
- -PublisherPassword
- -PublisherSecurityMode = 0
- -SubscriberLogin
- -SubscriberPassword
- -SubscriberSecurityMode = 0
  
  重要
  
  请尽可能使用 Windows 身份验证。

启动合并代理服务以便将推送订阅同步到合并发布项

在分发服务器的命令提示符或批处理文件中，执行 replmerg.exe。指定以下命令行参数：
- -发行人
- -PublisherDB
- -出版
- -分配器
- -订户
- -SubscriberDB
- -SubscriptionType = 0
如果使用 SQL Server 身份验证，则还必须指定以下参数：
- -DistributorLogin
- -分销商密码
- -DistributorSecurityMode = 0
- -PublisherLogin
- -PublisherPassword
- -PublisherSecurityMode = 0
- -SubscriberLogin
- -SubscriberPassword
- -SubscriberSecurityMode = 0
  
  重要
  
  请尽可能使用 Windows 身份验证。

示例（复制代理）

以下示例启动分发代理以同步推送订阅。

REM -- Declare the variables.  
SET Publisher=%instancename%  
SET Subscriber=%instancename%  
SET PublicationDB=AdventureWorks2012  
SET SubscriptionDB=AdventureWorks2012Replica   
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

以下示例启动合并代理以同步推送订阅。

REM -- Declare the variables.  
SET Publisher=%instancename%  
SET Subscriber=%instancename%  
SET PublicationDB=AdventureWorks2012  
SET SubscriptionDB=AdventureWorks2012Replica   
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

使用复制管理对象 (RMO)

可以通过使用复制管理对象（RMO）编写代码来访问复制代理功能，从而以编程方式进行推送订阅的同步。用于同步推送订阅的类库取决于订阅所属的发布类型。

注释

如果要在不影响应用程序的情况下自动启动运行同步，请异步启动代理。但是，如果要监视同步的结果并在同步过程中从代理接收回调（例如，如果要显示进度栏），则应同步启动代理。对于 MicrosoftSQL Server 2005 Express Edition 订阅服务器，必须同步启动代理。

将推送订阅与快照发布或事务发布进行同步

使用 ServerConnection 类创建与分发服务器的连接。
创建类的 TransSubscription 实例并设置以下属性：
- 发布数据库名称为DatabaseName。
- 订阅属于的出版物名称为 PublicationName。
- SubscriptionDBName 的订阅数据库名称。
- 订户SubscriberName的名称。
- 在步骤 1 中创建的连接 ConnectionContext。
LoadProperties调用该方法以获取剩余的订阅属性。如果此方法返回 false，请验证订阅是否存在。
通过以下方式之一在分发服务器上启动分发代理：
- 在步骤 2 的TransSubscription实例上调用SynchronizeWithJob方法。此方法异步启动分发代理，当代理作业正在运行时，控制权立即返回到您的应用程序。如果订阅的创建值是false为CreateSyncAgentByDefault，则无法调用此方法。
- 从SynchronizationAgent属性获取类的TransSynchronizationAgent实例，并调用Synchronize该方法。此方法以同步方式启动代理，并且控制与正在运行的代理作业保持一致。在同步执行期间，可以在代理运行时处理 Status 事件。

将推送订阅同步到合并发布

使用 ServerConnection 类创建与分发服务器的连接。
创建类的 MergeSubscription 实例，并设置以下属性：
- DatabaseName 的出版数据库名称。
- 订阅所属的刊物名称为 PublicationName。
- 订阅数据库SubscriptionDBName的名称。
- 订阅者 SubscriberName的名称。
- 在步骤 1 中创建的连接 ConnectionContext。
LoadProperties调用该方法以获取剩余的订阅属性。如果此方法返回 false，请验证订阅是否存在。
通过以下方式之一在分发服务器上启动合并代理：
- 在步骤 2 的MergeSubscription实例上调用SynchronizeWithJob方法。此方法异步启动合并代理，代理作业运行时，控制权立即返回到您的应用程序。如果订阅是以 false 和 CreateSyncAgentByDefault 的值创建的，则无法调用此方法。
- 从SynchronizationAgent属性获取类的MergeSynchronizationAgent实例，并调用Synchronize该方法。此方法同步启动合并代理，控制权限保持在正在运行的代理作业中。在同步执行期间，可以在代理运行时处理 Status 事件。

示例 (RMO)

此示例将推送订阅同步到事务发布，其中代理是使用代理作业异步启动的。

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

/// 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 = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

' 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

此示例同步推送订阅与事务发布，并以同步方式启动代理。

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

// 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 = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

' 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

此示例将推送订阅同步到合并发布，其中代理是使用代理作业异步启动的。

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

// 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 = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

' 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

本示例将推送订阅同步到合并发布，其中代理是同步启动的。

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

// 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 = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

' 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

另请参阅

复制管理对象概念
 同步数据
 复制安全最佳做法

通过