禁用发布和分发

项目
10/05/2023

适用于：SQL Server Azure SQL 托管实例

本主题介绍如何使用 SQL Server Management Studio、Transact-SQL 或复制管理对象 (RMO) 在 SQL Server 中禁用发布和分发功能。

您可以执行下列操作：

删除分发服务器上的所有分发数据库。
禁用使用分发服务器的所有发布服务器并删除这些发布服务器上的所有发布。
删除对这些发布的所有订阅。发布和订阅数据库中的数据不会被删除，但它将失去与任何发布数据库的同步关系。如果要删除订阅服务器上的数据，则必须手动删除。

本主题内容

开始之前：

先决条件
禁用发布和分发，使用：

SQL Server Management Studio

Transact-SQL

复制管理对象 (RMO)

开始之前

先决条件

若要禁用发布和分发，所有分发数据库和发布数据库都必须联机。如果存在分发数据库或发布数据库的“数据库快照” ，则在禁用发布和分发前，必须先删除这些数据库快照。数据库快照是数据库的只读脱机副本，与复制快照无关。有关详细信息，请参阅数据库快照 (SQL Server)。

使用 SQL Server Management Studio

使用禁用发布和分发向导禁用发布和分发。

禁用发布和分发

在 Microsoft SQL Server Management Studio 中连接到要禁用的发布服务器或分发服务器，然后展开服务器节点。
右键单击 “复制” 文件夹，再单击 “禁用发布和分发”。
完成禁用发布和分发向导中的步骤。

“使用 Transact-SQL”

可以使用复制存储过程以编程方式禁用发布和分发。

禁用发布和分发

停止所有与复制相关的作业。有关作业名称列表，请参阅复制代理安全模式的“SQL Server 代理下的代理安全性”部分。
在每个订阅服务器上，对订阅数据库执行 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

使用复制管理对象 (RMO)

禁用发布和分发

删除所有使用分发服务器的发布订阅。有关详细信息，请参阅 Delete a Pull Subscription 和 Delete a Push Subscription。
如果发布服务器和分发服务器在相同的服务器上，则删除所有使用分发服务器的发布，并禁用发布所有数据库。有关详细信息，请参阅 Delete a Publication。
使用 ServerConnection 类创建与分发服务器的连接。
创建 DistributionPublisher 类的一个实例。指定 Name 属性，并传递步骤 3 中的 ServerConnection 对象。
（可选）调用 LoadProperties 方法以获取对象的属性，并验证发布服务器是否存在。如果此方法返回 false，则步骤 4 中设置的发布服务器名称不正确，或者此分发服务器未使用该发布服务器。
调用 Remove 方法。如果发布服务器和分发服务器在不同的服务器上，并且应在分发服务器上卸载发布服务器而没有首先验证发布服务器上是否不再存在发布时，将值 true 传递给 force 。
创建 ReplicationServer 类的一个实例。传递步骤 3 中的 ServerConnection 对象。
调用 UninstallDistributor 方法。在没有首先验证所有本地发布数据库是否已禁用以及分发数据库是否已卸载的情况下，将 true 值传递给 force 以删除分发服务器上的所有复制对象。

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

另请参阅

Replication Management Objects Concepts
Replication System Stored Procedures Concepts

通过

禁用发布和分发

开始之前

先决条件

使用 SQL Server Management Studio

禁用发布和分发

“使用 Transact-SQL”

禁用发布和分发

示例 (Transact-SQL)

使用复制管理对象 (RMO)

禁用发布和分发

示例 (RMO)

另请参阅

反馈

其他资源