Return to Question

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 primary DB which replicates to a handful of secondaries, so that if the primary goes down, we can promote a secondary to be the new primary. 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 secondaries to disable/remove the subscription to the old primary if the old primary 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 secondaries as this risks data inconsistency. We'd likely clean-slate the old primary, so it's not an issue if its replication setup is throwing errors.

I've tried 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 primary.

So is there a script approach to permanently kill the subscription just from the secondary?

Additional information

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.

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.

I've tried 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?

Additional information

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.

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.

I've tried 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?

Additional information

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.

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 primary DB which replicates to a handful of secondaries, so that if the primary goes down, we can promote a secondary to be the new primary. 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 secondaries to disable/remove the subscription to the old primary if the old primary 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 secondaries as this risks data inconsistency. We'd likely clean-slate the old primary, so it's not an issue if its replication setup is throwing errors.

I've tried 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 primary.

So is there a script approach to permanently kill the subscription just from the secondary?

Additional information

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.

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.

I've tried 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 - https://learn.microsoft.com/en-us/sql/relational-databases/replication/disable-publishing-and-distribution?view=sql-server-2017 - but that assumes access to the master.

So is there a script approach to permanently kill the subscription just from the slave?

EDIT: 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.

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.

I've tried 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?

Additional information 

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.