Transactional replication removal sequence
I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012
Transactional Replication.  I have read articles about using SSMS as well as using replication stored procedures
and some procedures use SQLCMD or just regular TSQL executed in SSMS.  I have also read articles where
people said all you really need is connect to the Publisher instance, find the publication you want
to remove and choose "Delete" and everything will be taken care of behind the scene.  I'm not sure about
this yet. I have three SQL servers that participate in transactional replication.  SQL-P (publisher), 
SQL-D (distributor) and SQL-S (subscriber).  Do I need to connect to the distributor instance and the
subscriber instance when removing transactional replication or is it just really connecting to the
publisher and click delete on the publication?  I need someone to clarify for this for me and point
me to the right steps.  I want everything gone including any metadata, systems tables, distributions db
and any other replication objects created during the initial configuration.

Thanks!
September 2nd, 2015 2:20pm

Delete a Publication

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 3:51pm

If you delete the publication using SSMS on the publisher, it will remove everything related to that publication.  It will prompt you to connect to the subscriber and remove the subscriptions. 

Only in extremely rare circimstances do you need to resort to another method.

As for the distribution database, that is created when you configure Replication the first time. If you want to remove all replication object, you can right click the "Replication" folder in SSMS and select "Disable Publications and Distributions...". 

https://msdn.microsoft.com/en-us/library/ms151759.aspx?f=255&MSPPError=-2147217396

September 2nd, 2015 3:58pm

Best thing to do is drop subscriptions, then drop the publication. If you try to drop the publication when there are subscriptions attached it will fail.

If you are using pull you will need to make a connection to the subscriber to drop the subscriptions completely.

If you are using push everything can be done through sp_dropsubscription, the followed by an sp_droppublication.

Once this is done you can do a sp_replicationdboption publicationdatabase, 'publish',false

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 4:13pm

If I follow your step 1 to connect to the publisher and delete the publication via SSMS as well as connecting to subscriber when prompted, at the end of this operation, will it also have removed all related SQL replication agent jobs?  Jobs that pertain to publication and subscription.

If I follow your step 2 and choose "Disable Publication and Distribution", will this drop the default distribution database as well as any replication agent jobs associated with it on this distributor instance.

Just wanted to make sure I know exactly what will happen (or will not) before I actually perform the removal.

Thanks

September 2nd, 2015 8:45pm

Yes, the current setup is a "push" transactional replication.  So if I decide to follow your steps using TSQL vs. SSMS, I will perform:

1. Connect to publisher

2. Run sp_dropsubscription

3. Run sp_droppublication

4.  Run sp_replicationdboption publicationdatabase, 'publish',false

and this is all done on the publisher instance, correct?  Is there anything else I need to do on the distributor and subscriber sides?  What about those replication agent jobs involved in this process?

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 8:55pm

Yes, that is correct. Just run those commands (specifiying the subscriber, subscriber_db, publisher, publication) in the publication database on the publisher.

Now if you drop the distributor you will no longer be able to replicate from that server - which may or may not be what you want to do. Dropping the subscriptions will drop the distribution agents, dropping the publication will drop the log reader and snapshot agents.

September 2nd, 2015 9:02pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics