SQL Server 2000/2005 identity column + replication

Question

I have looked at some resources already and just want to clarify and get an opinion.

First of all to totally avoid any problems we could just not bother using identity columns as primary keys instead have them generated ourselves and just have those values replicated both ways presuming they are always unique at any time of creation.

For the purposes of this question I am talking about 2 or more way replication to solve global access issues and we do have identity columns.

Now we are setting up transactional replication and both databases should replicate to each other.

As I understand it you allocate a range of seed values to each database server and it will use these, you know there unique cause you gave ranges that do not cross. So does this mean during replication these values are inserted into the seed column?

so if you allocate ranges 1-10 and 11-20 to 2 servers once each server has inserted 10 rows you will have seeds 1-20 in both databases?

Answer 1

There is the option "NOT FOR REPLICATION" that can be applied to identity columns (and triggers and other constraints).

In your example, server1 would seed 1-10 but simply accept replicated 11-20.

A couple of ways to setting your seeds:

Either: set your seed/increments with NOT FOR REPLICATION Seed 1, increment 2 Seed 2, increment 2 Seed -1, increment -2 Seed -2, increment -2 Seed 1000000001, increment 2 Seed 1000000002, increment 2 Seed -1000000002, increment -2 Seed -1000000001, increment -2

This gives you 500,000,000 per server for 8 servers

Or: Add a second column called ServerID to give composite keys, use NOT FOR REPLICATION fot the ID column This scales up to,say, 256 servers for tinyint with 2^32 rows per server

Either way works...