3

When I began to work on replication a few years ago, I was convinced that Identity Range Management was only made available in recent SQL Servers for legacy purposes as, prior to SQL Server 2000, the GUID type field did not exist and most primary keys were built on autoincrement fields.

I already saw some of these databases 'at work', and I can say that managing these Identity Ranges in a complex replication topology can be a real PITA, with guaranteed headaches and all-night-long debugging sessions.

I have just read here that Identity Range Management was still proposed as default with SQL Server 2008, followed by this other question on Best Practises and Identity Range Management where #mwolfe02 reaches the conclusion that, in some situations, users cannot insert data in the database unless they are granted db owner's right!

Disturbing, isn't it. So, with the exception of legacy databases, why should someone use Identity Range Management with SQL Servers? In a more generic way, why would someone promote any predetermined\autoincrement identification method in a database?

Community
  • 1
  • 1
Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72
  • I'm not sure I agree with the conclusion reached by your second link - it's basically saying that you need to be a db_owner, *if whoever set up replication is letting the ranges get exhausted*, which is not an issue if the merge agent runs frequently enough/the ranges being handed out are of an appropriate size. – Damien_The_Unbeliever Jun 02 '11 at 14:47

1 Answers1

1

I guess one of the reasons to continue to use identity values is because of the disadvantages of using GUIDs as primary keys in a database, which is documented here GUIDs as PRIMARY KEYs.

I personally have never worked with a complex replication topology and have always used integers when setting up replication. I manage the identity ranges by adjusting the increment value of the identity function, so if I have two servers in replication I would have odd numbers on one and even numbers on the other and increment by 2. The same setup applies with multiple servers, the increment set equal to the number of servers.

I would also start my identity range at the lowest number for an integer (-2,147,483,648 to 2,147,483,647) as there is a lot of values that don't get used as integer identity columns are more often than not started at one. Even if you have 20 servers involved that still gives you quite a range of values to use.

SPE109
  • 2,911
  • 1
  • 19
  • 16
  • Doesn't that manual identity range management that you're proposing get more complicated if a) the number of servers in the topology varies over time, or b) There's a large disparity between the number of inserts performed by each peer? – Damien_The_Unbeliever Jun 02 '11 at 14:43
  • Yes, you are right it would get quite complicated if the number of servers varied greatly over time. But I suppose if you had 10 servers in the toplogy you could plan for 20 and still have a good number of rows available for each. As for disparity of inserts, I guess that would only be a problem if you thought you would run out of identities at your busy server. – SPE109 Jun 02 '11 at 15:06
  • #SPE, your proposal is valid within certain limits, as outlined by #Damien. Another situation where your proposal is not valid is when subscribers are also acting as publishers for other servers in a 'cascade-replication' topology. And these limits are the disturbing thing about Identity Range Management: how can one accept to build a replication architecture knowing that, one of these days, he might reach such a limit and face a disaster. Anyway thanks for your input on this. – Philippe Grondier Jun 02 '11 at 17:30
  • But I'd like you to comment a little bit more on "the disadvantages of using GUIDs as primary keys in a database". Because if there might be some disadvantages in using them, there are so many advantages! – Philippe Grondier Jun 02 '11 at 17:35
  • It's an interesting discussion, with both methods having things for and against them. I guess it is the skill of the DBA making educated decisions on which method proves best. – SPE109 Jun 03 '11 at 07:48
  • I was wondering if using the automatic identity management, this requirement the user is a dbo, could this be bypassed by activly monitoring the range and reseeding when necessary? – SPE109 Jun 03 '11 at 07:49