1

I am trying to design a database structure. It will be installed into a few instances of sql servers. I need to merge these databases periodically.

Problem is in server_A (PK)id=222 user=Tom, but in server_B (PK)id=222 user=Peter.

So, in server_A for Tom, (FK)userid=222, Address=1 abc street..., but same (FK)userid=222 in server_B address table has a different meaning.

All I can think of is this cheap approach. If I have 3 servers, I seed the id in 1st server to start at 1000000001, 2nd server at 2000000001, 3rd at 3000000001 .... so they will not repeat themselves, that might work for a small time project? And I don't need to use a Guid/uniqueidentifier to further complicate the problem?

What is a common/correct approach to this situation?

Tom
  • 15,781
  • 14
  • 69
  • 111
  • Which database system? Most vendors offer some sort of replication between servers, so you don't have to do it yourself. – Jonas Lincoln May 25 '12 at 12:27

3 Answers3

1

You can add a server_id as a foreign key that's now part of a compound primary key together with the original primary key. Each server has its own id.

Jordão
  • 55,340
  • 13
  • 112
  • 144
0

Use the merge replication feature of SQL Server, it will handle this for you.

Jonas Lincoln
  • 9,567
  • 9
  • 35
  • 49
  • I have not used this feature before. Does it automatically handles the related FKs for you when it merge the repeated PKs? Seems not according to @Branko's post? – Tom May 26 '12 at 00:41
0

Use GUIDs instead of ints as your primary keys. They are unique even when generated on different systems.

I'm no expert on SQL Server Merge Replication, but according to this post, it cannot resolve conflicting PKs automatically, so it's better to avoid conflicting PKs in the first place.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • I wanted to avoid guid/uniqueidentifier because I heard it makes the db much slower when you have a rather large table. No first hand experience here, but if I don't choose it carefully at the start I may run into another problem dealing with performance later on. – Tom May 26 '12 at 00:37
  • @Tom, the overhead might not be as large as you think (esp with newsequentialid())... http://www.codinghorror.com/blog/2007/03/primary-keys-ids-versus-guids.html – clyc May 26 '12 at 01:44