How to avoid data loss with EF Model First database schema upgrade?

Question

This is a long question, but I would be very very thankful if I can get some good advice on this. In short, I’m looking for a good approach for version upgrade of MS SQL database schema that also demands data being moved from deleted tables into new tables.

I think Stack Overflow is the most appropriate place for this question (not dba.stackexchange.com) because at its core, this is an issue for .NET developers using Entity Framework, and the database parts of this consists mostly of auto-generated sql scripts.

---

Background

A .NET application and SQL database is running in Azure (The application in worker roles and the database in Azure SQL). Until now, version upgrades have worked fine, because all database schema changes have been simple (like adding a new column). However, from now on I also need to deal with moving data from one table to another during upgrades. (I’m able to fix this temporarily by creating a new database, generate a script with data from the old database and manually edit the script to make it fit the new schema, but I hope there is a better approach).

I use Entity Framework and I use Model First. Entities and associations are defined in Visual Studio Data Model Designer, and this approach is very appropriate for my application.

I use a dacpac to upgrade the Azure SQL database, and this approach has worked well until now (but now I will get data loss, so now I must find a way to move data to new tables).

I hope I can continue to use entity framework and defining entities/associations in the designer, but it’s fine to switch away from dacpac upgrade to another technology if needed.

Upgrade approach until now

1. I add new entities (tables), associations (relations) and properties (columns) in the designer.
2. I right-click, pick “Generate Database from Model…” and this results in a .sql script that drops old database objects and creates the new database objects.
3. I create an empty database and run the script to create the tables/keys etc.
4. In SQL Server Management Studio, I right-click the database and pick “Tasks -> Extract Data-tier Application…”. When the wizard completes I get the dacpac I need (Actually I can now delete the database, since I only created it to be able to get the dacpac file, since I don’t think I can generate it in Visual Studio Data Model Designer).
5. I right-click the Azure SQL database and pick “Tasks -> Upgrade Data-tier Application…” and follow the wizard. Until now I have never had data loss, so this has worked fine!

Current situation

This is a simplified example to illustrate the issue, but I will get into almost identical situations quite often from now on it seems. Look at the old and the new version of the schema in the figure below. Assume there is already data in the database. I need the data in ImageFile to end up in ImageFileOriginal or ImageFileProcessed depending on the IsOriginal boolean/bit value. Using “Upgrade Data-tier Application” I will get alerted of data loss. What approach would you recommend to deal with this? As I said earlier, it’s fine to switch away from dacpac upgrade to another technology if needed.

I have read about Visual Studio Database Projects, Fluent Migrator, Red Gate and Entity Designer Database Generation Power Pack (It doesn't support Visual Studio 2012), but I didn’t find a good way for this. I admit I haven’t spent a whole day digging into each technology, but I certainly spent some time to try finding a good approach.

Answer 1

The best way to migrate database schema (create / delete tables / columns) and also data, is using the SSDT - Sql Server Data Tools, available for Visual Studio 2010 and Visual Studio 2012.

Here are some very useful links:

http://msdn.microsoft.com/data/tools http://blogs.msdn.com/b/ssdt http://msdn.microsoft.com/en-us/data/hh297027