How can I permanently remove replication by script if I only have access to the subscriber?

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

Answer 1

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

EXEC sp_removedbreplication @dbname = 'MySubscriberDb';

From learn.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.