asp.net - Deploying database changes with EF 4.1

Question

Does anyone have any best practices around deploying database changes in an EF 4.1 code-first solution? I know MS does not currently support database migrations for EF 4.1, but obviously people are going to need to do this from time to time.

Thanks

Answer 1

Once you deployed database to production you must do incremental changes. It means that before you deploy next version you must prepare two databases in your dev box:

• Database with DB schema currently deployed in production - you should be able to get this from source control so always correctly label / tag your production releases
• Database with new DB schema

Once you have two databases you can use some tool to make difference SQL script for you. I have experience with both:

• Visual Studio 2010 Premium / Ultimate Database tools
• Red Gate SQL Compare

These tools are for SQL server.

Once you have difference script you can test it on your dev box. Be aware that some more complicated changes cannot be created by difference script and require you to create custom migration script for example with storing data existing data in temporary tables while refactoring real table. Also if you use some new seed data in your new version you must add them manually into script or use Data Compare tools (also offered by both products).

After that you can plan outage of your production application, database backup and running upgrade script.