Why is a GUID is the best choice for a PK if you're going to be doing replication?

Question

I'm building an intranet web app with an Oracle back end. The Oracle DB will be replicated on another server for reasons that aren't important to this discussion. I'm reasonably certain we'll be using Oracle Basic Replication, not Advanced.

Can someone explain to me why most discussions around Primary Keys go something like:

• Use of auto-incremented ints for PKs is just fine 99% of the time
• EXCEPT if you're doing replication. Then use GUIDs for your PKs.

Nobody ever seems to explain why the exception for replication, and I don't understand why.

Many thanks.

Answer 1

I believe the GUID-for-replication argument only realy applies if both servers will be receiving new data. Then you don't want new rows to conflict, so use GUIDs as the PK - replication can go both ways.

We replicate autoincrement tables all the time, but it's a strict master/slave replication where data is only written to the Master.

Answer 2

One word: MERGE

The only way merge replication can work is if all of the "master" boxes can create a new key without it conflicting with the others.

Answer 3

If you consider what happens when two servers that are replicated with each other and have data being added to them between synchronization, the problem becomes apparent:

Time  Server  NewID
001   A       1
002   A       2
003 <sync of server A and B>
004   A       3
005   B       3
006 <sync of server A and B>

<---two different records have the same ID now, so there's a conflict!