Resolving all losing conflicts-Merge Replication in SQL Server 2005 and 2000
-
The application I support uses SQL Server 2005 as the publisher and SQL Server 2000 at the subscriber. One of our clients went nuts trying to prune his database and deleted a year and a half of sales data he didn't mean to. I did have an copy of the db as restoring from backup was not an option (he didn't discover the mistake for a week+). I manually imported the data at the subscriber end (which I've done before with no issue) as his database was pushing 20 gigs and publisher server space at the time was a premium. Long story short when it replicated it locked up all that data in 40k worth of conflicts with the subscriber as the loser. To avoid a costly and time consuming data import at the publisher, I want to use one of the customer resolvers, namely the SQL Server Subscriber always wins. I only need to do it on 2 articles. Can someone give me brief instructions as well as any pitfalls before I take the plunge? My concerns are: will I have to reinitialize the subscriber or redo my publication (reinit would be painful and due to the number of locations in the clients WAN, recreating the publisher is not an option).
Software/Hardware used:
SalonTouch, Windows XP, SQL Server 2000 (sp4) and 2005 (sp3), 64 bit Server 2003 R2 SP2
-
So, ultimately you are ok with an out of sync condition existing for these tables? If so, what is the possibility of dropping the subscription, adding the rows on the subscriber, then re-adding the subscription, without initialization of course.
I have not personally used that conflict resolution set, nor have I used the custom resolver at all so I can't speak to the impact of that. I was able to change a test publication / article to the alternate resolver method and it took it without having to drop the article so, hopefully you will find the same.
I'll be interested to hear how you proceed with this.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Your reply is intriguing. I wasn't aware that it was possible to drop a sub and re-add it without reinitialization. We as SOP set the metadata retention for 14 days, so from time to time we get a client that has not synchronized within the retention period and the sub has expired or in some cases we get a sub in good standing that throws the following error: "The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload)." This apparently is a bug (open ticket still with Microsoft and no response). The solution has been manual data imports via SQL Statements so that the client looses no valuable sales data. If you know of a better way that keeps the client side database intact, requires no reinitialization, reestablishes the subscription after its been dropped, and allows merge synchronization to recommence, expired or otherwise, then you just made a new best friend
-
IF you have a window that you could drop the subscriptions, remove the article from the publication, save the publication, re-add the article, save publication and then create the subscription without initializing I think it will take care of that. I think that will work but it seems messy though... That could all be scripted though which might make it more feasible.
I'll chew on it a bit and get back to you if I can come up with something more solid.
I would also be curious if the alternate conflict resolution model works for you if you decide to do that.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply