How do I replicate identity columns in SQL Server

Replication of Identity columns in SQL Server is a tricky business
you will need to bear in mind the following:-

  • Make sure you set the NOT_FOR_REPLICATION flag on the identity
  • You need to workout and assign separate ranges for each server i.e. server 1 may start at 1, server 2 may start at 20000. replicate first, and then go in and update the identity range using either VS or DBCC CHECKIDENT

There is more information at

http://msdn.microsoft.com/en-us/library/ms152543(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms152529(SQL.90).aspx
http://www.mssqltips.com/tip.asp?tip=1274
http://blogs.conchango.com/stevewright/archive/2007/04/16/SQL-Server-2005-Merge-Replication-Subscriber-unexpectedly-deleting-during-data-Synchronisation.--.aspx