Deploying Entity Framework Model First

Deploying Entity Framework Model First

While working on a project that uses Entity Framework, we noticed that the auto-generated script, created from the Model, overwrites the old scheme and moreover, erases all the data.

Since the project was relatively small and the team working on it was not big, this was not a problem.  However, once the project started to be manually tested by QA, we had to find a simple deployment process to update the DB without restarting it.

The problem:

We didn't want to use EF migrations because of two reasons:

1. It requires to do each change twice: First – in the DB, and Second – in the code.
2. We didn’t want to manage every small change.

The solution:

We found a simple and easy solution that satisfies our needs:

Step 1

We ran the auto-generated script on a side DB (that will be erased after the process)

So at that point we had 2 DBs:   

Old DB – the original DB which exists in production and isn't updated.

New DB– the new and temporary DB which has the updated scheme.

Step 2

We executed the SqlPackage command line tool (part of SQL Server) on the New DB and created a .dacpac file.

A "dacpac" is a file with a .dacpac extension, which holds a collection of object definitions that one could find in a SQL Server database such as tables, stored procedures, views etc.

Step 3

Using the created .dacpac file we executed the Publish command on the SqlPackage tool, which compares between the two DBs and allows actions such as add/remove/update on fields, types, SPs and more.

The main problem with the publish command is with the implementation that could cause a serious performance issue. In some cases, the original table is copied to a side table with all the data, then a new table is created with the new scheme and lastly, the data from the side table is copied into the new table. Therefore, in case of a large data set it could take a long time.

Summary

DB migrations is a known issue, and there are a lot of good solutions out there. In our special case we decided to use a simple and easy solution which can be implemented with basic tools that arrive with the SQL Server version we had. This solution does not fit in any situation, however, for us it did the work.