How to migrate primary key generation from "increment" to "hi-lo"?
- by Bevan
I'm working with a moderate sized SQL Server 2008 database (around 120 tables, backups are around 4GB compressed) where all the table primary keys are declared as simple int columns.
At present, primary key values are generated by NHibernate with the increment identity generator, which has worked well thus far, but precludes moving to a multiprocessing environment.
Load on the system is growing, so I'm evaluating the work required to allow the use of multiple servers accessing a common database backend.
Transitioning to the hi-lo generator seems to be the best way forward, but I can't find a lot of detail about how such a migration would work.
Will NHibernate automatically create rows in the hi-lo table for me, or do I need to script these manually?
If NHibernate does insert rows automatically, does it properly take account of existing key values?
If NHibernate does take care of thing automatically, that's great. If not, are there any tools to help?
Update
NHibernate's increment identifier generator works entirely in-memory. It's seeded by selecting the maximum value of used identifiers from the table, but from that point on allocates new values by a simple increment, without reference back to the underlying database table. If any other process adds rows to the table, you end up with primary key collisions. You can run multiple threads within the one process just fine, but you can't run multiple processes.
For comparison, the NHibernate identity generator works by configuring the database tables with identity columns, putting control over primary key generation in the hands of the database. This works well, but compromises the unit of work pattern.
The hi-lo algorithm sits inbetween these - generation of primary keys is coordinated through the database, allowing for multiprocessing, but actual allocation can occur entirely in memory, avoiding problems with the unit of work pattern.
