끌어오기 구독 삭제Delete a Pull Subscription

이 항목에서는 SQL Server 2017SQL Server 2017 , SQL Server Management StudioSQL Server Management Studio또는 RMO(복제 관리 개체)를 사용하여 Transact-SQLTransact-SQL에서 끌어오기 구독을 삭제하는 방법에 대해 설명합니다.This topic describes how to delete a pull subscription in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or Replication Management Objects (RMO).

항목 내용In This Topic

SQL Server Management Studio 사용 Using SQL Server Management Studio

게시자( 로컬 게시 SQL Server Management StudioSQL Server Management Studio폴더 사용) 또는 구독자( 로컬 구독 폴더 사용)에서 끌어오기 구독을 삭제합니다.Delete a pull subscription at the Publisher (from the Local Publications folder in SQL Server Management StudioSQL Server Management Studio) or the Subscriber (from the Local Subscriptions folder). 구독을 삭제해도 구독에서 개체나 데이터가 제거되지는 않으며 개체나 데이터는 수동으로 제거해야 합니다.Deleting a subscription does not remove objects or data from the subscription; they must be removed manually.

게시자에서 끌어오기 구독을 삭제하려면To delete a pull subscription at the Publisher

  1. SQL Server Management StudioSQL Server Management Studio에서 게시자에 연결한 다음 해당 서버 노드를 확장합니다.Connect to the Publisher in SQL Server Management StudioSQL Server Management Studio, and then expand the server node.

  2. 복제 폴더를 확장한 다음 로컬 게시 폴더를 확장합니다.Expand the Replication folder, and then expand the Local Publications folder.

  3. 삭제할 구독과 연결된 게시를 확장합니다.Expand the publication associated with the subscription you want to delete.

  4. 구독을 마우스 오른쪽 단추로 클릭한 다음 삭제를 클릭합니다.Right-click the subscription, and then click Delete.

  5. 확인 대화 상자에서 구독 정보를 삭제할 구독자에 연결할지 여부를 선택합니다.In the confirmation dialog box, select whether to connect to the Subscriber to delete subscription information. 구독자에 연결 확인란의 선택을 취소한 경우 나중에 구독자에 연결하여 해당 정보를 삭제해야 합니다.If you clear the Connect to Subscriber check box, you should connect to the Subscriber later to delete the information.

구독자에서 끌어오기 구독을 삭제하려면To delete a pull subscription at the Subscriber

  1. SQL Server Management StudioSQL Server Management Studio에서 구독자에 연결한 다음 해당 서버 노드를 확장합니다.Connect to the Subscriber in SQL Server Management StudioSQL Server Management Studio, and then expand the server node.

  2. 복제 폴더를 확장한 다음 로컬 구독 폴더를 확장합니다.Expand the Replication folder, and then expand the Local Subscriptions folder.

  3. 삭제할 구독을 마우스 오른쪽 단추로 클릭한 다음 삭제를 클릭합니다.Right-click the subscription you want to delete, and then click Delete.

  4. 확인 대화 상자에서 구독 정보를 삭제할 게시자에 연결할지 여부를 선택합니다.In the confirmation dialog box, select whether to connect to the Publisher to delete subscription information. 게시자에 연결 확인란의 선택을 취소한 경우 나중에 게시자에 연결하여 해당 정보를 삭제해야 합니다.If you clear the Connect to Publisher check box, you should connect to the Publisher later to delete the information.

Transact-SQL 사용 Using Transact-SQL

끌어오기 구독은 복제 저장 프로시저를 사용하여 프로그래밍 방식으로 삭제할 수 있습니다.Pull subscriptions can be deleted programmatically using replication stored procedures. 사용되는 저장 프로시저는 구독이 속한 게시 유형에 따라 달라집니다.The stored procedures used will depend on the type of publication to which the subscription belongs.

스냅숏 또는 트랜잭션 게시에 대한 끌어오기 구독을 삭제하려면To delete a pull subscription to a snapshot or transactional publication

  1. 구독 데이터베이스의 구독자에서 sp_droppullsubscription(Transact-SQL)을 실행합니다.At the Subscriber on the subscription database, execute sp_droppullsubscription (Transact-SQL). @publication, @publisher@publisher_db를 지정합니다.Specify @publication, @publisher, and @publisher_db.

  2. 게시 데이터베이스의 게시자에서 sp_dropsubscription(Transact-SQL)을 실행합니다.At the Publisher on the publication database, execute sp_dropsubscription (Transact-SQL). 이때 @publication@subscriber에서 사용 가능합니다.Specify @publication and @subscriber. @articleall 값을 지정합니다.Specify a value of all for @article. (옵션) 배포자에 액세스할 수 없으면 @ignore_distributor@ignore_distributor 을 지정하여 배포자에서 관련 개체를 제거하지 않고 구독을 삭제합니다.(Optional) If the Distributor cannot be accessed, specify a value of 1 for @ignore_distributor to delete the subscription without removing related objects at the Distributor.

병합 게시에 대한 끌어오기 구독을 삭제하려면To delete a pull subscription to a merge publication

  1. 구독 데이터베이스의 구독자에서 sp_dropmergepullsubscription(Transact-SQL)을 실행합니다.At the Subscriber on the subscription database, execute sp_dropmergepullsubscription (Transact-SQL). @publication, @publisher@publisher_db를 지정합니다.Specify @publication, @publisher, and @publisher_db.

  2. 게시 데이터베이스의 게시자에서 sp_dropmergesubscription(Transact-SQL)을 실행합니다.At the Publisher on the publication database, execute sp_dropmergesubscription (Transact-SQL). @publication, @subscriber@subscriber_db를 지정합니다.Specify @publication, @subscriber, and @subscriber_db. @subscription_typepull 값을 지정합니다.Specify a value of pull for @subscription_type. (옵션) 배포자에 액세스할 수 없으면 @ignore_distributor@ignore_distributor 을 지정하여 배포자에서 관련 개체를 제거하지 않고 구독을 삭제합니다.(Optional) If the Distributor cannot be accessed, specify a value of 1 for @ignore_distributor to delete the subscription without removing related objects at the Distributor.

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

다음은 트랜잭션 게시에 대한 끌어오기 구독을 삭제하는 예입니다.The following example deletes a pull subscription to a transactional publication. 첫 번째 일괄 처리는 구독자에서 실행되고 두 번째 일괄 처리는 게시자에서 실행됩니다.The first batch is executed at the Subscriber and the second is executed at the Publisher.

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

-- This is the batch executed at the Subscriber to drop 
-- a pull subscription to a transactional publication.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB     AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @publisher = $(PubServer);
SET @publicationDB = N'AdventureWorks2012';

USE [AdventureWorks2012Replica]
EXEC sp_droppullsubscription 
  @publisher = @publisher, 
  @publisher_db = @publicationDB, 
  @publication = @publication;
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".

-- This batch is executed at the Publisher to remove 
-- a pull or push subscription to a transactional publication.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriber = $(SubServer);

USE [AdventureWorks2012]
EXEC sp_dropsubscription 
  @publication = @publication, 
  @article = N'all',
  @subscriber = @subscriber;
GO

다음은 병합 게시에 대한 끌어오기 구독을 삭제하는 예입니다.The following example deletes a pull subscription to a merge publication. 첫 번째 일괄 처리는 구독자에서 실행되고 두 번째 일괄 처리는 게시자에서 실행됩니다.The first batch is executed at the Subscriber and the second is executed at the Publisher.

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

-- This batch is executed at the Subscriber to remove 
-- a merge pull subscription.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publication_db AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @publisher = $(PubServer);
SET @publication_db = N'AdventureWorks2012';

USE [AdventureWorks2012Replica]
EXEC sp_dropmergepullsubscription 
  @publisher = @publisher, 
  @publisher_db = @publication_db, 
  @publication = @publication;
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".

-- This batch is executed at the Publisher to remove 
-- a pull or push subscription to a merge publication.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @subscriber = $(SubServer);
SET @subscriptionDB = N'AdventureWorks2012Replica';

USE [AdventureWorks2012]
EXEC sp_dropmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB;
GO

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

RMO(복제 관리 개체)를 사용하여 프로그래밍 방식으로 끌어오기 구독을 삭제할 수 있습니다.You can delete pull subscriptions programmatically by using Replication Management Objects (RMO). 끌어오기 구독을 삭제하는 데 사용되는 RMO 클래스는 끌어오기 구독이 구독하는 게시의 유형에 따라 다릅니다.The RMO classes that you use to delete a pull subscription depend on the type of publication to which the pull subscription is subscribed.

스냅숏 또는 트랜잭션 게시에 대한 끌어오기 구독을 삭제하려면To delete a pull subscription to a snapshot or transactional publication

  1. ServerConnection 클래스를 사용하여 구독자 및 게시자 둘 다의 연결을 만듭니다.Create connections to both the Subscriber and Publisher by using the ServerConnection Class.

  2. TransPullSubscription 클래스의 인스턴스를 만들고 PublicationName, DatabaseName, PublisherNamePublicationDBName 속성을 설정합니다.Create an instance of the TransPullSubscription class, and set the PublicationName, DatabaseName, PublisherName, and PublicationDBName properties. 1단계의 구독자 연결을 사용하여 ConnectionContext 속성을 설정합니다.Use the Subscriber connection from step 1 to set the ConnectionContext property.

  3. IsExistingObject 속성을 통해 구독이 존재하는지 확인합니다.Check the IsExistingObject property to verify that the subscription exists. 이 속성의 값이 false이면 2단계에서 구독 속성이 올바르게 정의되지 않았거나 구독이 없는 것입니다.If the value of this property is false, either the subscription properties in step 2 were defined incorrectly or the subscription does not exist.

  4. Remove 메서드를 호출합니다.Call the Remove method.

  5. 1단계에서 만든 게시자 연결을 사용하여 TransPublication 클래스의 인스턴스를 만듭니다.Create an instance of the TransPublication class by using the Publisher connection from step 1. Name, DatabaseNameConnectionContext를 지정합니다.Specify Name, DatabaseName and ConnectionContext.

  6. LoadProperties 메서드를 호출합니다.Call the LoadProperties method. 이 메서드가 false를 반환하는 경우 5단계에서 지정한 속성이 올바르지 않거나 서버에 게시가 없는 것입니다.If this method returns false, either the properties specified in step 5 are incorrect or the publication does not exist on the server.

  7. RemovePullSubscription 메서드를 호출합니다.Call the RemovePullSubscription method. subscribersubscriberDB 매개 변수에 구독자의 이름과 구독 데이터베이스를 지정합니다.Specify the name of the Subscriber and the subscription database for the subscriber and subscriberDB parameters.

병합 게시에 대한 끌어오기 구독을 삭제하려면To delete a pull subscription to a merge publication

  1. ServerConnection 클래스를 사용하여 구독자 및 게시자 둘 다의 연결을 만듭니다.Create connections to both the Subscriber and Publisher by using the ServerConnection Class.

  2. MergePullSubscription 클래스의 인스턴스를 만들고 PublicationName, DatabaseName, PublisherNamePublicationDBName 속성을 설정합니다.Create an instance of the MergePullSubscription class, and set the PublicationName, DatabaseName, PublisherName, and PublicationDBName properties. 1단계의 연결을 사용하여 ConnectionContext 속성을 설정합니다.Use the connection from step 1 to set the ConnectionContext property.

  3. IsExistingObject 속성을 통해 구독이 존재하는지 확인합니다.Check the IsExistingObject property to verify that the subscription exists. 이 속성의 값이 false이면 2단계에서 구독 속성이 올바르게 정의되지 않았거나 구독이 없는 것입니다.If the value of this property is false, either the subscription properties in step 2 were defined incorrectly or the subscription does not exist.

  4. Remove 메서드를 호출합니다.Call the Remove method.

  5. 1단계에서 만든 게시자 연결을 사용하여 MergePublication 클래스의 인스턴스를 만듭니다.Create an instance of the MergePublication class by using the Publisher connection from step 1. Name, DatabaseNameConnectionContext를 지정합니다.Specify Name, DatabaseName and ConnectionContext.

  6. LoadProperties 메서드를 호출합니다.Call the LoadProperties method. 이 메서드가 false를 반환하는 경우 5단계에서 지정한 속성이 올바르지 않거나 서버에 게시가 없는 것입니다.If this method returns false, either the properties specified in step 5 are incorrect or the publication does not exist on the server.

  7. RemovePullSubscription 메서드를 호출합니다.Call the RemovePullSubscription method. subscribersubscriberDB 매개 변수에 구독자의 이름과 구독 데이터베이스를 지정합니다.Specify the name of the Subscriber and the subscription database for the subscriber and subscriberDB parameters.

예(RMO) Examples (RMO)

이 예에서는 트랜잭션 게시에 대한 끌어오기 구독을 삭제하고 게시자에서 구독 등록을 제거합니다.This example deletes a pull subscription to a transactional publication and removes the subscription registration at the Publisher.

// Define the Publisher, publication, and databases.
string publicationName = "AdvWorksProductTran";
string publisherName = publisherInstance;
string subscriberName = subscriberInstance;
string subscriptionDbName = "AdventureWorks2012Replica";
string publicationDbName = "AdventureWorks2012";

//Create connections to the Publisher and Subscriber.
ServerConnection subscriberConn = new ServerConnection(subscriberName);
ServerConnection publisherConn = new ServerConnection(publisherName);

// Create the objects that we need.
TransPublication publication;
TransPullSubscription subscription;

try
{
	// Connect to the Subscriber.
	subscriberConn.Connect();

	// Define the pull subscription.
	subscription = new TransPullSubscription();
	subscription.ConnectionContext = subscriberConn;
	subscription.PublisherName = publisherName;
	subscription.PublicationName = publicationName;
	subscription.PublicationDBName = publicationDbName;
	subscription.DatabaseName = subscriptionDbName;

	// Define the publication.
	publication = new TransPublication();
	publication.Name = publicationName;
	publication.DatabaseName = publicationDbName;
	publication.ConnectionContext = publisherConn;

	// Delete the pull subscription, if it exists.
	if (subscription.IsExistingObject)
	{
		if (publication.LoadProperties())
		{
                     // Remove the pull subscription registration at the Publisher.
			publication.RemovePullSubscription(subscriberName, subscriptionDbName);
		}
		else
		{
			// Do something here if the publication does not exist.
			throw new ApplicationException(String.Format(
				"The publication '{0}' does not exist on {1}.",
				publicationName, publisherName));
		}
                 // Delete the pull subscription at the Subscriber.
                 subscription.Remove();
	}
	else
	{
		throw new ApplicationException(String.Format(
			"The subscription to {0} does not exist on {1}",
			publicationName, subscriberName));
	}
}
catch (Exception ex)
{
	// Implement the appropriate error handling here.
	throw new ApplicationException(String.Format(
		"The subscription to {0} could not be deleted.", publicationName), ex);
}
finally
{
	subscriberConn.Disconnect();
	publisherConn.Disconnect();
}
' Define the Publisher, publication, and databases.
Dim publicationName As String = "AdvWorksProductTran"
Dim publisherName As String = publisherInstance
Dim subscriberName As String = subscriberInstance
Dim subscriptionDbName As String = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

'Create connections to the Publisher and Subscriber.
Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)
Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim publication As TransPublication
Dim subscription As TransPullSubscription

Try
    ' Connect to the Subscriber.
    subscriberConn.Connect()

    ' Define the pull subscription.
    subscription = New TransPullSubscription()
    subscription.ConnectionContext = subscriberConn
    subscription.PublisherName = publisherName
    subscription.PublicationName = publicationName
    subscription.PublicationDBName = publicationDbName
    subscription.DatabaseName = subscriptionDbName

    ' Define the publication.
    publication = New TransPublication()
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName
    publication.ConnectionContext = publisherConn

    ' Delete the pull subscription, if it exists.
    If subscription.IsExistingObject Then
       
        If publication.LoadProperties() Then
            ' Remove the pull subscription registration at the Publisher.
            publication.RemovePullSubscription(subscriberName, subscriptionDbName)
        Else
            ' Do something here if the publication does not exist.
            Throw New ApplicationException(String.Format( _
             "The publication '{0}' does not exist on {1}.", _
             publicationName, publisherName))
        End If
        ' Delete the pull subscription at the Subscriber.
        subscription.Remove()
    Else
        Throw New ApplicationException(String.Format( _
         "The subscription to {0} does not exist on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Implement the appropriate error handling here.
    Throw New ApplicationException(String.Format( _
        "The subscription to {0} could not be deleted.", publicationName), ex)
Finally
    subscriberConn.Disconnect()
    publisherConn.Disconnect()
End Try

이 예에서는 병합 게시에 대한 끌어오기 구독을 삭제하고 게시자에서 구독 등록을 제거합니다.This example deletes a pull subscription to a merge publication and removes the subscription registration at the Publisher.

// Define the Publisher, publication, and databases.
string publicationName = "AdvWorksSalesOrdersMerge";
string publisherName = publisherInstance;
string subscriberName = subscriberInstance;
string subscriptionDbName = "AdventureWorks2012Replica";
string publicationDbName = "AdventureWorks2012";

//Create connections to the Publisher and Subscriber.
ServerConnection subscriberConn = new ServerConnection(subscriberName);
ServerConnection publisherConn = new ServerConnection(publisherName);

// Create the objects that we need.
MergePublication publication;
MergePullSubscription subscription;

try
{
	// Connect to the Subscriber.
	subscriberConn.Connect();

	// Define the pull subscription.
	subscription = new MergePullSubscription();
	subscription.ConnectionContext = subscriberConn;
	subscription.PublisherName = publisherName;
	subscription.PublicationName = publicationName;
	subscription.PublicationDBName = publicationDbName;
	subscription.DatabaseName = subscriptionDbName;

	// Define the publication.
	publication = new MergePublication();
	publication.Name = publicationName;
	publication.DatabaseName = publicationDbName;
	publication.ConnectionContext = publisherConn;

	// Delete the pull subscription, if it exists.
	if (subscription.IsExistingObject)
	{
		// Delete the pull subscription at the Subscriber.
		subscription.Remove();

		if (publication.LoadProperties())
		{
			publication.RemovePullSubscription(subscriberName, subscriptionDbName);
		}
		else
		{
			// Do something here if the publication does not exist.
			throw new ApplicationException(String.Format(
				"The publication '{0}' does not exist on {1}.",
				publicationName, publisherName));
		}
	}
	else
	{
		throw new ApplicationException(String.Format(
			"The subscription to {0} does not exist on {1}",
			publicationName, subscriberName));
	}
}
catch (Exception ex)
{
	// Implement the appropriate error handling here.
	throw new ApplicationException(String.Format(
		"The subscription to {0} could not be deleted.", publicationName), ex);
}
finally
{
	subscriberConn.Disconnect();
	publisherConn.Disconnect();
}
' Define the Publisher, publication, and databases.
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publisherName As String = publisherInstance
Dim subscriberName As String = subscriberInstance
Dim subscriptionDbName As String = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

'Create connections to the Publisher and Subscriber.
Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)
Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim publication As MergePublication
Dim subscription As MergePullSubscription

Try
    ' Connect to the Subscriber.
    subscriberConn.Connect()

    ' Define the pull subscription.
    subscription = New MergePullSubscription()
    subscription.ConnectionContext = subscriberConn
    subscription.PublisherName = publisherName
    subscription.PublicationName = publicationName
    subscription.PublicationDBName = publicationDbName
    subscription.DatabaseName = subscriptionDbName

    ' Define the publication.
    publication = New MergePublication()
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName
    publication.ConnectionContext = publisherConn

    ' Delete the pull subscription, if it exists.
    If subscription.IsExistingObject Then

        ' Delete the pull subscription at the Subscriber.
        subscription.Remove()

        If publication.LoadProperties() Then
            publication.RemovePullSubscription(subscriberName, subscriptionDbName)
        Else
            ' Do something here if the publication does not exist.
            Throw New ApplicationException(String.Format( _
             "The publication '{0}' does not exist on {1}.", _
             publicationName, publisherName))
        End If
    Else
        Throw New ApplicationException(String.Format( _
         "The subscription to {0} does not exist on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Implement the appropriate error handling here.
    Throw New ApplicationException(String.Format( _
        "The subscription to {0} could not be deleted.", publicationName), ex)
Finally
    subscriberConn.Disconnect()
    publisherConn.Disconnect()
End Try

참고 항목See Also

게시 구독 Subscribe to Publications
Replication Security Best Practices Replication Security Best Practices