PK Generation Or Data Synchronization

I would appreciate some advice on generating a PK and/or synchronizing data
between two (maybe three) locations.
Simplified scenario.
Using SQL Server 2005 (could got to 2008 if needed).
Client has two locations (central and remote).
Item record would containseven fields - six int fields (id's from component
tables) and a seventh field that would be the PK.
Most of the time the PK would be generated at central, however, should the
connection be lost, remote must be able to generate a PK also and then when
connection is restored, any new PK generated by remote must be added to
central.
The PK needs to be the same for the same combination of the six fields, no
matter which server generated it, in case both servers happened to create a
record with the same six fields. Therefore using a GUID would not work.
Since the PK will be used by another system (Great Plains), it's length is
limited to 30 characters.
AG
Email: discussATadhdataDOTcom
Well you could simply use a coded IDENTITY or similar. By coded, I mean
generating the new ID in a central table, and on each server the identity
seed is different, e.g. on central it starts at 1, on remote it starts at
something like 2000000 or some reasonably large number that you won't get to
in central. INTs are big and BIGINTs are bigger; for the latter you could
spread the numbers out over a million servers and still have plenty of room
to grow for just about any business model. In addition to ensuring
uniqueness, this way when you replicate data back to central, you know which
rows were created on the remote system (which a GUID wouldn't tell you).
However, now that I mention GUID, I am thinking about one of your final
comments. If the systems cannot talk to each other, and regardless of what
methodology you use for your surrogate key, what are you going to do to
consolidate when the remote server creates a new row where the six columns
are the same as a row that already exists in the central database? (Even if
you are using replication, let's say that row was created just before the
server went down, and the row was never copied to remote.) You can't
prevent the new row from being inserted, because you don't know such a row
already exists!
"AG" <NOSPAMa-giam@.newsgroups.nospam> wrote in message
news:uxVTMTHkIHA.5724@.TK2MSFTNGP03.phx.gbl...
>I would appreciate some advice on generating a PK and/or synchronizing data
>between two (maybe three) locations.
> Simplified scenario.
> Using SQL Server 2005 (could got to 2008 if needed).
> Client has two locations (central and remote).
> Item record would containseven fields - six int fields (id's from
> component tables) and a seventh field that would be the PK.
> Most of the time the PK would be generated at central, however, should the
> connection be lost, remote must be able to generate a PK also and then
> when connection is restored, any new PK generated by remote must be added
> to central.
> The PK needs to be the same for the same combination of the six fields, no
> matter which server generated it, in case both servers happened to create
> a record with the same six fields. Therefore using a GUID would not work.
> Since the PK will be used by another system (Great Plains), it's length is
> limited to 30 characters.
> --
> AG
> Email: discussATadhdataDOTcom
>
|||Thanks for the quick reply Aaron.
I had thought about a GUID also. As well as generating the PK in a trigger
using odd numbers for one server and even for another, but your idea of a
different identity seed is simpler and more scalable.
However, as you stated also, the problem is that there could be the same six
columns with different PK's generated by both servers when connection is
down.
I was hoping for some algorathym that would combine the six column values
and always generate the same value from the same six values. Something like
a hash code. However, I am limited to 30 characters and I do believe a hash
code is 64.
Another idea is to write routines to do the 'replication', instead of using
SQL replication.
Then add all new rows in 'remote' via a stored procedure. If the sp can't
connect to 'central' it would generate it's own PK and also record that in
another table. Then when connectivity is restored, my replication routine
would check for duplicate rows and if necessary, update the PK in 'remote'
with the PK from 'central'.
Aside from the complexity, the problem with that is I don't know if Great
Plains can use cascade update to update its' detail tables that would be
referencing my PK as an FK.
AG
Email: discussATadhdataDOTcom
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:2BD5421B-65B2-4B86-8080-015FC8A50740@.microsoft.com...
> Well you could simply use a coded IDENTITY or similar. By coded, I mean
> generating the new ID in a central table, and on each server the identity
> seed is different, e.g. on central it starts at 1, on remote it starts at
> something like 2000000 or some reasonably large number that you won't get
> to in central. INTs are big and BIGINTs are bigger; for the latter you
> could spread the numbers out over a million servers and still have plenty
> of room to grow for just about any business model. In addition to
> ensuring uniqueness, this way when you replicate data back to central, you
> know which rows were created on the remote system (which a GUID wouldn't
> tell you).
> However, now that I mention GUID, I am thinking about one of your final
> comments. If the systems cannot talk to each other, and regardless of
> what methodology you use for your surrogate key, what are you going to do
> to consolidate when the remote server creates a new row where the six
> columns are the same as a row that already exists in the central database?
> (Even if you are using replication, let's say that row was created just
> before the server went down, and the row was never copied to remote.) You
> can't prevent the new row from being inserted, because you don't know such
> a row already exists!
>
>
> "AG" <NOSPAMa-giam@.newsgroups.nospam> wrote in message
> news:uxVTMTHkIHA.5724@.TK2MSFTNGP03.phx.gbl...
>