停用發行和散發

發行項
09/25/2024

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

本主題說明如何使用 SQL Server Management Studio、Transact-SQL 或 Replication Management Objects (RMO)，在 SQL Server 中停用發行和散發。

您可以執行下列工作：

刪除「散發者」上的散發資料庫。
停用所有使用「散發者」的「發行者」，以及刪除這些「發行者」上的所有發行集。
刪除所有的發行集訂閱。發行集和訂閱資料庫中的資料不會刪除，不過它會遺失與任何發行資料庫的同步處理關聯性。若要刪除「訂閱者」中的資料，您必須手動刪除。

本主題內容

開始之前：

先決條件
若要停用發行和散發，請使用：

SQL Server Management Studio

Transact-SQL

Replication Management Objects (RMO)

開始之前

必要條件

若要停用發行和散發，所有散發和發行集資料庫都必須在線上。如果存在散發或發行集資料庫的任何 「資料庫快照集」 ，則必須先卸除這些快照集，然後才能停用發行和散發。資料庫快照集是資料庫的唯讀離線副本，與複寫快照集無關聯。如需詳細資訊，請參閱資料庫快照集 (SQL Server)。

使用 SQL Server Management Studio

使用「停用發行與散發精靈」停用發行和散發。

停用發行和散發

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

使用 TRANSACT-SQL

您可以使用複寫預存程序來以程式設計的方式停用發行和散發。

停用發行和散發

停止所有複寫相關的作業。如需作業名稱清單，請參閱＜複寫代理程式安全性模型＞一節中的「SQL Server Agent 下的代理程式安全性」。
在訂閱資料庫的每一個訂閱者上，執行 sp_removedbreplication 從資料庫中移除複寫物件。這個預存程序將不會移除散發者上的複寫作業。
在發行集資料庫的發行者上，執行 sp_removedbreplication 從資料庫中移除複寫物件。
如果發行者使用遠端散發者，請執行 sp_dropdistributor。
在散發者上執行 sp_dropdistpublisher。應該針對散發者上註冊的每一個發行者執行此預存程序一次。
在散發者上，執行 sp_dropdistributiondb 來刪除散發資料庫。應該針對散發者上的每一個散發資料庫執行此預存程序一次。這樣也會移除與散發資料庫有關的任何佇列讀取器代理程式作業。
在散發者上，執行 sp_dropdistributor 從伺服器移除散發者的指定。

注意

如果在您執行 sp_dropdistpublisher 和 sp_dropdistributor之前，尚未卸除所有複寫發行和散發物件，這些程序將會傳回錯誤。若要在卸除發行者或散發者時，一併卸除所有與複寫相關的物件，@no_checks 參數必須設定為 1。如果發行者或散發者已離線或是無法連上，則 @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

使用 Replication Management Objects (RMO)

停用發行和散發

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

範例 (RMO)

此範例會移除散發者上的發行者註冊、捨棄散發資料庫，以及解除安裝散發者。

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

共用方式為

停用發行和散發

開始之前

必要條件

使用 SQL Server Management Studio

停用發行和散發

使用 TRANSACT-SQL

停用發行和散發

範例 (Transact-SQL)

使用 Replication Management Objects (RMO)

停用發行和散發

範例 (RMO)

意見反應

其他資源

共用方式為

停用發行和散發

開始之前

必要條件

使用 SQL Server Management Studio

停用發行和散發

使用 TRANSACT-SQL

停用發行和散發

範例 (Transact-SQL)

使用 Replication Management Objects (RMO)

停用發行和散發

範例 (RMO)

相關內容

意見反應

其他資源