Search code examples
sql-serversql-server-2005replicationidentity-columnmerge-replication

Help determining proper Identity Range sizes


I have a Merge Replication with ~200 subscribers. I am trying to determine what would be appropriate Identity Range Sizes on some of the tables. You see I inherited a DB structure that was not designed for Replication therefore all table PK's are int IDENTITY's. Unfortunately some of these tables were built WITHOUT any tracking fields so I have no idea when they were inserted so I am having trouble formulating an estimate for the size of Subscriber Range needed.

Does anyone have any advice?


Solution

  • It may be too late in your case, but in situations like this I prefer to avoid worrying about ranges all together. Instead, I'll set the identity seed value uniquely on each database and increment by an appropriate power of 10.

    For example, if I had less than 10 subscribers, I'd use:

    Database 1: IDENTITY(1,10) - produces 1, 11, 21, ...
    Database 2: IDENTITY(2,10) - produces 2, 12, 22, ...
    Database 3: IDENTITY(3,10) - produces 3, 13, 23, ...
    ...