We've been building a replication network of DBs to give us some degree of Always On without paying the full price tag. Currently we have a publisher DB which replicates to a handful of subscribers, so that if the publisher goes down, we can promote a subscriber to be the new publisher. As is, this is a tedious manual process, but we want to start scripting elements of it so that the process is quicker and closer to being automated.
What we're currently wanting to do is make a script for subscribers to disable/remove the subscription to the old publisher if the old publisher goes down. This is due to the chance that DBs sometimes come back online after being offline for some time - in this scenario, we don't want it to have any further replication impact on the subscribers as this risks data inconsistency. We'd likely clean-slate the old publisher, so it's not an issue if its replication setup is throwing errors.
sp_subscription_cleanup which briefly shows the subscription as gone, but it comes back and data continues to be distributed to it from the publisher. I've found an MS doc on this, but that assumes access to the publisher.
So is there a script approach to permanently kill the subscription just from the subscriber?
I note that you can right click on the subscription and delete it there, but I can't see from that menu the script that it's running. The subscription has also returned.