Raph’s Blog

Migrations VS DACPACs

Dealing with SQL Database changes on production should always be automated. There are various techniques we can use and today I will compare two popular approaches. A migrations approach and a DACPAC approach.

The two approaches

Below is an explanation of the two approaches. Feel free to skip this section and go straight to the next section if you are aware of the two approaches.

Migrations

The migrations approach involves running a set of scripts against a target database in a predetermined order. The target database will have a migrations meta table with the scripts that have already been run, so that they are not run again. Some tools allow you to specify certain scripts to be run regardless if they’ve been run previously or not.

Popular .NET tools include DbUp and Entity Framework Migrations.

DACPAC

A DACPAC (Data-tier Application Component Package) is a package that contains the state of your database. It is generated from tools such as SQL Projects that you can create from Visual Studio 2017. From a code perspective, this means a set of CREATE scripts for all your entities. Occasionally, when you modify an object the CREATE scripts may not accurately reflect the state of the database (for example: when you rename a column) in this case a refactor log is kept in the DACPAC. This refactor log is similar to a migrations meta table.

Which route to take? Which route to take? Credit: Greg Jeanneau

Criteria 1: Ease of Setting Up

You can easily setup migrations to run on your application’s entry point, they are included in ORM (object relational mapper) frameworks (such as Entity Framework) and you’ll be able to execute your migrations with one method call.

On the other hand, generating a DACPAC file means creating a new SQL Project that is difficult to reference from your existing application. You’ll need to create a step in your release pipeline to ensure that it is applied and this may mean an added overhead when trying to initially get setup.

Migrations have the edge here, simply include DbUp or Entity Framework in your solution, modify your entry point to execute migrations and that’s it!

Criteria 2: Code Maintainability

Migrations work by having a set of scripts in a folder that will grow over time. When modifying an existing SQL Database object, you’ll need to create a new file for each set of changes that get deployed.

When dealing with programmability objects in DbUp, you can choose to set scripts to Always run, this means they will run against the target database on every deployment, these scripts will need to drop objects and re-create them. If you’re doing this with tables there will be data loss so I recommend against it.

If you’re frequently modifying fields on SQL Tables, you will need to commit multiple SQL files with ALTER statements for each set of changes. On the other hand, SQL Projects that generate DACPACs will have all stored procedures, functions, or tables as CREATE statements, so if you need to modify them, you’ll modify the CREATE statement on the original file. This gives you a git history of SQL Objects just like any code file.

Over time, you could have hundreds of SQL Scripts in a migrations folder. This is why in terms of maintainability DACPACs win!

Criteria 3: Integration with Release Pipelines

Migrations are easy to integrate into a release pipeline. If you use Entity Framework migrations, you automatically get the dotnet ef database command. If you use DbUp, an executable is built that is used to run migrations against a target database.

Likewise, DACPACs are easy to integrate into a release pipeline. The tool SqlPackage.exe is used to deploy a DACPAC to a target database. This tool comes with SQL Server 2017 or Visual Studio 2017.

It was hard to decide a winner here, but I’ll choose migrations only because it’s a bit more difficult getting SqlPackage.exe on a build agent. Migrations though, are usually self-contained or come with the standard dotnet command line tool.

Criteria 4: Development Experience

The development experience with migrations is great, all you do is add migrations to your migration folder! For example: you can modify a field on your ORM classes and create a migration script for it. If you’re using Entity Framework migrations the dotnet ef database command scaffolds the migrations for you, even easier!

Using a SQL Project to generate a DACPAC is a great experience as well. The tooling allows you to create a pre and/or post deployment scripts, rename fields, write unit tests, add fields, and user permissions. Basically, anything you can do on a database, you’ll be able to do with SQL Projects. In this case, the development experience may not always be tightly-coupled to your application code, but that’s not a bad thing. In fact, it can be a positive thing, especially if your database is used by multiple applications.

Migrations definitely provide an easy development experience. Easy, does not necessarily mean better though and there are problems associated with the development experience using migrations. Code is read a lot more than it is written, and the underlying structure of a database is better understood using SQL Projects than they are using migrations.

For example, if you’ve chosen a migration approach and a field is introduced into a previously created table, you will be reading two separate migrations in isolation to each other, however, using SQL Projects, everything is declared as CREATE statements so you will read the definition of the table from one file.

Initially, I though the development experience of using migrations was better because it was simpler. After several months of using SQL Projects though, I would say that DACPACs win on this criteria.

Criteria 5: Testability

Modern day tooling has made writing tests against in-memory databases easy. With Entity Framework you can use the In-Memory Database Provider for your tests and even run your migrations against it! This means that you can write automated tests in the same language as your application code while hitting a database that behaves close to the one you have deployed in production.

SQL Projects give you the ability to write tests against any valid database. The tests themselves are written in T-SQL and the asserts can be configured to (non-exhaustive):

  • Result count
  • Scalar Value
  • Execution time

DACPACs win here. Writing automated tests against a database isolated away from application code, means that the unit-under-test is the database. This achieves a better separation of concerns and more granular tests as the tests themselves are hitting the database directly not the application code that hits the database.

Conclusion

A count of each criteria will show that a migrations approach won on two occasions, while a DACPAC approach won on three occasions. This does not mean that using a DACPAC approach is the best solution for all scenarios.

Each approach has its advantages and to help you pick which approach is right for you. It’s important to ask yourself the right questions

  • Is my application the only one accessing the database?
  • Am I using my SQL Database only as a datastore?
  • Will I only be using an ORM to access to the database?
  • Am I early on in my development cycle and need to validate a business assumption?
  • Will data clean-up be a regular occurrence?

If you answered ‘yes’ to most of the above, then you should consider taking a migration approach.

  • Am I reliant on programmability objects that need validation and testing?
  • Is the database used by several applications?
  • Will I need to tune database indexes?
  • Do I want greater control and visibility of data types and relationships?
  • Will my table schemas be changing often?
  • Will I have complicated roles and security that require database level visibility?

If you answered ‘yes’ to most of the above, then you should consider using DACPACs.

You can always opt for a migrations approach while your database is simple and easily convert it to a SQL Project (which builds a DACPAC) when your database becomes more complex. Just reverse engineer your database by generating CREATE scripts using SQL Server Management Studio and add them to a SQL Project.

A definite answer to which approach is best would probably not do every scenario justice, as such, I would encourage you to learn both approaches to properly understand which to choose. Whatever decision you make, it’s easy to migrate from one to the other. What’s important is that you are aware of the value that both approaches provide.