How to programmatically remove a push subscription?

Question

I have 4 databases, a publisher and 3 subscribers (transactional replication).

In case of an error in the publisher I want to:

1. delete all subscriptions
2. create a new publisher
3. make the two remaining databases subscribe to the new publisher.

(so that the program can continue normally and the 3 databases continue up to date)

I have the code to create a new publication and to create a new subscriber, but I don't know how to remove the old subscription.

I've read this Here:

At the Publisher on the publication database, execute sp_dropsubscription (Transact-SQL). Specify @publication and @subscriber. Specify a value of all for @article. (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.

At the Subscriber on the subscription database, execute sp_subscription_cleanup (Transact-SQL) to remove replication metadata in the subscription database.

but im my case, publisher and distributor are on the same machine. (and so I can't execute sp_dropsubsription right away, because the publisher machine is down)

edit: I've done sp_subscription_cleanup @publisher='PCONE', @publisher_db='TEST' (in one of the subscribers) but the subscription still is in the local subscriptions folder

and so this does not prevent the old publisher to send data when he comes back to life.

How can I prevent that? (how can I programmatically do the equivalent to right click a subscription and select delete)

Answer 1

If you were looking for the big hammer, sp_removedbreplication is it. This should do what you are asking, and remove all of the replication related goodies in the subscribing database, regardless of whether or not the publisher is accessible. Once the publisher/distributor come back up you can do the cleanup to remove the subscription on that side as well.

Like I said, this is the BIG HAMMER, so use it carefully.

Answer 2

Since its the publisher that pushes the data, removing the subscription is done at the publisher. Here's a script I use (Taken from Wrox). I first find the subscription I want to remove in part 1, and then use that information to drop the subscription. This script isn't meant to be run all at once.

**/* Part I on the publisher/distribution server */**
use distribution
go

/* Declare a table variable */
declare @subscription_push table
(
publisher_id smallint,
publisher_db sysname,
subscriber_db sysname,
subscription_int int,
sync_type tinyint,
status tinyint
);

insert into @subscription_push
select publisher_id, publisher_db, subscriber_db, subscription_type, sync_type, status
from distribution..MSsubscriptions
where subscription_type = 1 and status = 2

/* check the data */
select * from @subscription_push

/* Declare table variable that will store the publisher, the publication database, the type of publication and the name of the publication using
sp_helpsubscription */

declare @subscriberinfo table
(publisher sysname,
subscriber sysname);

insert into @subscriberinfo
select publisher, subscriber from distribution..MSsubscriber_info

/* check the data */
select * from @subscriberinfo

**/* Part II */**

/* Then remove the subscription from the publisher based on the information in the table @subscriberinfo*/
exec sp_dropsubscription @publication='publication_name', @article='article or all for all articles', @subscriber='subscribername', @destination_db='TEST'

go

Hope this helps. I have used in a test environment several times. Perhaps someone has a better way to remove push subscriptions?

Answer 3

I came across this old post looking for the same answer. There is an easier way without torpedoing all replication. After running sp_dropsubscription you must run a cleanup stored procedure at the subscriber. So, after running this:

USE PublisherDB
GO
exec sp_dropsubscription
    @publication = N'MyPublication', 
    @subscriber = N'SubscriberServer', 
    @destination_db = N'SubscriptionDatabase', 
    @article = N'all';

You will need to run this:

USE SubscriberDB
GO
exec sp_subscription_cleanup
    @publisher = N'PublisherServer', 
    @publisher_db = N'PublisherDB', 
    @publication = N'MyPublication';

I hope this helps someone else. This removes any metadata from the subscriber. Until the cleanup is run the subscription will show up in Local Subscriptions.