Disable Publishing and Distribution

This topic describes how to disable publishing and distribution in SQL Server 2014 by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).

You can do the following:

Delete all distribution databases on the Distributor.

Disable all Publishers that use the Distributor and delete all publications on those Publishers.

Delete all subscriptions to the publications. Data in the publication and subscription databases will not be deleted; however, it loses its synchronization relationship to any publication databases. If you want the data at the Subscriber to be deleted, you must delete it manually.

Prerequisites

To disable publishing and distribution, all distribution and publication databases must be online. If any database snapshots exist for distribution or publication databases, they must be dropped before disabling publishing and distribution. A database snapshot is a read-only offline copy of a database and is not related to a replication snapshot. For more information, see Database Snapshots (SQL Server).

To disable publishing and distribution

At each Subscriber on the subscription database, execute sp_removedbreplication to remove replication objects from the database. This stored procedure will not remove replication jobs at the Distributor.

At the Publisher on the publication database, execute sp_removedbreplication to remove replication objects from the database.

At the Distributor, execute sp_dropdistpublisher. This stored procedure should be run once for each Publisher registered at the Distributor.

At the Distributor, execute sp_dropdistributiondb to delete the distribution database. This stored procedure should be run once for each distribution database at the Distributor. This also removes any Queue Reader Agent jobs associated with the distribution database.

At the Distributor, execute sp_dropdistributor to remove the Distributor designation from the server.

Note

If all replication publishing and distribution objects are not dropped before you execute sp_dropdistpublisher and sp_dropdistributor, these procedures will return an error. To drop all replication-related objects when a Publisher or Distributor is dropped, the @no_checks parameter must be set to 1. If a Publisher or Distributor is offline or unreachable, the @ignore_distributor parameter can be set to 1 so that they can be dropped; however, any publishing and distributing objects left behind must be removed manually.

Examples (Transact-SQL)

This example script removes replication objects from the subscription database.

-- 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'AdventureWorks2012';
-- Disable the publication database.
USE [AdventureWorks2012]
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

(Optional) Call the LoadProperties method to get the properties of the object and verify that the Publisher exists. If this method returns false, the Publisher name set in step 4 was incorrect or the Publisher is not used by this Distributor.

Call the Remove(Boolean) method. Pass a value of true for force if the Publisher and Distributor are on different servers, and when the Publisher should be uninstalled at the Distributor without first verifying that publications no longer exist at the Publisher.

Call the UninstallDistributor method. Pass a value of true for force to remove all replication objects at the Distributor without first verifying that all local publication databases have been disabled, and distribution databases have been uninstalled.

Examples (RMO)

This example removes the Publisher registration at the Distributor, drops the distribution database, and uninstalls the Distributor.

' Set the Distributor and publication database names.' Publisher and Distributor are on the same server instance.Dim publisherName AsString = publisherInstance
Dim distributorName AsString = subscriberInstance
Dim distributionDbName AsString = "distribution"Dim publicationDbName AsString = "AdventureWorks2012"' 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 AdventureWorks2012 database.If publicationDb.LoadProperties() ThenIf publicationDb.EnabledMergePublishing Then
publicationDb.EnabledMergePublishing = FalseElseIf publicationDb.EnabledTransPublishing Then
publicationDb.EnabledTransPublishing = FalseEndIfElseThrowNew ApplicationException( _
String.Format("The {0} database does not exist.", publicationDbName))
EndIf' 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.ThrowNew ApplicationException(String.Format( _
"{0} is not a Publisher for {1}.", publisherName, distributorName))
EndIf' Drop the distribution database.If distributionDb.LoadProperties() Then
distributionDb.Remove()
Else' Do something here if the distribition DB does not exist.ThrowNew ApplicationException(String.Format( _
"The distribution database '{0}' does not exist on {1}.", _
distributionDbName, distributorName))
EndIf' 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.ThrowNew ApplicationException(String.Format( _
"The Distributor '{0}' does not exist.", distributorName))
EndIfElseThrowNew ApplicationException("You must first delete all subscriptions.")
EndIfCatch ex As Exception
' Implement appropriate error handling here.ThrowNew ApplicationException("The Publisher and Distributor could not be uninstalled", ex)
Finally
publisherConn.Disconnect()
distributorConn.Disconnect()
EndTry

This example uninstalls the Distributor without first disabling local publication databases or dropping the distribution database.

' Set the Distributor and publication database names.' Publisher and Distributor are on the same server instance.Dim distributorName AsString = 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.EndIfCatch ex As Exception
' Implement appropriate error handling here.ThrowNew ApplicationException("The Publisher and Distributor could not be uninstalled", ex)
Finally
conn.Disconnect()
EndTry