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:
- It requires to do each change twice: First – in the DB, and Second – in the code.
- 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.
