nhibernate + migrations workflow

I'm looking to refine my workflow around NHibernate and a relatively frequently changing schema, and how best to deal with this - I would like the same solution to apply to production systems, so I think I need a migration开发者_Go百科 engine rather than just Schema Update.

What I want to know is how i can refine the workflow as far as possible so that I'm doing as little work as possible to get my database in sync with my domain model. The tarantino approach of scripts looks nice, but there doesn't seem to be a way to generate the update script from my nHibernate mappings, so i'm down to hand crafting a script or using a redgate sql compare like tool. Is there anything I'm missing that would make life easier around the script generation stage?

Thanks, Chris

---

I haven't used any of these migration tools for .net myself, but when trying out Ruby on Rails on my spare time some years ago, I saw the benefits of migrations compared to the t-sql scripts we used at my job at the time.

---

In a recent project I've been, we've discovered that migrations, combined with VCS branching and NHibernate can cause several headaches and drawbacks along the way.

What we did was to set up NHibernate to generate the schema with each automated build (for the development environment) along with some data loading.

For the production environment, we had a script that based on the current schema and the desired schema, it generated one migration with the necessary fields and modifications.

---

We use SQL Compare. It is paid for but worth the investment imho. Keep each of the generated scripts well organized - i.e. timestamped - and you get yourself a good way of generating the database for any of the released versions.

This is our usual flow

1. During DEV we have two databases "ProjectName" and "ProjectName_TEST".
2. For every schema change we generate (NHibernate) a brand new database and replace "ProjectName_TEST".
3. We use SQL Compare to update "ProjectName" (therefore keeping all dev data in it)
4. On release, we compare "ProjectName_TEST" with the Production Database and generate an update script.

Have a look at the command line options as they are quite handy to automate the process via VS build events.