SQL Server – How to Disable Replication When Distribution DB is Lost

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 :-)

I'm not sure if you'll be able to reliable tell if the server has ever used replication or not. For example, if someone drops replication properly, there will be no trace left behind.