The Short Answer
No, it is not safe to rely on hbm2ddl.auto=update or the SchemaUpdate tool for zero-downtime migrations in a production environment. You should use explicit migration scripts following the Expand-Contract pattern to ensure deterministic changes and backward compatibility.
Why Automatic Updates Fail in Rolling Upgrades
NHibernate's automatic schema update tool is designed for development convenience, not production orchestration. When multiple instances of different versions run simultaneously, several risks emerge:
- Race Conditions: Multiple nodes attempting to apply the same
ALTER TABLE statement can lead to database locks or execution errors.
- Lack of Precision:
hbm2ddl is additive; it cannot handle column renames, data migrations, or complex constraint changes without manual intervention.
- Unpredictable State: There is no versioned history of changes, making it nearly impossible to audit exactly which node applied which change and when.
The Recommended Workflow: Expand-Contract
To achieve zero downtime, decouple the database deployment from the application deployment using these phases:
1. Expand Phase (Additive)
Apply a SQL script to add new columns or tables. These must be nullable or have default values so that the old version of the application (which is unaware of these columns) can still perform INSERT operations without failure.
2. Migration Phase (Data Sync)
If moving data from an old column to a new one, use a background process or database triggers to synchronize data. The new version of the application should be deployed to perform "double-writes" (writing to both old and new columns) to maintain consistency for any remaining old nodes.
3. Contract Phase (Cleanup)
Once all application instances are upgraded and the old columns are no longer being read or written, apply a final script to remove the obsolete schema elements.
Verification Steps
To ensure the migration is safe, perform the following checks:
- Additive Validation: Run the old application version against the "Expanded" schema. Verify that
INSERT and UPDATE operations succeed despite the presence of new columns.
- Proxy Initialization: In NHibernate, ensure that new properties are mapped as optional or handled via
dynamic-component if necessary. Verify that lazy-loaded proxies do not throw PropertyNotFoundException when the old code encounters a schema it doesn't fully recognize.
- Lock Monitoring: Use
sp_who2 or sys.dm_tran_locks in SQL Server during the script execution to ensure migrations aren't causing blocking chains that lead to application timeouts.
Missing Detail: Are you using a specific migration framework (e.g., FluentMigrator, DbUp) or raw SQL scripts? The recommendation for execution orchestration depends on whether you have a tool to track migration versions across environments.
nhibernate