Unanswered: Primary Key Violation in Merge Replication
Dear all, Please guide me if you have suggestion/solution to my situation.
I am running a product management system at my office. now i decide to run this system at other office too. The system is running with a Microsoft SQL server 2000. Thus I decide to set up a merge replication to do the data synchronization due to the network connection is scheduled to online once a day. Now the problem is primary key violation. while synchronizing between two Database servers. the primary key violation occurs frequently. Because our program will retrieve the maximum key then plus one as the new key while generating a new product. Assume two sites create few products. thus some keys will be the same. so due to publisher-win resolver. one site records will lose.
Is there any way or tools/resolvers to solve the problem automatically? I mean, when the conflict detected. one record will remain no change while the other one change its primary key (maximum plus 1), then insert to the table to solve the conflict.
I am no expert in this area but I see nobody more knowledgeable has posted so this might be of some use.....
it occurs to me that you may need to use Ranged Identities. I use SQLServer7 so I have to implement this idea manually but I believe in 2000 SQLServer manages it for you as part of the subscription process.
Ok, what I do is keep a table that tracks Range Identities based on subscribers. So, for example, (I use pocketpcs) device 0x0003434343 is given a range of, say, 10001 to 19000 for creating new records on a specific table.
When this user subscribes to my master database publication and downloads the data I look up the RangedIdentity table and do an "ALTER TABLE tablename ALTER COLUMN identitycolumn IDENTITY(newseedvalue)".
This way whatever records device 0x00034... creates will have identity values unique to it.
It seems to work out fine but my only worry is that when the ranges are exhausted they have to be re-assigned.
The problem with ranges is indeed that at some time youre range values will at some time get exhausted and you have to create a new range etcetera . It's better to use a composite key, for example the ID you use at this time and an additional column (eg OfficeID and use value 1 for the first office and value 2 for the second office). The combination of the two columns is then unique.
Thx for your kindly reply, I know the Range Identity is the best way to solve my problem, but unfortunately, our database stucture is not compatiable with Range Identity, that means, it uses char '0001' as primary key instead of using int. I cant change the type from char to int in order to use Identity method (the application will not work if using int as primary key type). so what can i do now?
you don't have to change anything at all. If your primary key was working fine until now, just leave it. What I meant was that you have to add another column (eg OfficeID), for example of type char(1). If you insert a record from one site, you additionally set the value of OfficeID to '0'. If enter a record from the other office, set the value of OfficeID to '1' etc. Offcourse you have to change the way that records are inserted because you have an extra parameter.