September 10, 2001 at 12:01 am
Hi,
I require some assistance on structuring my DB tables for replication.
We (hypothetically) have a client with 2 branches, one each side of the
country, linked over a WAN and replicating at an unspecified period (1 day
OR 1 hours OR ..and so on ).
Branch A produces a client record with the primary key HUD001 and branch B
(on the other side of
the country) also happens produces a new client record with the primary key
value of HUD001.
Due to the time issue of replication over large distances
(3000 km) and slow WAN's, record verification is unable to take place within
the required time to detect whether or not this key value already exists.
Basically branches could spend the entire day duplicating these keys until
the necessary checking takes place. How could we get around this problem?
We could possibly have a UNIQUE KEY which is (CLIENT CODE + BRANCH CODE),
and secondary keys which are: CLIENT CODE, and another which is BRANCH CODE.
Any Ideas would be appreciated?
September 10, 2001 at 4:42 am
Merge replication basically will take care of this for you, might want to explore how it uses identity cols to see if it will work for you. Having a key based on the branch is workable as long as you won't be using that implied meaning for anything, that is - client moves from office SSS to TTT, hopefully you would not have to change all the primary keys to match.
My personal favorite for these types of scenarios (and pretty much all others) is to use a uniqueidentifier instead of identity. Guaranteed unique across all locations. Down side is its a bigger value than int, so you're going to use a few more bytes per record. Up side is that your developers can generate the ID on the client, not have to insert and then return scope_identity().
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply