分享方式:


同步處理發送訂閱

適用於:SQL Server Azure SQL 資料庫

本主題描述如何使用 SQL Server Management Studio、複寫代理程式或 Replication Management Objects (RMO),同步處理 SQL Server 中的發送訂閱。

注意

Azure SQL 受控執行個體可以是快照式與異動複寫的發行者、散發者與訂閱者。 Azure SQL Database 中的資料庫只能是快照式與異動複寫的發送訂閱者。 如需詳細資訊,請參閱使用 Azure SQL DatabaseAzure SQL 受控執行個體的異動複寫。

使用 SQL Server Management Studio

訂閱是由散發代理程式 (適用於快照式與異動複寫) 或合併代理程式 (適用於合併式複寫) 同步處理。 代理程式可以繼續執行、視需要執行,或是依照排程執行。 如需指定同步處理排程的詳細資訊,請參閱指定同步處理排程

從 Microsoft SQL Server Management Studio 中的 [本機發行集] 和 [本機訂閱] 資料夾,以及複寫監視器中的 [所有訂閱] 索引標籤,視需要同步處理訂閱。 Oracle 發行集的訂閱無法在需要時從「訂閱者」同步處理。 如需啟動複寫監視器的詳細資訊,請參閱啟動複寫監視器

需要時在 Management Studio 上同步處理發送訂閱 (發行者端)

  1. 在 Management Studio 中連線至發行者,然後展開伺服器節點。

  2. 展開 [複寫] 資料夾,然後展開 [本機發行集] 資料夾。

  3. 展開您要同步處理其訂閱的發行集。

  4. 以滑鼠右鍵按一下您要同步處理的訂閱,然後按一下 [檢視同步處理的狀態]

  5. 在 [檢視同步處理的狀態 - <訂閱者>:<訂閱資料庫>] 對話方塊中,按一下 [開始]。 同步處理完成後,會顯示 [同步處理已完成] 的訊息。

  6. 按一下 [關閉] 。

需要時在 Management Studio 上同步處理發送訂閱 (訂閱者端)

  1. 在 Management Studio 中連線至訂閱者,然後展開伺服器節點。

  2. 展開 [複寫] 資料夾,然後展開 [本機訂閱] 資料夾。

  3. 以滑鼠右鍵按一下您要同步處理的訂閱,然後按一下 [檢視同步處理的狀態]

  4. 接著會顯示有關建立連接到「散發者」的訊息。 按一下 [確定]。

  5. 在 [檢視同步處理的狀態 - <訂閱者>:<訂閱資料庫>] 對話方塊中,按一下 [開始]。 同步處理完成後,會顯示 [同步處理已完成] 的訊息。

  6. 按一下 [關閉] 。

需要時在複寫監視器中同步處理發送訂閱

  1. 在複寫監視器中,展開左窗格裡的發行者群組,展開發行者,然後按一下發行集。

  2. 按一下 [所有訂閱] 索引標籤。

  3. 以滑鼠右鍵按一下您要同步處理的訂閱,再按一下 [啟動同步處理]

  4. 若要檢視同步處理的進度,以滑鼠右鍵按一下該訂閱,再按一下 [檢視詳細資料]

使用複寫代理程式

發送訂閱可透過程式設計方式加以同步處理,以及視需要從命令提示字元叫用適當的複寫代理程式可執行檔加以同步處理。 叫用的複寫代理程式可執行檔取決於發送訂閱所屬的發行集類型。

啟動散發代理程式,以同步處理交易式發行集的發送訂閱

  1. 從命令提示字元或是散發者上的批次檔中,執行 distrib.exe。 指定下列命令列引數:

    • -Publisher

    • -PublisherDB

    • -Distributor

    • -Subscriber

    • -SubscriberDB

    • -SubscriptionType = 0

    無果您正在使用「SQL Server 驗證」,您也必須指定下列引數:

    • -DistributorLogin

    • -DistributorPassword

    • -DistributorSecurityMode = 0

    • -PublisherLogin

    • -PublisherPassword

    • -PublisherSecurityMode = 0

    • -SubscriberLogin

    • -SubscriberPassword

    • -SubscriberSecurityMode = 0

      重要

      儘可能使用 Windows 驗證。

啟動合併代理程式,以同步處理合併式發行集的發送訂閱

  1. 從命令提示字元或是散發者上的批次檔中,執行 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  
  

使用 Replication Management Objects (RMO)

您可以使用 Replication Management Objects (RMO) 和對複寫代理程式功能的 Managed 程式碼存取,以程式設計的方式同步處理發送訂閱。 用於同步處理發送訂閱的類別依該訂閱所屬的發行集類型而定。

注意

如果您要啟動自發執行的同步處理而不影響應用程式,請非同步啟動代理程式。 不過,如果要監視同步處理的結果並在同步處理期間從代理程式接收回撥 (例如,如果要顯示進度列),您就應該同步啟動代理程式。 若為 Microsoft SQL Server 2005 Express Edition 訂閱者,您必須同步啟動代理程式。

若要同步處理快照式或交易式發行集的發送訂閱

  1. 使用 ServerConnection 類別建立與散發者的連接。

  2. 建立 TransSubscription 類別的執行個體,並設定下列屬性:

  3. 呼叫 LoadProperties 方法以取得剩餘的訂閱屬性。 如果此方法傳回 false,請確認該訂閱存在。

  4. 以下列其中一種方式啟動「散發者」上的「散發代理程式」:

若要同步處理合併式發行集的發送訂閱

  1. 使用 ServerConnection 類別建立與散發者的連接。

  2. 建立 MergeSubscription 類別的執行個體,並設定下列屬性:

  3. 呼叫 LoadProperties 方法以取得剩餘的訂閱屬性。 如果此方法傳回 false,請確認該訂閱存在。

  4. 以下列其中一種方式啟動「散發者」上的「合併代理程式」:

範例 (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

另請參閱

複寫管理物件概念
同步處理資料
複寫安全性最佳作法