October 3, 2007 at 1:48 pm
I am trying to get my head around the not for replication on an identity column using merge repl. If the identity seed is 1, increment 1 on both tables then adding data to tableA (publisher) will copy across to TableB (sub) with the same identity id. If you manually enter some data on tableB at the same time as an application is entering data onto TableA then both will have the same identities in, and therefore i believe will cause conflicts! How is it possible to manually add data into the subscriber so that it wont cause conflicts? On a production DB (sub) we also had a problem where the table identity had to be reseeded, but running an insert now just means we will get conflicts 🙁
I tried this at home on a test database with empty tables and i entered 4 lines on TableA which were given identities 1,2,3 and 4. these then replicated across. I then ran 2 inserts at the subscriber and these were given identities of 24005 and 24006, where did sql get these numbers from??? and what will happen once TableA gets up to 24005 rows?
Im really confused on how sql gets round this identity problem and cant seem to find any documentation on it.
October 5, 2007 at 12:39 pm
John,
Merge gives you "automatic" identity range handling. you do want to replicate the identity values and you will have to either partition the values or use the "automatic" range. In addition because you want the updates on the subscriber to be propagated to the publisher you need to make sure that your subscriber is a "global" subscriber.
Hope it helps.
* Noel
October 16, 2007 at 5:39 am
Yes just to confirm
Merge replication manages it's own identities, and very well. It uses a stepped identity approach. Table A on Server A will have identity range of 1-1000, on Table A on Server B the range will be 1001-2000, so the inserts that take place both ends never conflict. When the end of the range is approaching, sql simply reseeds to the next range which for Table A on Server A is 2001-3000.
I would always let merge replication manage the identities unless it is really important not too.
Regards
Graeme
October 16, 2007 at 7:05 am
Graeme100 (10/16/2007)
Yes just to confirmMerge replication manages it's own identities, and very well. It uses a stepped identity approach. Table A on Server A will have identity range of 1-1000, on Table A on Server B the range will be 1001-2000, so the inserts that take place both ends never conflict. When the end of the range is approaching, sql simply reseeds to the next range which for Table A on Server A is 2001-3000.
I would always let merge replication manage the identities unless it is really important not too.
Regards
Graeme
Interesting, was this the same in sql 2000 as well as 2005? The only reason i ask is because currently at work the identity ranges are exactly the same for both the publisher and subscriber, and hence conflicts occur (not a lot because the subscriber hardly ever inserts data and it shouldn't be set up using merge rep - but it is!). I am assuming that when it was setup the publication database was just backed up and restored, and hence the identity ranges were the same? is that correct?
What step is it in the replication setup that changes the identity ranges for the subscriber server?
October 16, 2007 at 9:04 am
In 2K
The ranges are added automatically once you run the snapshot and merge agent. You can see these in the article properties. These can of course be changed if the ranges are unreasonable.
In 2005
The process is I think much the same but a lot better handled. I'm about to get my hands dirty with 2005 replication so i'll know a lot more soon.
Did you apply a new snapshot?
Form my experience one must be a little careful when re-applying merge as you should make sure the old identity range constraints are removed other wise this can cause a few issues.
Regards
Graeme
October 16, 2007 at 12:12 pm
Since i started there (only 2 months ago) there have been no snapshots applied. They were getting conflicts for as long as they could remember, and didn't know how to sort them.
I will take a look at the article settings and see what is setup, but im guessing both sides will have the same ranges.
October 17, 2007 at 2:55 am
If your databases are not too big, a possible solution is to reapply the snaphot
OR...during a weekend
Synchronise the databases and stop any further changes
Drop the replication
Remove the Identity RAnge constraints
Re-apply a no-synch merge replication.
Run the Snapshop agent
Run the Merge agent
Check the Identity ranges on Publisher/Subscriber
All should be ok
Regards
Graeme
October 24, 2007 at 11:20 am
Hello,
Great topic, I’m glad I came across it. We’ve been running merge replication on SQL 7.0, 2000 and now 2005, Publisher with one updating Subscriber. They way we've always handled the identity values in an effort to avoid conflicts is to have the publisher use only Even numbers increasing the increment by 2 and doing the opposite on the Subscriber.
So on PubServer.Table_A the values started at 2,2 and on SubServer.Table_A they started at 3,2. All and all this has been working out well for us.
Occasionally we run into issues where the table’s numbers get out of whack, even though they are supposed to be load balanced. So PubServer.Table_A may have a value of 50 but SubServer.Table_A has a value of 2051. In these instances I use DBCC Checkident to reset the identity seeds, making sure I use the set it to an even number on the Publisher and an odd number on the Subscriber.
After reading this post I’m thinking that maybe I should use Merges’ "automatic" identity range handling. If I switched to this would it just be a point of setting the @identity_support parameter to 1 when creating the publication or would I still need to set up ranges on both sides. And where I have the table set to increment by 2 would this now cause issues using the automatic handling? I’m confused, any thoughts?
Barbara
October 25, 2007 at 12:05 am
Hi, I use in place of identity column rather column uniqueidentifier, it's much better resolution, because is unique within all database.
October 25, 2007 at 12:57 am
Hi,
As far as Unique identifiers is concerned that's fine unless you want to use the identity i.e. as a companyID or a Userid in which case the INT is the simpler option...I think anyway. Merge uses guids anyway to manage records.
Barbara, everything in your merge should be fine.
If you followed my list then once you re-apply the merge, the identities each end will be managed by the merge. The fact you have stepped identities means you will only be using every other identity in each range.
I.e.
Server A ID Range 1000-2000 with stepped ID of 2 you will get 1002,1004,1006 etc
Server B ID Range 2000-3000 with stepped ID of 2 you will get 2002,2004,2006 etc
The iDs will never conflict merge will just have to step more often
Regards
G
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply