게시 및 배포 해제Disable Publishing and Distribution

이 항목에서는 SQL Server 2017SQL Server 2017 , SQL Server Management StudioSQL Server Management Studio또는 RMO(복제 관리 개체)를 사용하여 Transact-SQLTransact-SQL에서 게시 및 배포를 해제하는 방법에 대해 설명합니다.This topic describes how to disable publishing and distribution in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-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.

    항목 내용In This Topic

  • 시작하기 전에:Before you begin:

    필수 구성 요소Prerequisites

  • 다음을 사용하여 게시 및 배포를 해제하려면To disable publishing and distribution, using:

    SQL Server Management StudioSQL Server Management Studio

    Transact-SQLTransact-SQL

    RMO(복제 관리 개체)Replication Management Objects (RMO)

시작하기 전 주의 사항 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. 자세한 내용은 데이터베이스 스냅숏(SQL Server)을 참조하세요.For more information, see Database Snapshots (SQL Server).

SQL Server Management Studio 사용 Using SQL Server Management Studio

게시 및 배포 해제 마법사를 사용하여 게시 및 배포를 해제합니다.Disable publishing and distribution by using the Disable Publishing and Distribution Wizard.

게시 및 배포를 해제하려면To disable publishing and distribution

  1. MicrosoftMicrosoft SQL Server Management StudioSQL Server Management Studio에서 해제할 게시자나 배포자에 연결한 다음 해당 서버 노드를 확장합니다.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. 복제 폴더를 마우스 오른쪽 단추로 클릭한 다음, 게시 및 배포 해제를 클릭합니다.Right-click the Replication folder, and then click Disable Publishing and Distribution.

  3. 게시 및 배포 해제 마법사의 단계를 완료합니다.Complete the steps in the Disable Publishing and Distribution Wizard.

Transact-SQL 사용 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. 작업 이름 목록은 복제 에이전트 보안 모델의 "SQL Server 에이전트의 에이전트 보안" 섹션을 참조하세요.For a list of job names, see the "Agent Security Under SQL Server Agent" section of Replication Agent Security Model.

  2. 구독 데이터베이스의 각 구독자에서 sp_removedbreplication 을 실행하여 데이터베이스에서 복제 개체를 제거합니다.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. 게시 데이터베이스의 게시자에서 sp_removedbreplication 을 실행하여 데이터베이스에서 복제 개체를 제거합니다.At the Publisher on the publication database, execute sp_removedbreplication to remove replication objects from the database.

  4. 게시자가 원격 배포자를 사용하는 경우 sp_dropdistributor를 실행합니다.If the Publisher uses a remote Distributor, execute sp_dropdistributor.

  5. 배포자에서 sp_dropdistpublisher를 실행합니다.At the Distributor, execute sp_dropdistpublisher. 이 저장 프로시저는 배포자에 등록된 각 게시자에 대해 한 번만 실행해야 합니다.This stored procedure should be run once for each Publisher registered at the Distributor.

  6. 배포자에서 sp_dropdistributiondb 를 실행하여 배포 데이터베이스를 삭제합니다.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. 배포자에서 sp_dropdistributor 를 실행하여 서버에서 배포자 지정을 제거합니다.At the Distributor, execute sp_dropdistributor to remove the Distributor designation from the server.

    참고

    모든 복제 게시 및 배포 개체가 삭제되지 않은 상태로 sp_dropdistpublishersp_dropdistributor를 실행하면 오류가 반환됩니다.If all replication publishing and distribution objects are not dropped before you execute sp_dropdistpublisher and sp_dropdistributor, these procedures will return an error. 게시자 또는 배포자를 삭제할 때 모든 복제 관련 개체를 삭제하려면 @no_checks1을 참조하세요.To drop all replication-related objects when a Publisher or Distributor is dropped, the @no_checks parameter must be set to 1. 게시자 또는 배포자가 오프라인이거나 연결할 수 없는 경우에는 @ignore_distributor 매개 변수를 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.

예(Transact-SQL) Examples (Transact-SQL)

다음 스크립트 예에서는 구독 데이터베이스에서 복제 개체를 제거합니다.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

다음 스크립트 예에서는 게시자 및 배포자 역할의 서버에서 게시 및 배포를 해제하고 배포 데이터베이스를 삭제합니다.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

RMO(복제 관리 개체) 사용 Using Replication Management Objects (RMO)

게시 및 배포를 해제하려면To disable publishing and distribution

  1. 배포자를 사용하는 게시에 대한 모든 구독을 제거합니다.Remove all subscriptions to publications that use the Distributor. 자세한 내용은 Delete a Pull SubscriptionDelete a Push Subscription를 참조하세요.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. 자세한 내용은 Delete a Publication을 참조하세요.For more information, see Delete a Publication.

  3. ServerConnection 클래스를 사용하여 배포자 연결을 만듭니다.Create a connection to the Distributor by using the ServerConnection class.

  4. DistributionPublisher 클래스의 인스턴스를 만듭니다.Create an instance of the DistributionPublisher class. Name 속성을 지정하고 3단계의 ServerConnection 개체를 전달합니다.Specify the Name property, and pass the ServerConnection object from step 3.

  5. (옵션) LoadProperties 메서드를 호출하여 개체의 속성을 얻고 게시자가 있는지 확인합니다.(Optional) Call the LoadProperties method to get the properties of the object and verify that the Publisher exists. 이 메서드가 false를 반환하면 4단계에서 지정한 게시자 이름이 올바르지 않거나 이 배포자에서 게시자를 사용하지 않는 것입니다.If this method returns false, the Publisher name set in step 4 was incorrect or the Publisher is not used by this Distributor.

  6. Remove 메서드를 호출합니다.Call the Remove method. 게시자와 배포자가 다른 서버에 있으며 게시자에 게시가 더 이상 존재하지 않는지 먼저 확인하지 않고 배포자에서 게시자를 제거해야 하는 경우 forcetrue 값을 전달합니다.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. ReplicationServer 클래스의 인스턴스를 만듭니다.Create an instance of the ReplicationServer class. 3단계의 ServerConnection 개체를 전달합니다.Pass the ServerConnection object from step 3.

  8. UninstallDistributor 메서드를 호출합니다.Call the UninstallDistributor method. 모든 로컬 게시 데이터베이스가 해제되었는지, 그리고 배포 데이터베이스가 제거되었는지 확인하지 않고 배포자의 모든 복제 개체를 제거하려면 forcetrue 값을 전달합니다.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.

예(RMO) 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.
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

다음 예에서는 먼저 로컬 게시 데이터베이스를 해제하거나 배포 데이터베이스를 삭제하지 않고 배포자를 제거합니다.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

참고 항목See Also

복제 관리 개체 개념 Replication Management Objects Concepts
복제 시스템 저장 프로시저 개념 Replication System Stored Procedures Concepts