SQL Server 2000/2005 Identifier Column + Replication

Question

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

First of all, in order to completely avoid any problems, we could simply not worry about using identifier columns as primary keys, instead they generated themselves and simply received these values ​​in both directions, believing that they are always unique at any time creation.

For the purposes of this question, I am talking about two or more replication methods for solving global access problems, and we have identifier columns.

Now we are setting up transactional replication, and both databases must be copied with each other.

As I understand it, you allocate a series of seed values ​​to each database server, and it will use them, you know that there is a unique reason why you gave ranges that do not overlap. Does this mean that during replication these values ​​are inserted into the start column?

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

Answer 1

There is a " NOT FOR REPLICATION " option that can be applied to identification columns (and triggers and other restrictions).

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

Several ways to plant seeds:

Or: set seed / increments with NOT FOR REPLICATION, like this

• 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 ServerID column to create composite keys, use NOT FOR REPLICATION for the ID column

It scales to, say, 256 servers for tinyint with 2 ^ 32 rows per server

In any case, it works ...