Interesting ID issues...

Hi all,
I have a system in place currently that both a local and a live database, currently the sales database is kept in line with a home grown "syncronise" script that controls keeping the two tables (live and local) in sync with the use of Local and Remote ID's

For example;
Table "Sales"
ID - Int
RemoteID - Int
Various Data fields

The 'Live' System writes new records using the RemoteID field, whilst the local inserts new records using the ID field. The Sync script copies the data up/down and assigns the corresponding RemoteID/ID's to the records as they are copied accordingly.

Its been decided that this process must change, in favor of Two Way Replication. I have been tasked with finding the solution to the safe handling of ID's as theres a danger that two people can be writing a record (one live, one local) and possibly create the same ID's - i know that replication has its own ID's and could probably manage this well on its own, but these 'Internal' ID's have to be unique due to business rules.

So i have been considering two possibilities to address this problem,
1. Staggering the ID's - for instance Live ID's begin at 1,000,001 and local ID's begin at 1.
2. Having a controlling ID table which provides the next ID

Both options have their advantages and disadvantages, with option 1 we will run into a problem later when the local ID's reach the 1 million mark (a few years away, i admit) and option 2 will need careful locking/transactional code to ensure the same ID isnt given twice.

Does anyone have experience with this kind of issue? or could suggest an alternative approach to this problem?

Thanks in advance!

Replication ( of any type) by itself does not genrerate any Id's for you. Replication is just a process of copying the data from A to B and/or B to A. You can however, set up 2 range's of Id's for the 2 columns but like you mentioned you could run into issues in future and it could be sooner than later depending on how your data changes. And option 2 does create locking issues.

Now my question is since the 2 columns are different what is the need to keep them in the same table? Are local Id's from your local testing or do they hold any significance? I am trying to understand your design. Looks like you have 2 tables with same data (in 2 db's over 2 servers)?

There is a Peer-Peer Replication option available in SQL 2005 where you can insert data in either of the peer's and replication will take care of sync'ing them.

|||

I work for a Dotcom and we have two databases, one on our 'hosted' live servers and one in the local office.

Historically we have used two seperate ID fields, one live (RemoteID) and one local (ID), to allow staff to create sales records locally and customers to create sales records on the live systems. We have a script that then pulls down/copies up the new records and manages the ID's. Due to our sales increasing over the last 12 months this script can no longer handle the work in a timely fashion so another solution is required.

The ID numbers need to be the same, locally and live as this is part of the reference number given to the customer, its been communicated to me that the company wants to keep only a single ID record for sales now and not have to manage multiple ID fields, the company also wants to use replication to manage the data movement. So i need to find a solution for this.

The database can get quite busy under peak load, so i need a solution thats fast, efficient and capable of handling a lot of new records whilst still allowing staff to create local sales and customers to create live sales simultaneously without getting the ID's mixed up.

Using a control table for ID's was a thought, but my concern is speed and locking under heavy load