Simon Harriyott

Syncronising IDs across databases

Let us suppose you have a customer, who has a website, which has a database. Let us also suppose that you have a local version of this website and database that you use for development and support. Let us also suppose that you periodically update your database with data from the live site.

Let us suppose (alright, that's the last time I'll say it) that since the last update, you have added rows to a table with an auto-incrementing ID column on your database, and the customer has done the same with her database. When you next come to synchronise the databases, you have two different rows with ID 3948. What to do?

In SQL Server, the identity seed and identity increment both default to 1. These can be changed to get around this problem. Changing them both to 10 on the live database will mean that all rows will end in zero (10, 20, 30 ...). On the development database, changing the seed to 5 and the increment to 10 will mean that all new rows will end in 5 (15, 25, 35 ...).

When merging the two databases, the rows should slot nicely between each other. A nice side effect is that you can tell which site a row was added from by the last number in the ID.
17 January 2008