How to remove replication setup after the instance is take off from an availability group and a failover cluster

Question

There were two replicas (primary Server1 and secondary Server2) inside the availability group and the primary replica was the publisher of a transactional replication and a snapshot replication.
Then we added another secondary replica Server3, redirected the publisher to the listener from the Server1 instance name, manually failed over to Server3 so that the replication did not break.
Now we would like to delete Server1. I have removed it from the availability group and the failover cluster.
To remove Server1 from the replication setup is quite tricky. I read from the internet that deleting replication from Server1 would deleting some meta data objects inside the distributor database and break the replication even though the publication is from Server3 (the new primary replica) and Server1 is outside the availability group and the cluster.
Could you give some instructions on how to remove Server1 from the replication setup? The distributor settings still have Server1 as one of the publishers. To re-initialise the replication takes 14 hours, so is only the last resort.

Answer 1

Hi @Wendy1 ，

Deleting a publication does not remove published objects from the publication database or the corresponding objects from the subscription database

Please check out this doc:
Delete a Publication

To delete a single publication, execute sp_droppublication at the Publisher on the publication database.

To delete all publications in and remove all replication objects from a published database, execute sp_removedbreplication at the Publisher. Specify a value of tran for @type .

If this database has no other publications, execute sp_replicationdboption to disable publication of the current database using snapshot or transactional replication.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

According to https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/maintaining-an-always-on-publication-database-sql-server?view=sql-server-ver15#remove-original-publisher, all I need to do is to turn off the old primary replica host.