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 master DB which replicates to a handful of slaves, so that if the master goes down, we can promote a slave to be the new master. 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 slaves to disable/remove the subscription to the old master if the old master 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 slaves as this risks data inconsistency. We'd likely clean-slate the old master, 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 master.
So is there a script approach to permanently kill the subscription just from the slave?
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.