Friday, March 9, 2012

Merge replication failing after recreating the publication

I am having a problem with merge replication after recreating a publication. It is a simple two-way replication between two servers allowing applications to update data at both ends, i.e. 1 publisher, 1 subscriber for all tables except some junk ones. The problem started after I did the following:

1) I dropped and recreated the publication to allow for some changes to the database schema. These caused problems so I dropped the subscription and publication and restored the databases at each end from backups taken before we started.

2) When I created the publication again, it wouldn't let me use the same name as it thought the publication already existed. It let me use a different name instead.

3) Most things work OK but one of the tables is not replicating inserts from the subscriber to the publisher. There are no errors and no conflicts, other similar tables replicate OK. Inserts go across the other way OK.

4) I am getting replication conflicts on another table that says 'Unable to synchronize the row because the row was updated by a different process outside of replication'

I believe the problem is to do with the original publication details still being in the restored databases, so am looking to drop the publication & subscription, remove the remnants of the old publication, fix the data and recreate pub & sub. What do I need to do to get rid of the old replication data in the database?

Any help much appreciated.

After dropping the publication and subscription, you can clean up the published and subscriber database of all replication objects by calling sp_removedbreplication.|||I could try this, but I did delete the subscription and publication AND removed the database as a publisher in the server replication properties using the GUI - which presumably does the same thing?|||

Another possibility is if you take a database backup of a published database, and restore it to the same server. When you do this, the restore actually preserves the replication metadata and objects in the database, which could be another reason for the errors. If you were to restore the database to a different machine, then restore would drop the database objects. You can test this out by restoring your published database, and then looking to see if there are any merge system tables laying around, like sysmergepublications, sysmergearticles, merge triggers, etc. If so, then sp_removedbreplication should do the trick.

If this is not the case, then you may want to try dropping the distributor as well then, there may be orphaned rows in some of the metadata tables stored in the distribution database.

No comments:

Post a Comment