SQL Server schema evolution (migrations)

Talking to production SQL Server applications it's very important to support scenarios allowing to modify database schema during the application life cycle. In my particular case it's Entity Framework 4.1 application.

Today ADO.NET team w开发者_Go百科orks on Code First Migrations. I think it's really great but it's in alpha stage and can be used only with EF Code First.

What are the best practices to implement such a workflow writing update (up and down) scripts in SQL language for production use? Are there any supporting tools?

Deployment scenarios are also very important. How to automate migrations on a build server (using MSBuild, for example)?

There are quite a few - find a long list of alternatives here.

I've been looking into this myself, trying to avoid doing any further development on our own, internal home-grown solution (which is a mess....).

The one that I was intrigued about the most is Roundhouse

It works off SQL scripts (which is a must for me), it allows one-time scripts, everytime scripts and so forth, it keeps track of what it's done, it allows environment-specific scripts (e.g. stuff for your DEV or TEST environment that you don't want on production), it's highly configurable - looking good to me!

I prefer use plain SQL query for SQL schema migration but it's not easy if you don't have a database version control, If you use .net there is a NuGet Package which is free and will control your database version and it work with plain SQL query.

https://www.nuget.org/packages/SQLMigrationByQuery