Deploy database versions. Entity Framework Migration vs DacPacs SSDT

Question

I have a data oriented application with SQL Server. The environment in which it will be deployed is not under our control, and there is no database administrator (all of them are small enterprises), so we need the distribution process for each application / database update to be as automatic as possible.

Besides the usual changes between versions of the application (sometimes unpredictable), we already know that we will need to distribute some new seed data with each version. Sometimes these seed data will be associated with other data in our system. For example: you might need to insert 2 new rows of some master data during the v2-v3 upgrade process and some other 5 rows during the v5-v6 upgrade process.

Ef

We checked the Entity Framework Db Migrations (available for existing databases without Code-First from version 4.3.1), which is a traditional sequential script in a more automatic and controlled way (for example, Fluent Migrations).

SSDT

On the other hand, with a different philosophy, we tested SSDT and its dacpacs, snapshots and scripts before and after deployment.

Questions:

• Which of these technologies / philosophies is more suitable for the described case?

• Any other technologies / philosophies you can use?

• Any other tips?

Thanks in advance.

Answer 1

This is an interesting question. Here at Red Gate, we hope to solve this problem at the end of this year, as we have many customers who are asking how we can provide a simple deployment package. We have an SQL Packager that essentially transfers the SQL script to exe.

I would say that dacpacs are designed to cover the case you are using. However, as I understand it, they work with dynamic script deployment when applied to the target. The downside is that you won’t get the warm fuzzy feeling that you might have when deploying a pre-tested SQL script.

I have not tried updating data with dacpacs before, so I would be interested to know how well this works. As far as I remember, it trims the target tables and repopulates them.

I have no experience with EF migration, so I would be interested to read the answers on this topic.

Answer 2

We are likely to make a hybrid decision. We would like to abandon the idea of ​​deploying packages, but, on the other hand, due to the nature of our applications (small enterprises as end users, no database administrators are required to update, so several “live” versions of the database coexist) we cannot refuse full control of the migration process, including the scheme and data. In our case, the pre- and post-deployment scenarios may not be sufficient (or at least not convenient enough) for a complete migration, for example, EF Migrations. Changes such as adding / removing source data, changing a one-to-many relationship to a many-many relationship, or even drastically changing a database schema (and therefore migrating data to this schema from any previous released schema) may be part of our day job at the release of our first version.

Thus, we are likely to use the EF transitions with the Up and Down system for each version. Basically, each "Up" will call dacpac with the last database snapshot (and each Down, its previous), each with its own deployment settings for this particular migration. EF migrations will handle the version control line, possibly also some of the complex parts of data migration.

We feel more confident in this hybrid form. We skipped automation and detection of schema changes in the Entity Framework Migration, as we skipped managing the lineup in Dacpacs mode.