How can I create a locationID type of column to denote what server created a row in SQL Server Merge Replication?

Question

I want to setup merge replication between two SQL Server 2008 Servers. For the Primary Key’s, I’d like to use a composite key: an identity column plus a column that will denote which server the row was created on (locationID column).

Any ideas on how to do this? I’m not sure if you could use a trigger or a function to insert a default value every time a row is entered, but I’ve read this method is possible – just can’t find detailed steps on how to exactly do it. Thanks for any help or references you can provide!

Answer 1

In a peer-to-peer transactional replication scenario, I've used a technique of assigning each server a number, which will serve as the ones digit of the identity seed. Then, increment each identity by ten instead of the default 1. So, identities assigned by server 1 are 1, 11, 21, 31, ... and identites assigned by server 2 are 2, 12, 22, 32, ...