停用發佈與分發

適用於：SQL Server Azure SQL 受控執行個體

本文說明如何使用 SQL Server Management Studio、Transact-SQL 或複寫管理物件（RMO）來停用 SQL Server 中的發行與散發功能。

你可以執行以下步驟：

刪除發佈者上的所有發佈資料庫。
停用所有使用散發者的發行者，並刪除這些發行者上的所有發行項目。
刪除所有出版品的訂閱。發行資料庫和訂閱資料庫中的資料不會被刪除；但是，資料會失去與任何發行資料庫的同步關係。若要刪除「訂閱者」中的資料，您必須手動刪除。

必要條件

若要停用發行和散發，所有散發和發行集資料庫都必須在線上。如果有任何用於發佈或出版資料庫的快照，您必須在停用發佈與分發前將其移除。資料庫快照是資料庫的唯讀離線副本，與複製快照無關。如需詳細資訊，請參閱資料庫快照集 (SQL Server)。

使用 SQL Server Management Studio

使用「停用發佈與發行」精靈來停用發佈與發行。

停用發佈與散佈

在 Microsoft SQL Server Management Studio 中，連線到您想要停用的發行者或散發者，然後展開伺服器節點。
以滑鼠右鍵按一下 [複寫] 資料夾，然後按一下 [停用發行與散發] 。
完成「停用散發暨發行精靈」中的步驟。

使用 TRANSACT-SQL

你可以透過複製儲存程序來停用發佈和分發。

停用發佈與散佈

停止所有複寫相關的作業。如需作業名稱清單，請參閱＜複寫代理程式安全性模型＞一節中的「SQL Server Agent 下的代理程式安全性」。
在訂閱資料庫中的每個訂閱者上，執行 sp_removedbreplication 以從資料庫中移除複寫物件。此儲存程序不會移除分發器的複製工作。
在發行資料庫中的發行者上，執行 sp_removedbreplication，以從資料庫中移除複寫物件。
如果發行者使用遠端散發者，請執行 sp_dropdistributor。
在散發者上執行 sp_dropdistpublisher。對每個在發行商註冊的 Publisher 執行一次這個儲存程序。
在散發者上，執行 sp_dropdistributiondb 來刪除散發資料庫。在分發器處對每個發行資料庫執行一次此儲存程序。此動作同時移除與分發資料庫相關的任何佇列讀取代理工作。
在散發者端，執行 sp_dropdistributor，以從伺服器移除散發者角色。

注意

如果你在執行 sp_dropdistpublisher 和 sp_dropdistributor 前沒有丟棄所有複製發佈和分發物件，這些程序就會回傳錯誤。若要在 Publisher 或 Distributor 被丟棄時丟棄所有與複製相關的物件，請將參數設@no_checks為 1。如果 Publisher 或 Distributor 離線或無法聯繫，請將參數設@ignore_distributor為 1，這樣你就能直接丟棄他們。不過，您必須手動移除所有遺留的發佈與分發物件。

範例 (Transact-SQL)

這個範例指令碼會從訂閱資料庫中移除複寫物件。

-- Remove replication objects from the subscription database on MYSUB.
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'AdventureWorks2022Replica'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO

此範例指令碼會停用同時作為發行者與散發者的伺服器上的發行與散發功能，並刪除散發資料庫。

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- Disable publishing and distribution.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB as sysname;
SET @distributionDB = N'distribution';
SET @publisher = $(DistPubServer);
SET @publicationDB = N'AdventureWorks2022';

-- Disable the publication database.
USE [AdventureWorks2022]
EXEC sp_removedbreplication @publicationDB;

-- Remove the registration of the local Publisher at the Distributor.
USE master
EXEC sp_dropdistpublisher @publisher;

-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;

-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO

使用複寫管理物件 (RMO)

停用發佈與散佈

移除所有使用該散發者之發行集的訂閱。如需相關資訊，請參閱 Delete a Pull Subscription 以及 Delete a Push Subscription。
移除使用散發者的所有訂閱，以及停用所有資料庫的發行 (如果發行者和散發者在相同的伺服器上)。如需詳細資訊，請參閱 Delete a Publication。
使用 ServerConnection 類別建立與散發者的連接。
建立 DistributionPublisher 類別的執行個體。指定 Name 屬性，並傳遞步驟 3 中的 ServerConnection 物件。
(選擇性) 呼叫 LoadProperties 方法，以取得物件的屬性及確認發行者確實存在。如果此方法回傳錯誤，代表步驟 4 中設定的 Publisher 名稱錯誤，或該 Publisher 未被該發行商使用。
呼叫 Remove 方法。如果發行者和散發者位於不同的伺服器上，以及應該在散發者上解除安裝發行者，而不需要先確認發行集是否已經不在發行者上時，請針對 force 傳遞 true 的值。
建立 ReplicationServer 類別的執行個體。傳遞步驟 3 的 ServerConnection 物件。
呼叫 UninstallDistributor 方法。將 force 傳遞為 true，即可移除分發者上的所有複寫物件，而不先驗證所有本機發行集資料庫都已停用，且散發資料庫都已解除安裝。

範例 (RMO)

以下範例移除 Publisher 在發行商的註冊，移除 Distribution 資料庫，並卸載該發行商。

// Set the Distributor and publication database names.
// Publisher and Distributor are on the same server instance.
string publisherName = publisherInstance;
string distributorName = publisherInstance;
string distributionDbName = "distribution";
string publicationDbName = "AdventureWorks2022";

// Create connections to the Publisher and Distributor
// using Windows Authentication.
ServerConnection publisherConn = new ServerConnection(publisherName);
ServerConnection distributorConn = new ServerConnection(distributorName);

// Create the objects we need.
ReplicationServer distributor =
    new ReplicationServer(distributorConn);
DistributionPublisher publisher;
DistributionDatabase distributionDb =
    new DistributionDatabase(distributionDbName, distributorConn);
ReplicationDatabase publicationDb;
publicationDb = new ReplicationDatabase(publicationDbName, publisherConn);

try
{
    // Connect to the Publisher and Distributor.
    publisherConn.Connect();
    distributorConn.Connect();

    // Disable all publishing on the AdventureWorks2022 database.
    if (publicationDb.LoadProperties())
    {
        if (publicationDb.EnabledMergePublishing)
        {
            publicationDb.EnabledMergePublishing = false;
        }
        else if (publicationDb.EnabledTransPublishing)
        {
            publicationDb.EnabledTransPublishing = false;
        }
    }
    else
    {
        throw new ApplicationException(
            String.Format("The {0} database does not exist.", publicationDbName));
    }

    // We cannot uninstall the Publisher if there are still Subscribers.
    if (distributor.RegisteredSubscribers.Count == 0)
    {
        // Uninstall the Publisher, if it exists.
        publisher = new DistributionPublisher(publisherName, distributorConn);
        if (publisher.LoadProperties())
        {
            publisher.Remove(false);
        }
        else
        {
            // Do something here if the Publisher does not exist.
            throw new ApplicationException(String.Format(
                "{0} is not a Publisher for {1}.", publisherName, distributorName));
        }

        // Drop the distribution database.
        if (distributionDb.LoadProperties())
        {
            distributionDb.Remove();
        }
        else
        {
            // Do something here if the distribition DB does not exist.
            throw new ApplicationException(String.Format(
                "The distribution database '{0}' does not exist on {1}.",
                distributionDbName, distributorName));
        }

        // Uninstall the Distributor, if it exists.
        if (distributor.LoadProperties())
        {
            // Passing a value of false means that the Publisher 
            // and distribution databases must already be uninstalled,
            // and that no local databases be enabled for publishing.
            distributor.UninstallDistributor(false);
        }
        else
        {
            //Do something here if the distributor does not exist.
            throw new ApplicationException(String.Format(
                "The Distributor '{0}' does not exist.", distributorName));
        }
    }
    else
    {
        throw new ApplicationException("You must first delete all subscriptions.");
    }
}
catch (Exception ex)
{
    // Implement appropriate error handling here.
    throw new ApplicationException("The Publisher and Distributor could not be uninstalled", ex);
}
finally
{
    publisherConn.Disconnect();
    distributorConn.Disconnect();
}

' Set the Distributor and publication database names.
' Publisher and Distributor are on the same server instance.
Dim publisherName As String = publisherInstance
Dim distributorName As String = subscriberInstance
Dim distributionDbName As String = "distribution"
Dim publicationDbName As String = "AdventureWorks2022"

' Create connections to the Publisher and Distributor
' using Windows Authentication.
Dim publisherConn As ServerConnection = New ServerConnection(publisherName)
Dim distributorConn As ServerConnection = New ServerConnection(distributorName)

' Create the objects we need.
Dim distributor As ReplicationServer
distributor = New ReplicationServer(distributorConn)
Dim publisher As DistributionPublisher
Dim distributionDb As DistributionDatabase
distributionDb = New DistributionDatabase(distributionDbName, distributorConn)
Dim publicationDb As ReplicationDatabase
publicationDb = New ReplicationDatabase(publicationDbName, publisherConn)

Try
    ' Connect to the Publisher and Distributor.
    publisherConn.Connect()
    distributorConn.Connect()

    ' Disable all publishing on the AdventureWorks2022 database.
    If publicationDb.LoadProperties() Then
        If publicationDb.EnabledMergePublishing Then
            publicationDb.EnabledMergePublishing = False
        ElseIf publicationDb.EnabledTransPublishing Then
            publicationDb.EnabledTransPublishing = False
        End If
    Else
        Throw New ApplicationException( _
            String.Format("The {0} database does not exist.", publicationDbName))
    End If

    ' We cannot uninstall the Publisher if there are still Subscribers.
    If distributor.RegisteredSubscribers.Count = 0 Then
        ' Uninstall the Publisher, if it exists.
        publisher = New DistributionPublisher(publisherName, distributorConn)
        If publisher.LoadProperties() Then
            publisher.Remove(False)
        Else
            ' Do something here if the Publisher does not exist.
            Throw New ApplicationException(String.Format( _
                "{0} is not a Publisher for {1}.", publisherName, distributorName))
        End If

        ' Drop the distribution database.
        If distributionDb.LoadProperties() Then
            distributionDb.Remove()
        Else
            ' Do something here if the distribition DB does not exist.
            Throw New ApplicationException(String.Format( _
             "The distribution database '{0}' does not exist on {1}.", _
             distributionDbName, distributorName))
        End If

        ' Uninstall the Distributor, if it exists.
        If distributor.LoadProperties() Then
            ' Passing a value of false means that the Publisher 
            ' and distribution databases must already be uninstalled,
            ' and that no local databases be enabled for publishing.
            distributor.UninstallDistributor(False)
        Else
            'Do something here if the distributor does not exist.
            Throw New ApplicationException(String.Format( _
                "The Distributor '{0}' does not exist.", distributorName))
        End If
    Else
        Throw New ApplicationException("You must first delete all subscriptions.")
    End If

Catch ex As Exception
    ' Implement appropriate error handling here.
    Throw New ApplicationException("The Publisher and Distributor could not be uninstalled", ex)

Finally
    publisherConn.Disconnect()
    distributorConn.Disconnect()

End Try

以下範例是在未先停用本地出版資料庫或丟棄發行資料庫的情況下，解除安裝分發器。

// Set the Distributor and publication database names.
// Publisher and Distributor are on the same server instance.
string distributorName = publisherInstance;

// Create connections to the Distributor
// using Windows Authentication.
ServerConnection conn = new ServerConnection(distributorName);
conn.DatabaseName = "master";

// Create the objects we need.
ReplicationServer distributor = new ReplicationServer(conn);

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


    // Uninstall the Distributor, if it exists.
    // Use the force parameter to remove everthing.  
    if (distributor.IsDistributor && distributor.LoadProperties())
    {
        // Passing a value of true means that the Distributor 
        // is uninstalled even when publishing objects, subscriptions,
        // and distribution databases exist on the server.
        distributor.UninstallDistributor(true);
    }
    else
    {
        //Do something here if the distributor does not exist.
    }
}
catch (Exception ex)
{
    // Implement appropriate error handling here.
    throw new ApplicationException("The Publisher and Distributor could not be uninstalled", ex);
}
finally
{
    conn.Disconnect();
}

' Set the Distributor and publication database names.
' Publisher and Distributor are on the same server instance.
Dim distributorName As String = publisherInstance

' Create connections to the Distributor
' using Windows Authentication.
Dim conn As ServerConnection = New ServerConnection(distributorName)
conn.DatabaseName = "master"

' Create the objects we need.
Dim distributor As ReplicationServer = New ReplicationServer(conn)

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


    ' Uninstall the Distributor, if it exists.
    ' Use the force parameter to remove everthing.  
    If distributor.IsDistributor And distributor.LoadProperties() Then
        ' Passing a value of true means that the Distributor 
        ' is uninstalled even when publishing objects, subscriptions,
        ' and distribution databases exist on the server.
        distributor.UninstallDistributor(True)
    Else
        'Do something here if the distributor does not exist.
    End If

Catch ex As Exception
    ' Implement appropriate error handling here.
    Throw New ApplicationException("The Publisher and Distributor could not be uninstalled", ex)

Finally
    conn.Disconnect()

End Try

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-09-04

停用發佈與分發

必要條件

使用 SQL Server Management Studio

停用發佈與散佈

使用 TRANSACT-SQL

停用發佈與散佈

範例 (Transact-SQL)

使用複寫管理物件 (RMO)

停用發佈與散佈

範例 (RMO)

相關內容

意見反應

其他資源