Deshabilitar la publicación y la distribuciónDisable Publishing and Distribution

SE APLICA A: síSQL Server síAzure SQL Database (solo Instancia administrada) noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

En este tema se describe cómo deshabilitar la publicación y la distribución en SQL ServerSQL Server mediante SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQLo Replication Management Objects (RMO).This topic describes how to disable publishing and distribution in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or Replication Management Objects (RMO).

Puede hacer lo siguiente:You can do the following:

  • Eliminar todas las bases de datos del distribuidor.Delete all distribution databases on the Distributor.

  • Deshabilitar todos los publicadores que utilizan el distribuidor y eliminar todas las publicaciones en esos publicadores.Disable all Publishers that use the Distributor and delete all publications on those Publishers.

  • Eliminar todas las suscripciones a las publicaciones.Delete all subscriptions to the publications. Los datos de las bases de datos de publicaciones y de suscripciones no se eliminarán; sin embargo, pierden su relación de sincronización con todas las bases de datos de publicaciones.Data in the publication and subscription databases will not be deleted; however, it loses its synchronization relationship to any publication databases. Si desea eliminar los datos del suscriptor, debe hacerlo de forma manual.If you want the data at the Subscriber to be deleted, you must delete it manually.

En este temaIn This Topic

Antes de comenzarBefore You Begin

Requisitos previosPrerequisites

  • Para deshabilitar la publicación y la distribución, todas las bases de datos de distribución y de publicaciones deben estar en línea.To disable publishing and distribution, all distribution and publication databases must be online. Si existe alguna instantánea de base de datos para las bases de datos de distribución o de publicaciones, deben quitarse antes de deshabilitar la publicación y distribución.If any database snapshots exist for distribution or publication databases, they must be dropped before disabling publishing and distribution. Una instantánea de base de datos es una copia de solo lectura sin conexión de una base de datos y no está relacionada con una instantánea de replicación.A database snapshot is a read-only offline copy of a database and is not related to a replication snapshot. Para más información, vea Instantáneas de base de datos (SQL Server).For more information, see Database Snapshots (SQL Server).

Uso de SQL Server Management StudioUsing SQL Server Management Studio

Deshabilite la publicación y la distribución con el Asistente para deshabilitar la publicación y distribución.Disable publishing and distribution by using the Disable Publishing and Distribution Wizard.

Para deshabilitar la publicación y la distribuciónTo disable publishing and distribution

  1. Conéctese al publicador o el distribuidor que desea deshabilitar en MicrosoftMicrosoft SQL Server Management StudioSQL Server Management Studioy, a continuación, expanda el nodo de servidor.Connect to the Publisher or Distributor you want to disable in MicrosoftMicrosoft SQL Server Management StudioSQL Server Management Studio, and then expand the server node.

  2. Haga clic con el botón secundario en la carpeta Replicación y, a continuación, haga clic en Deshabilitar publicación y distribución.Right-click the Replication folder, and then click Disable Publishing and Distribution.

  3. Siga todos los pasos del Asistente para deshabilitar la publicación y distribución.Complete the steps in the Disable Publishing and Distribution Wizard.

Usar Transact-SQLUsing Transact-SQL

Puede deshabilitarse la publicación y la distribución mediante programación con los procedimientos almacenados de la replicación.Publishing and distributing can be disabled programmatically using replication stored procedures.

Para deshabilitar la publicación y la distribuciónTo disable publishing and distribution

  1. Detenga todos los trabajos relacionados con replicación.Stop all replication-related jobs. Para obtener una lista de nombres de tarea, vea la sección "Seguridad de agentes con el Agente SQL Server" de Modelo de seguridad del Agente de replicación.For a list of job names, see the "Agent Security Under SQL Server Agent" section of Replication Agent Security Model.

  2. En cada suscriptor de la base de datos de suscripciones, ejecute sp_removedbreplication para quitar los objetos de replicación de dicha base de datos.At each Subscriber on the subscription database, execute sp_removedbreplication to remove replication objects from the database. Este procedimiento almacenado no quitará los trabajos de replicación del distribuidor.This stored procedure will not remove replication jobs at the Distributor.

  3. En la base de datos de publicación del publicador, ejecute sp_removedbreplication para quitar los objetos de replicación de dicha base de datos.At the Publisher on the publication database, execute sp_removedbreplication to remove replication objects from the database.

  4. Si el publicador usa un distribuidor remoto, ejecute sp_dropdistributor.If the Publisher uses a remote Distributor, execute sp_dropdistributor.

  5. En el distribuidor, ejecute sp_dropdistpublisher.At the Distributor, execute sp_dropdistpublisher. Este procedimiento almacenado se debería ejecutar una vez para cada publicador registrado en el distribuidor.This stored procedure should be run once for each Publisher registered at the Distributor.

  6. En el distribuidor, ejecute sp_dropdistributiondb para eliminar la base de datos de distribución.At the Distributor, execute sp_dropdistributiondb to delete the distribution database. Este procedimiento almacenado se debería ejecutar una vez para cada base de datos de distribución en el distribuidor.This stored procedure should be run once for each distribution database at the Distributor. Esto también quita cualquier trabajo del Agente de lectura de cola asociado a la base de datos de distribución.This also removes any Queue Reader Agent jobs associated with the distribution database.

  7. En el distribuidor, ejecute sp_dropdistributor para quitar la designación de distribuidor del servidor.At the Distributor, execute sp_dropdistributor to remove the Distributor designation from the server.

    Nota

    Si no se quitan todos los objetos de distribución y publicación de replicación antes de ejecutar sp_dropdistpublisher y sp_dropdistributor, estos procedimientos devolverán un error.If all replication publishing and distribution objects are not dropped before you execute sp_dropdistpublisher and sp_dropdistributor, these procedures will return an error. Para quitar todos los objetos relacionados con la replicación cuando se quita un publicador o un distribuidor, el parámetro @no_checks debe estar establecido en 1.To drop all replication-related objects when a Publisher or Distributor is dropped, the @no_checks parameter must be set to 1. Si un publicador o un distribuidor no tienen conexión o son inaccesibles, el parámetro @ignore_distributor puede estar establecido en 1 para que se puedan quitar; pero todos los objetos de publicación y distribución omitidos se deben quitar de forma manual.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.

Ejemplos (Transact-SQL)Examples (Transact-SQL)

Este script de ejemplo quita los objetos de replicación de la base de datos de suscripciones.This example script removes replication objects from the subscription database.

-- Remove replication objects from the subscription database on MYSUB.
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'AdventureWorks2012Replica'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO

Este script de ejemplo deshabilita la publicación y la distribución en un servidor que sea publicador y distribuidor, y quita la base de datos de distribución.This example script disables publishing and distribution on a server that is a Publisher and Distributor and drops the distribution 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

Uso de Replication Management Objects (RMO)Using Replication Management Objects (RMO)

Para deshabilitar la publicación y la distribuciónTo disable publishing and distribution

  1. Quite todas las suscripciones a las publicaciones que usan el Distribuidor.Remove all subscriptions to publications that use the Distributor. Para obtener más información, consulte Delete a Pull Subscription y Delete a Push Subscription.For more information, see Delete a Pull Subscription and Delete a Push Subscription.

  2. Quite todas las publicaciones que usan el Distribuidor y deshabilite la publicación para todas las bases de datos si el Publicador y el Distribuidor están en el mismo servidor.Remove all publications that use the Distributor, and disable publishing for all databases if the Publisher and Distributor are on the same server. Para más información, consulte Delete a Publication.For more information, see Delete a Publication.

  3. Cree una conexión al distribuidor mediante la clase ServerConnection .Create a connection to the Distributor by using the ServerConnection class.

  4. Cree una instancia de la clase DistributionPublisher .Create an instance of the DistributionPublisher class. Especifique la propiedad Name y pase el objeto ServerConnection del paso 3.Specify the Name property, and pass the ServerConnection object from step 3.

  5. (Opcional) Llame al método LoadProperties para obtener las propiedades del objeto y compruebe que el Publicador existe.(Optional) Call the LoadProperties method to get the properties of the object and verify that the Publisher exists. Si este método devuelve false, el nombre del Publicador establecido en el paso 4 era incorrecto o este Distribuidor no usa el Publicador.If this method returns false, the Publisher name set in step 4 was incorrect or the Publisher is not used by this Distributor.

  6. Llame al método Remove .Call the Remove method. Pase un valor de true para force si el Publicador y el Distribuidor están en servidores diferentes, y cuando el Publicador se debería desinstalar en el Distribuidor sin comprobar primero que las publicaciones ya no existen en el Publicador.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. Cree una instancia de la clase ReplicationServer .Create an instance of the ReplicationServer class. Pase el objeto ServerConnection del paso 3.Pass the ServerConnection object from step 3.

  8. Llame al método UninstallDistributor .Call the UninstallDistributor method. Pase un valor de true para que force quite todos los objetos de replicación en el Distribuidor sin comprobar primero que todas las bases de datos de publicación locales se han deshabilitado y se han desinstalado las bases de datos de distribución.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.

Ejemplos (RMO)Examples (RMO)

Este ejemplo quita el registro del Publicador en el Distribuidor, coloca la base de datos de distribución y desinstala el Distribuidor.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.
string publisherName = publisherInstance;
string distributorName = publisherInstance;
string distributionDbName = "distribution";
string publicationDbName = "AdventureWorks2012";

// 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 AdventureWorks2012 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 = "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() 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

Este ejemplo desinstala el Distribuidor sin deshabilitar primero las bases de datos de publicación locales o quitar la base de datos de distribución.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.
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

Consulte tambiénSee Also

Replication Management Objects Concepts Replication Management Objects Concepts
Replication System Stored Procedures Concepts (Conceptos sobre los procedimientos almacenados del sistema de replicación)Replication System Stored Procedures Concepts