Change ID generator in nhibernate and migrate existing database

Question

I have an existing product using the increment ID generator for most db entities. A new version should allow clustering of multiple server instances working on the same database. The product supports use of MSSQL and Oracle databases.

So I consider changing the ID generator to native, but there are some issues with that.

• Two different algorithms will be used for Oracle and MSSQL - will that be transparent when creating objects in the code?
• How can I migrate existing databases and how will I get the generator to not use the IDs already in use?

Thanks in advance for any insights on this.

Answer 1

I would suggest looking at a hilo generator strategy. The benefit is that it can be used for multiple processes, and you still retain the performance benefit of using a generated id in NHibernate (specifically allowing batching of inserts).

MSSQL does not allow you to change a column to be an identity column - you will need to add a new column and then update all the foreign keys - if you have a lot of tables / relationships, this can be very very messy.

With the hilo generator strategy you can avoid that issue altogether, it's just a configuration change and adding a table to your database to store the table high values, and populating that table with the correct values.