同步推送订阅

项目
04/26/2023

本主题说明如何使用 SQL Server Management Studio、复制代理或复制管理对象 (RMO) 在 SQL Server 中同步推送订阅。

注意

对于快照复制和事务复制，Azure SQL 托管实例可以是发布服务器、分发服务器和订阅服务器。对于快照复制和事务复制，Azure SQL 数据库中的数据库只能是推送订阅服务器。有关详细信息，请参阅使用 Azure SQL 数据库和 Azure SQL 托管实例进行事务复制。

使用 SQL Server Management Studio

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

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

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

在 Management Studio 中连接到发布服务器，然后展开服务器节点。
展开 “复制” 文件夹，再展开 “本地发布” 文件夹。
展开要同步其订阅的发布。
右键单击要同步的订阅，然后单击 “查看同步状态”。
在“查看同步状态 - <订阅服务器>:<订阅数据库>”对话框中，单击“启动”。完成同步后，将显示消息 “同步完成” 。
单击“关闭” 。

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

在 Management Studio 中连接到订阅服务器，然后展开服务器节点。
展开 “复制” 文件夹，再展开 “本地订阅” 文件夹。
右键单击要同步的订阅，然后单击 “查看同步状态”。
将显示一条消息，指示建立与分发服务器的连接。单击“确定”。
在“查看同步状态 - <订阅服务器>:<订阅数据库>”对话框中，单击“启动”。完成同步后，将显示消息 “同步完成” 。
单击“关闭” 。

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

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

使用复制代理

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

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

通过命令提示符或分发服务器中的批处理文件，执行 distrib.exe。指定下列命令行参数：
- -Publisher
- -PublisherDB
- -Distributor
- -Subscriber
- -SubscriberDB
- -SubscriptionType = 0
如果您使用的是 SQL Server 身份验证，则还必须指定下列参数：
- -DistributorLogin
- -DistributorPassword
- -DistributorSecurityMode = 0
- -PublisherLogin
- -PublisherPassword
- -PublisherSecurityMode = 0
- -SubscriberLogin
- -SubscriberPassword
- -SubscriberSecurityMode = 0
  
  重要
  
  请尽可能使用 Windows 身份验证。

启动合并代理以将推送订阅与合并发布进行同步

通过命令提示符或分发服务器中的批处理文件，执行 replmerg.exe。指定下列命令行参数：
- -Publisher
- -PublisherDB
- -Publication
- -Distributor
- -Subscriber
- -SubscriberDB
- -SubscriptionType = 0
如果您使用的是 SQL Server 身份验证，则还必须指定下列参数：
- -DistributorLogin
- -DistributorPassword
- -DistributorSecurityMode = 0
- -PublisherLogin
- -PublisherPassword
- -PublisherSecurityMode = 0
- -SubscriberLogin
- -SubscriberPassword
- -SubscriberSecurityMode = 0
  
  重要
  
  请尽可能使用 Windows 身份验证。

示例（复制代理）

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

  
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

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

  
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

使用复制管理对象 (RMO)

可以使用复制管理对象 (RMO) 和托管代码的复制代理功能访问权限以编程方式同步推送订阅。用于同步推送订阅的类取决于订阅所属的发布的类型。

注意

如果您要启动一个自主运行而不影响应用程序的订阅，请异步启动代理。但是，如果您要监视同步的结果并在同步进程期间从代理处接收回调（例如，您要显示进度栏），则应当同步启动代理。对于 Microsoft SQL Server 2005 Express Edition 订阅者，必须同步启动代理。

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

使用 ServerConnection 类创建与分发服务器的连接。
创建 TransSubscription 类的实例并设置下列属性：
- 用于 DatabaseName的发布数据库的名称。
- 用于 PublicationName的订阅所属的发布的名称。
- 用于 SubscriptionDBName的订阅数据库的名称。
- 用于 SubscriberName的订阅服务器的名称。
- 在步骤 1 中为 ConnectionContext创建的连接。
调用 LoadProperties 方法以获取其他订阅属性。如果该方法返回 false，则确保订阅存在。
使用下列方法之一在分发服务器中启动分发代理：
- 在步骤 2 中的 SynchronizeWithJob 实例上调用 TransSubscription 方法。该方法异步启动分发代理，并在代理作业运行时立即将控制权返回给您的应用程序。如果创建的订阅的 false for CreateSyncAgentByDefault。
- 获取 TransSynchronizationAgent 属性中 SynchronizationAgent 类的实例，然后调用 Synchronize 方法。该方法可以同步启动代理，并且控制权仍属于运行代理作业。在执行同步期间，您可以在代理仍旧运行的情况下处理 Status 事件。

将推送订阅与合并发布进行同步

使用 ServerConnection 类创建与分发服务器的连接。
创建 MergeSubscription 类的实例并设置下列属性：
- 用于 DatabaseName的发布数据库的名称。
- 用于 PublicationName的订阅所属的发布的名称。
- 用于 SubscriptionDBName的订阅数据库的名称。
- 用于 SubscriberName的订阅服务器的名称。
- 在步骤 1 中为 ConnectionContext创建的连接。
调用 LoadProperties 方法以获取其他订阅属性。如果该方法返回 false，则确保订阅存在。
使用下列方法之一在分发服务器中启动合并代理：
- 在步骤 2 中的 SynchronizeWithJob 实例上调用 MergeSubscription 方法。该方法异步启动合并代理，并在代理作业运行时立即将控制权返回给您的应用程序。如果创建的订阅的 false for CreateSyncAgentByDefault。
- 获取 MergeSynchronizationAgent 属性中 SynchronizationAgent 类的实例，然后调用 Synchronize 方法。该方法可以同步启动合并代理，并且控件仍属于运行代理作业。在执行同步期间，您可以在代理仍旧运行的情况下处理 Status 事件。

示例 (RMO)

该示例将推送订阅与事务发布进行同步，其中，代理使用代理作业进行异步启动。

// 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

该示例将推送订阅与事务发布进行同步，其中，代理进行同步启动。

// 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

该示例将推送订阅与合并发布进行同步，其中，代理使用代理作业进行异步启动。

// 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

该示例将推送订阅与合并发布进行同步，其中，代理进行同步启动。

// 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

另请参阅

Replication Management Objects Concepts
同步数据
 复制安全最佳做法

通过