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