Merge Changes in SQL Server 2005: (3) - Identity Range Management

Introduction

Yes - I know the title of this article looks a little odd at this stage! We're currently into November 2008, SQL Server 2005 has been out for 3 years and it is now no longer the latest version on the market, so isn't it all a bit old hat to be listing new bits to SQL Server 2005? Well I suppose it is, but recently I've come to understand some aspects of the replication changes a little better – both from the newsgroups and from experience, and I'd like to collate this info into an article that I couldn't find out there on the web. Specifically, this is about the many alterations made to the management of identity ranges, one of which I've only just recently stumbled upon.

Identity Changes

Firstly, let's remember that in SQL Server 2000 if we had a table in which there was an identity column, and this was added to a merge publication, the default was to use manual identity range management. Of course automatic range management was possible, but it was disabled by default. In those cases where automatic was used, there was the option to specify the publisher range size, the subscriber range size and a threshold value. The threshold is the % of identity values to be used up before a new range is allocated (during synchronization).

The article properties dialog box in SQL Server 2005 is set up differently but still offers the exact same 3 properties, as shown below:

So, as the dialog boxes are almost identical, what could have changed? Let’s look at the changes one by one….

(1) The range management is now automatically managed by default. This makes sense as there was a steady flow of questions from people who were having PK conflicts on synchronization. Basically they’d used surrogate PKs which has the identity property – no problem with that – but by default the identity values on the publisher and subscriber would not be partitioned, and hence relied on manual intervention to reseed. There was a good article by Mike Hotek which explained clever ways to manually manage the ranges on each subscriber to ensure there was no overlap, but many people had already assumed this was taken care of behind the scenes and hence ran into the PK conflict problem.

(2) The default range sizes have increased. The publisher range has changed from 100 to 10,000 and the subscriber range size has increased form 100 to 1,000. The threshold percentage is set the same at 80. This makes sense as a range size of 100 is really only good for lookup tables, which more than likely will be classed as download-only anyway. (If you don’t know what “download-only” articles are then look here!).

(3) The check constraint now has 2 clauses. There was a check constraint already implemented on SQL Server 2000 but in the case of SQL Server 2005 there are 2 clauses in the check constraint rather than one, eg:

align="left"> ([yyy]>(2001) AND [yyy]<=(3001) OR [yyy]>(3001) AND [yyy]<=(4001))

The reason for this is explained below.

(4) The merge trigger code implements an “overflow range”, but it's not for everyone! The logic in the merge insert trigger dictates that the table will have the identity column reseeded once the first range is filled up. So, this explains why there are 2 check constraints – the first one is for normal use and the second one is designed to be used for the overflow range i.e. although the default range size is 1000 for a subscriber, there is potential for 2000 values to be inserted!

The clever part is that the overflow range is automatically allocated by the merge insert trigger and therefore doesn’t require a connection to the publisher.

However, this reseeding is restricted to those cases where the subsequent insert is done by a member of the db_owner role. The code below comes from the merge insert trigger:

if is_member('db_owner') = 1
begin
…
if IDENT_CURRENT('[dbo].[tTestIdentities]') = @range_end
begin
DBCC CHECKIDENT ('[dbo].[tTestIdentities]', RESEED, @next_range_begin) with no_infomsgs
End

I suppose the reasoning is that if the initial range is used up and a connection to the publisher is not possible, the administrator or a super-user could add another row in order to fire the trigger and cause reseeding to the second range. For those interested, Lizet mentions how to make a neat (and unsupported!) alteration to this security restriction that I’ll leave you to investigate if you think you'll need it.

(5) The “threshold” listed in the article properties dialog box is NOT for use in SQL Server 2005 replication. This is missed out entirely in the explanation of sp_addmergearticle but is alluded to in BOL in the text for sp_changemergearticle (“percentage value used for Subscribers running SQL Server Mobile or previous versions of SQL Server”). Its appearance in the SQL 2005 article properties dialog box (above) is quite misleading and I’ve added a BOL request to have this section amended and clarified. Embarrassingly this is something I only recently noticed while in the middle a demo at a client’s site; I’d successfully tested the use of the threshold parameter in SQL Server 2000 before going to see the client and had assumed that as the property is exposed in the same way in the SQL 2005 GUI it worked exactly the same way. I failed the demo three times and I then began to doubt that it worked at all, and after laboriously checking through the codebase that evening I confirmed it - the threshold parameter is no longer used – doh!

Conclusions

So, hopefully this sheds a bit of light on some of the changes to behaviour in the handling of identity ranges.