July 10, 2012 at 8:40 am
I have inherited a database that uses merge replication for high availability between geographically separate sites (the database is also mirrored for intra-site failover). Currently the vast majority of articles have Automatic identity range management. This relies on the connectivity between the Publisher, the Subscriber and the Distributor (a separate SQL Server instance co-located with the Publisher). In the event of a prolonged failover to the Subscriber site we have encountered issues where the identity range at the subscriber is exhausted and/or we encounter primary key constraint violations (due to publisher and subscriber allocating the same identity value).
We believe the most effective way to deal with this is to move to manual identity range management, allocating sufficiently large ranges at the Publisher and Subscriber (an alternative of using odd and even numbers at publisher and subscriber is not feasible due to the impact on availability as we would have to drop and recreate the tables and reestablish replication).
As far as I can tell from there is no requirement to create a new snapshot when changing the identity range management mechanism. The question is how to go about changing the identity range management mechanism, together with the required ranges for all of the articles.
sp_changemergearticle allows the identityrangemanagementoption option to be changed but that's only half-way there.
Any advice and guidance would be welcomed.
July 10, 2012 at 9:25 am
I'm used to using GUIDs instead of identities for Merge Replication, specifically to avoid these kinds of problems.
Assuming you don't have the option of switching to those at this late date, do you have a proof-of-concept set of servers where you can test out the changes you're contemplating?
What you're looking at doing probably isn't something a ton of people have done, so direct answers to your question may or may not be available. So, if nobody happens by with a precise analog of experience that matches your plan, here are a few suggestions:
1. Set up some servers, possibly even just some local VMs, and copy your situation onto them. Merge Replication, data (or at least enough of it to do valid testing), and so on.
2. Try modifying the test servers from automatic to manual, and take the steps after that which either documentation or intuition lead you to. Documentation, as you mentioned, may only take you so far.
3. Break the connection between the test servers, and modify data in a way that parallels what will happen on your production servers.
4. Try to re-establish replication after doing so.
5. Ask around if you run into specific problems with it. You might get lucky and get answers. But don't count on that. You may have a rare enough situation that your own trial and error is what you'll have to rely on.
6. Document exactly what you had to go through, good and bad, as you go. Specifically document the success once you have it working, but also document the things (if any) that broke things. Do this for yourself and your teammates, but you might also want to turn it into an article that SQL Server Central can publish. Doing so will help others, look good on your resume, and so on.
If you get better, more specific answers, that's great. But with this rare a situation, I wouldn't count on getting them, or on the answers actually being relevant/correct if you do get them, unless the source on them is provably very, very reliable.
Sorry I can't help more, but I hope that helps at least a little.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply