Are there recommended procedures for migrating database schema upgrades from development to production database servers?

I'm almost done writing a web app that uses NHibernate through a class library I've written. I know that shortly after release of the first version, I'll be changing the db schema some more to add features and such. The changes will likely include added, moved and removed columns, changed column datatypes, and new tables.

So far in development, when I change a mapping file, I just have NHibernate tear down and rebuild the whole database, and I have code to fill the db with some initial data I use for development. But how can I safely merge these schema changes into production to minimize lost data and time?

I'm hoping that there's a best practices list or something. The two ways that strike me are:

1. Write a routine that loads every single object from the database into memory (using NHibernate), then rebuild the database, then persist every object back again.
2. Go through the database structure myself (using SQL Enterprise Manager in my case) and actually add the tables, change the datatypes, etc. that were done automatically for me in the dev db. The problem I see with this is NHibernate seems to have finer control over datatypes than what SQL Enterprise Manager lets me see. For example, the varbinary datatype may appear to have one length, but if I reproduce it in another db, NHibernate somehow recognizes that it is not the original, and crashes until I let NHibernate rebuild it.

Can anyone with experience or good ideas share them?

We use a tool called Db Ghost to handle our SQL change management. It can generate change scripts to move the schema from one version to another.

Of course if you make data destructive changes such as move a column from one table to another you will have to create some manual scripts in there too.

Red Gate also makes a tool called SQL Compare that I hear is pretty good. But, we use Db Ghost to source control the DB scripts and source also. We also use it during our build scripts to update the schema from one dev build to another via NAnt.

hth,
BOb

Hum, I should really try out Db Ghost :)

ATM, I'm applying changes by hand (using SQL Server generated change scripts)

Another nice solution would be to port Hibernate's SchemaUpdate :)

I'm using Sql Compare from Red Gate. Basically works by scripting out the schema of the databases being compared and using a FileDiff like approach in showing the differences between them. Then it generates the script needed to synchronize the objects. I found it very productive and easy to use. I have no experience with DB Ghost though.

Cheers,
Radu

I'm familiar with SQL Enterprise Manager, and generating SQL scripts using it. But change scripts? How do you generate those?

In fact, I use VS.NET to modify SQL Server databases (editing columns/keys/...)
When I save, it generates the change script and ask me if I want to save it (it is possible to disable this option).

We are testing Apex Sql Diff which seems rather good. Give it a try...

http://www.apexsql.com/sql_tools_diff.asp

cheers

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere