I have 4 databases, a publisher and 3 subscribers (transactional replication).
In case of an error in the publisher I want to:
- delete all subscriptions
- create a new publisher
- 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)
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)