SQL Server 2012 – Safely Dropping a Table Under Replication

Merge replication is a bit tricky to remove as compared to Transactional replication.

Below are the steps that I follow successfully to remove obsolete/leftover merge replication bits and pieces :

1. Backup the merge replicated database(s) at the publisher and subscriber(s) before proceeding.
2. Script out merge replication from the publisher server and store the create/delete scripts safely in another location.

3. Obtain the list of published articles and its existing triggers for reference in later step by running the query below on the publisher and subscriber’s merge replicated database and storing the result somewhere for the time being (ie. In a text file)

   set nocount on 
   print 'get the list of published articles and its triggers' 
   select 
   p.name as publication_name, 
   o2.name as tbl_name, 
   o1.parent_obj , 
   o1.name as trigger_name, 
   left(o1.name, 3) as trigger_type, 
   o1.crdate as create_date 
   
   into #t 
   from sysobjects o1 
   join sysobjects o2 on o1.parent_obj = o2.id 
   join sysmergearticles a on o2.id = a.objid 
   join sysmergepublications p on a.pubid = p.pubid 
   where o1.xtype = 'TR' 
   and p.name = '' 
   order by p.name, o2.name, o1.name 
   
   select * from #t 
   
   print 'generate the drop statements for the replication triggers' 
   
   select 'drop trigger ['+trigger_name+']' 
   from #t 
   where trigger_type in ('ins','upd','del') 
   drop table #t 
   

4. Drop the merge replication by running the delete script generated in step 1 from the publisher.

5. On both the publisher and subscriber(s), clean out any triggers that are not removed by the replication delete script by running the drop trigger script generated in step 3.

6. On both the publisher and subscriber(s), check the sysmergepublications, sysmergesubscriptions, and sysmergearticles tables whether the entries for the merge replication still exists. If so, run the following script to remove the entries from sysmergearticles and sysmergepublications

   Note : You may also delete any entries in sysmergearticles that are no longer referenced by entries in sysmergepublictaions/sysmergesubscriptions, but please be very careful when doing this

      -- delete the obsolete entries from sysmergearticles on the publisher side 
   
      delete from sysmergearticles 
      where pubid in ( select p.pubid 
      from sysmergepublications p 
      where p.name = '' 
      ) 
      -- delete the obsolete entries from sysmergearticles on the subscriber side 
      delete from sysmergearticles 
      where pubid in ( select p.pubid 
      from sysmergesubscriptions p 
      where p.publication = '' 
      ) 
      -- delete the obsolete entries from sysmergepublications 
      delete from sysmergepublications 
      where name = '' 
       -- delete the obsolete entries from sysmergesubscriptions 
       delete from sysmergesubscriptions 
       where publication = ''
   

7. If there’s no other merge replication set up on this database you may also truncate the MSmerge* tables on the publisher/subscriber databases.

Following above steps will ensure that MERGE Replication is cleanly removed - no trace left behind !

Note: I have been using above method in my current environment without any issues. But please test this before hand ... as it goes without saying.. if something is screwed up, don't blame this site and me :-)

SQL Server Replication can in some situations hide errors from you. The behavior you are seeing could for example be caused by the snapshot agent not finishing, likely because of an incorrectly configured permission set.

Check out my Stairway to Replication here: http://www.sqlservercentral.com/stairway/72401/

Levels 1 - 5 give a solid foundation to the security requirements when setting up replication. Level 10 describes how to troubleshoot a replication setup. This includes information about where to find error messages and how to interpret them.