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.


It sounds like what you're asking for is sp_removedbreplication, which you would run like:

EXEC sp_removedbreplication @dbname = 'MySubscriberDb';

From docs.microsoft.com:

This stored procedure removes all replication objects ...on the subscription database on the Subscriber instance of SQL Server. Execute in the appropriate database, or if the execution is in the context of another database on the same instance, specify the database where the replication objects should be removed. This procedure does not remove objects from other databases, such as the distribution database.

That said, doing just this may not resolve your issue, depending on your configuration.

Option 1) Change your replication topology

I'd also recommend configuring your Distribution Agent to run as a pull subscription. This will enable you to disable the SQL Agent Jobs associated with loading any snapshots or transactions. Because the job is on your subscriber, you will be able to disable it, even when the publisher/distributor are offline. If/when the publisher/distributor comes online, you don't need to worry about transactions/snapshots being loaded to the subscriber, until you explicitly re-enable the Distribution Agent job. Note that if you do this, you would not need to use sp_removedbreplication.

Option 2) Change your replication topology another way

Is your distribution database on the same server as your primary? If you move your distribution database & jobs to a dedicated distributor, then this provides similar benefit to Option 1. Additionally, you would have access to the distribution database in order to add/remove subscription information from the replication metadata.

Option 3) Revoke permissions

You could also evaluate the permissions in use by your Distribution Agents. By revoking permissions for a remote Distribution Agent, you can prevent the distribution agent from logging in to the database to re-establish replication.

Option 4) Is replication really the right choice?

While you're asking about replication, I'd challenge you to ask if replication is the right choice for your need. It sounds like you're using replication for High Availability / Disaster Recovery (HA/DR). Replication isn't a great choice for HA/DR. You may find that Availability Groups make for a better HA/DR solution. They come with different challenges, but are specifically built to solve HA/DR needs, and ensure only a single writable primary in order to avoid this particular challenge.

| improve this answer | |
  • I've tried that, and like sp_subscription_cleanup, it removes it, but then it comes back after a few minutes and data is replicated from the old master to the server. It seems the distributor is allowed to reinstate the subscription even if it finds it removed on the slave server. I wonder if there's a setting to prevent it reinstating the subscription. – andrewb Oct 19 '18 at 5:12
  • 1
    @andrewb I've made some significant additions to my answer--hope that helps. – AMtwo Oct 19 '18 at 14:14
  • Thanks, that's really helpful. Given our constraints, pull replication may be the best option. Unfortunately the licensing cost of SQL Enterprise isn't an option for us, as nice as it would be. – andrewb Oct 22 '18 at 4:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.