There are a number of migration libraries for .NET. Which one do you prefer and why?
For those of you who haven't heard of migrations. This is something that Ruby on Rails made popular. It is a way to specify your database schema and version in code, and easily "migrate" between versions of your database.
Here is an article on .NET Migration solutions http://www.flux88.com/DatabaseMigrationsForNET.aspx. I want to know what you have used and found helpful.
@Chris Smith "Migrations" in this case means migrating from one version of a database schema to the next.
Say your customer has version 3.7 of your application, in which the database schema is version 3.7, and they are upgrading to version 3.8.
If there are schema changes involved, then there will be a script (generated by your 'migrations' tool of choice) for helping the end user get where they need to go.
Ruby on Rails has a much-loved solution to this problem, that has been emulated in many other languages, including some .net versions.
The Castle project has a 'migrator' sub project, for example.
RikMigrations is a dot net migrations port written by Richard Mason (available at CodePlex)
CodePlex also has a project named Dot Net Migrations -- code available.
-1 on RikMigrations.
I'm currently using the Subsonic Migrator, since it works well with my subsonic models.
I have used Migrator.NET and SubSonic. I believe Migrator.NET has been in development for longer and I prefer it over SubSonic.
Migrator.NET finds migrations in your assembly (project) based on an attribute, not the filename. Filename is more in line with the convention over configuration way of working, and is probably inspired by Ruby on Rails. I find attributes to be more natural for .NET.
Migrator can execute provider specific SQL queries in your migrations. It's not something you do often, and it might not even be recommended, but it can really be valuable when you need it.
The codebase for Migrator.NET seems very solid to me. They have a nice provider model that accounts for the vendor specific syntax/rules. There are also several runners available: console, MSBuild and Nant, and there's also a contributed webforms runner for ASP.NET. I usually configure the MSBuild runner as an External Tool in Visual Studio.
Edit: I have written an article explaining how to run migrations in Visual Studio.
One thing missing from Migrator.NET is configuration using web.config, but that's a minor issue, since you usually configure it once and then forget about it. But the way it's done in SubSonic makes it overall easier to switch configurations, like when you need to run a migration on the staging or live server, instead of on your local machine.
Hope that helps!
Thats a very good question. Very beneficial :) But... where I can find Machine.Migrations? I seems googled everywhere but can't find it (only find the introduction blog stuff etc..)
I wrote a hard to get wrong tool for doing this at http://code.google.com/p/simplescriptrunner/
I have a complementary tool which generates the upgrade scripts for you too at http://code.google.com/p/migrationscriptgenerator/
SQL Server only at the moment mind
I've been using my own tool (octalforty Wizardby, what a shameless plug) for over two months and have just released it to the public. Pretty nice thing.
I've been using RoundhousE. It is sql based and it meets a lot of auditing concerns (we are governed by SOx where I work). Plus it versions your database based on source control (if you want it to - it can version based on whatever you want).
I think that SQL database should be the only source for maintaining database schema but concept like Migrator.NET holds database schema in its own structure. The problem with such a concept is that DBAs must keep the rules of using Migrator.NET for every single schema change. Well, it might be acceptable by them after an argue but the problem comes if somebody would do schema changes directly in database without tracking them also in Migrator.NET. I posted a couple of articles about issue of database continuous integration at http://petrkozelek.e-blog.cz/2010/01/12/database-continuous-integration-with-dbtacker/.
I fully agree with concepts that each developer has his own database server instance. Concepts like Migrator.NET are valueable when you have to develop your application to be used with different database platforms. In this case Migrator.NET or, generally talking, some kind of structured language for definition of database schema and its changes will be a middle layer betweeen application logic and particular database dialect.
Another approach that I am a fan of is to use the set of migration scripts. I.e. all schema changes are written only in these migration scripts which contain raw SQL commands for particular database platform. With DBTracker it is easy to initiate new or update existing database. The benefit of such approach is that you can still use advanced features for particular database platform. Compared to that, Migrator.NET at first has to support advanced features (disable trigger, for example) and then you can use it.
Concept using markup language naturally limit you because their syntax is just a wrapper around database query language. Database schema changes should be tracked by its natural (SQL) language. We should not try to develop another language syntax for that.