Skip to content

Latest commit

 

History

History
132 lines (81 loc) · 9.66 KB

disable-publishing-and-distribution.md

File metadata and controls

132 lines (81 loc) · 9.66 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords monikerRange
Disable Publishing and Distribution
Learn how to disable publishing and distribution in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects.
MashaMSFT
mathoma
09/25/2024
sql
replication
how-to
updatefrequency5
disabling publishing
publishing [SQL Server replication], disabling
distribution disabling [SQL Server replication]
removing replication
replication [SQL Server], removing
disabling replication
disabling distribution
=azuresqldb-mi-current||>=sql-server-2016

Disable Publishing and Distribution

[!INCLUDE SQL Server SQL MI] This topic describes how to disable publishing and distribution in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], 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.

In This Topic

Before You Begin

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).

Using SQL Server Management Studio

Disable publishing and distribution by using the Disable Publishing and Distribution Wizard.

To disable publishing and distribution

  1. Connect to the Publisher or Distributor you want to disable in [!INCLUDEmsCoName] [!INCLUDEssManStudioFull], and then expand the server node.

  2. Right-click the Replication folder, and then click Disable Publishing and Distribution.

  3. Complete the steps in the Disable Publishing and Distribution Wizard.

Using Transact-SQL

Publishing and distributing can be disabled programmatically using replication stored procedures.

To disable publishing and distribution

  1. Stop all replication-related jobs. For a list of job names, see the "Agent Security Under SQL Server Agent" section of Replication Agent Security Model.

  2. 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.

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

  4. If the Publisher uses a remote Distributor, execute sp_dropdistributor.

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

  6. 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.

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

:::code language="sql" source="codesnippet/tsql/disable-publishing-and-d_1.sql":::

This example script disables publishing and distribution on a server that is a Publisher and Distributor and drops the distribution database.

:::code language="sql" source="codesnippet/tsql/disable-publishing-and-d_2.sql":::

Using Replication Management Objects (RMO)

To disable publishing and distribution

  1. Remove all subscriptions to publications that use the Distributor. For more information, see Delete a Pull Subscription and Delete a Push Subscription.

  2. Remove all publications that use the Distributor, and disable publishing for all databases if the Publisher and Distributor are on the same server. For more information, see Delete a Publication.

  3. Create a connection to the Distributor by using the xref:Microsoft.SqlServer.Management.Common.ServerConnection class.

  4. Create an instance of the xref:Microsoft.SqlServer.Replication.DistributionPublisher class. Specify the xref:Microsoft.SqlServer.Replication.DistributionPublisher.Name%2A property, and pass the xref:Microsoft.SqlServer.Management.Common.ServerConnection object from step 3.

  5. (Optional) Call the xref:Microsoft.SqlServer.Replication.ReplicationObject.LoadProperties%2A 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.

  6. Call the xref:Microsoft.SqlServer.Replication.DistributionPublisher.Remove%2A 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.

  7. Create an instance of the xref:Microsoft.SqlServer.Replication.ReplicationServer class. Pass the xref:Microsoft.SqlServer.Management.Common.ServerConnection object from step 3.

  8. Call the xref:Microsoft.SqlServer.Replication.ReplicationServer.UninstallDistributor%2A 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.

[!code-csHowTo#rmo_DropDistPub]

[!code-vbHowTo#rmo_vb_DropDistPub]

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

[!code-csHowTo#rmo_DropDistPubForce]

[!code-vbHowTo#rmo_vb_DropDistPubForce]

Related content