本文描述了如何使用SQL Server Management Studio、Transact-SQL或复制管理对象(RMO)禁用SQL Server中的发布和分发。
你可以执行以下步骤:
删除分发服务器上的所有分发数据库。
禁用所有使用该分发服务器的发布服务器,并删除其上的所有发布。
删除对这些发布内容的所有订阅。 发布和订阅数据库中的数据不会被删除,但它将失去与任何发布数据库的同步关系。 如果要删除订阅服务器上的数据,则必须手动删除。
先决条件
- 若要禁用发布和分发,所有分发和发布数据库都必须处于联机状态。 如果存在任何分发数据库或发布数据库的数据库快照,您必须先删除它们,然后才能禁用发布和分发。 数据库快照是数据库的只读离线副本,与复制快照无关。 有关详细信息,请参阅数据库快照 (SQL Server)。
使用 SQL Server Management Studio
请使用禁用发布和分发向导来禁用发布和分发。
禁用发布和分发
在 Microsoft SQL Server Management Studio 中连接到要禁用的发布服务器或分发服务器,然后展开服务器节点。
右键单击 “复制” 文件夹,再单击 “禁用发布和分发”。
完成禁用发布和分发向导中的步骤。
“使用 Transact-SQL”
你可以通过使用复制存储过程禁用发布和分发。
禁用发布和分发
停止所有与复制相关的作业。 有关作业名称列表,请参阅 复制代理安全模式的“SQL Server 代理下的代理安全性”部分。
在每个订阅服务器上,对订阅数据库执行 sp_removedbreplication 以从该数据库删除复制对象。 该存储过程不会删除分发服务器上的复制作业。
在发布服务器上,对发布数据库执行 sp_removedbreplication 以从该数据库删除复制对象。
如果发布服务器使用远程分发服务器,则执行 sp_dropdistributor。
在分发服务器上,执行 sp_dropdistpublisher。 为每个在分发商注册的 Publisher 运行一次这个存储过程。
在分发服务器上,执行 sp_dropdistributiondb 以删除分发数据库。 在分发器处为每个分发数据库运行一次该存储过程。 此操作还会移除与分发数据库关联的任何队列读取代理任务。
在分发服务器上,执行 sp_dropdistributor 以从该服务器删除分发服务器指定。
注意
如果你在执行 sp_dropdistpublisher 和 sp_dropdistributor之前没有丢弃所有复制发布和分发对象,这些过程会返回错误。 当 Publisher 或 Distributor 被丢弃时,要丢弃所有与复制相关的对象,请将参数设
@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 方法以获取对象的属性,并验证发布服务器是否存在。 如果该方法返回错误,说明第4步设置的Publisher名称错误,或者该Publisher未被该分发商使用。
调用 Remove 方法。 如果发布服务器和分发服务器位于不同的服务器上,并且应在分发服务器上卸载发布服务器而不先验证发布服务器上的发布是否已不存在,请为 force 传递值 true。
创建 ReplicationServer 类的一个实例。 传递步骤 3 中的 ServerConnection 对象。
调用 UninstallDistributor 方法。 为 force 传递 true 值,可删除分发服务器上的所有复制对象,而无需先验证所有本地发布数据库均已禁用且分发数据库已删除。
示例 (RMO)
以下示例移除了分销商的Publisher注册,丢弃了分发数据库,并卸载了分销商。
// 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