Identity Range Management

Hello all,

I am just beginning to get into replication and have a couple questions for the experts.

When I set up replication (transactional with queued updates), the default identity range was set to 10,000 for the publisher and 1,000 for the subscriber.I understand that if I try to insert 1,100 records at the subscriber, I will get an error saying that the identity range has been exhausted and I need to run sp_adjustpublisheridentityrange on the publisher and the Distribution Agent for the subscriber.

My questions are:

Why wouldn’t someone increase the identity range to a very large number, just in case something happens and the publisher/subscriber link is broken for an extended period of time?What are the ramifications to a larger identity range?

Since identity range management is done by the distributor, what happens if the distributor goes down?Do the publisher and subscribers receive errors when they fill their current ranges?

Thanks,

Jarret

1. If you set it too large, it's possible to exhaust all possible values. i.e. if you have smallint column with publisher having range of 10,000 and two subscribers with range of say 11,000, and one subscriber hits the threshold, that subscriber cannot get new range since you hit datatype limit even though the 2nd subscriber may not have used any of of their range of identities.

2. If distributor goes down, subscriber inserts will run out of identity values and will most likely fail.

You can read more about this in BOL topic "Replicating Identity Columns", http://msdn2.microsoft.com/en-us/library/ms152543.aspx.