1

What is the proper way to completely clean Merge Replication setup ?

I have tried to delete the subscriptions, then delete the publication then "Disable Publishing & Distribution" & delete the distribution database then started setting up the replication again.

unfortunately, after that I discovered that dbo.msmergesubscriptions table still have old records about old subscriptions I used to have before 2 years !!

Is there any official documentation about how to properly & totally clean Merge Replication setup?

Improve this question

2 Answers 2

Reset to default
1

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

Improve this answer
6
  • 1
    I was trying your script in step 3, but it seems that trigger names in my case starts with MSmerge_ instead of ('ins' or 'upd' or 'del' ) like you presume, which is causing the list of drop trigger script to be empty, Is it expected to be different between systems ? Commented Jun 19, 2013 at 6:22
  • 1
    Also you are using a filter in the query ( and p.name = '') which is trying to select a publication which has no name. Commented Jun 19, 2013 at 6:22
  • 2
    @AdelKhayata you have to put in the publication Name. Commented Jun 19, 2013 at 14:07
  • This is not how you drop a Merge topology. Commented Jun 24, 2013 at 15:26
  • @BrandonWilliams The question is not how to drop merge replicaiton, it is how to clean up Merge replication after it is not dropped correctly. Commented Jun 24, 2013 at 15:39
2

Somehow you are ending up with orphaned replication metadata after deleting the subscriptions and/or the publication. This can happen when using the GUI to drop subscriptions and publication. Instead, try using T-SQL replication stored procedures to delete the subscriptions and publication.

How to: Delete a Push Subscription (Replication Transact-SQL Programming)

How to: Delete a Pull Subscription (Replication Transact-SQL Programming)

How to: Delete a Publication (Replication Transact-SQL Programming)

After deleting the subscriptions and publication, verify the replication metadata was removed. If not, use sp_removedbreplication to remove all replication objects from the databases.

Improve this answer
3
  • 1
    Yes, You are right, I used GUI to drop subscriptions and publication and also disable publishing & distribution, but why using The GUI is different than using T-SQL commands ? isn't it at the end the same commands executed in the background ? Commented Jun 19, 2013 at 6:00
  • 1
    Also the other answer in step 5 is referring to run a script to delete left behind triggers even after dropping everything using T-SQL commands!! so this means even if I use T-SQL commands, I still might have some replication meta data left in the setup!! Is this really possible ? Is there any Official Microsoft document to clarify how this should be done ? Commented Jun 19, 2013 at 6:06
  • 2
    I have provided the documentation on how to remove subscriptions and publications.Yes, you are correct, the GUI uses the same T-SQL commands to delete subscriptions and publications, however, notice that when removing a pull subscription that you must execute a command at the subscriber and then execute a command at the publisher. When using the GUI, depending on where you initiate the delete, the publisher or subscriber may not be available and a command may never be executed, thus leaving orphaned replication metadata. It is best to use T-SQL replication stored procs to remove replication. Commented Jun 19, 2013 at 15:08

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.