Managing identity/primary key across servers

Question

I'm in the middle of designing a new database that will need to support replication and I'm stuck on the decision what to choose as my primary key.

In our current database for primary key we use two int columns, first column is identity and the other is used to describe on which server the row is inserted. Now I want to avoid using two columns for primary key, and just use one column instead. So far I have two way of doing this:

1. Use GUID for my primary key

   This one will ensure that there is always a unique key across any number of servers. What I don't like with this one, is that the GUID is 16bytes in size, and when used for foreign key across many tables it will waste space. Also it is harder to use it when writing queries, and it will slower to query.

2. Use the int or bigint, and manually specify the seed and increment value for every table on each server. For example if there is two servers, the X table on the first server will start from number 1, and on second server it will start from number 2, each will increment by 2. So there would be (1,3,5,...) on first, and (2,4,6,...) on second server. The good thing with this design is that it is easier to use when writing queries, it's fast, and it use less space for foreign keys. Bad thing is that we never know how many servers will be running so it's harder to tell what will be the increment value. Also it's harder to manage the schema change on server.

What is the best practice for managing multiple servers, and what's the best way, if any, to do in this kind if situations?

Answer 1

Your question is a good one, and one that is asked often.

From a maintenance perspective, I would absolutely go with GUIDS. They are there for a reason. Somewhere along the line you might run into complex operations moving and re-replicating your data, and then the other options can make it a little more complex than it needs to be.

There is a very nice short read about the various options here:

http://msdn.microsoft.com/en-us/library/bb726011.aspx

As for the Replication part - if done properly, there are no real headaches with replication.