Managing stateful data is typically one of the trickier parts of a DevOps strategy.  The migrations feature of Entity Framework can help immensely with its small, independently executable, PR-friendly delta.  Theoretically, it can even grant the ability to revert migrations if a deployment goes poorly.  If used correctly the technology can be a huge help.

However, in practice, there are a lot of different ways to automate the execution of those deltas.  Each approach has pros and cons, and what may work for one project may not for another.

In this post I'll show you six ways to run EF Database Migrations, explain in which circumstances each would be most helpful, and then show how to set up Active Directory (AD) Authentication and set your connection string correctly when connecting from a build server.

1. Migrating via App

The question of when to run migrations is ... interesting.  The thing is, it's so easy to run the migrations when an app starts for the first time with the DbContext.Database.MigrateAsync() command.

Running migrations on startup is convenient and a time saver because it piggybacks off of an existing database connection and firewall rule.  But it offers some downsides:

1. Slows app startup
2. Hard to revert
3. Timeout problems for long-running migrations
4. Violates the principle of least privilege

The last point bears an explanation.  It references the following security best practice:

The principal of least privilege
In order to minimize the damage caused by security incidents, a system should be granted the minimum level of access required.  

In other words, don't grant a system any permissions it doesn't need.

For instance, as part of their day-to-day operations apps don't generally need to delete database tables, therefore they should not be granted that permission.  However, that's exactly the type of permission an app needs if it is going to run database migrations.  Thus, by having an app run database migrations we inadvertently grant attackers the ability to cause more mayhem than is necessary should they gain access to the database through an app.

To put it more concretely: if an app is connecting to a SQL Server, then it's ok to grant the account the app is running under db_datareader and db_datawriter, but not db_ddladmin, and definitely not db_owner.  Doing so will generate a flag in a security center audit.

Migrating via Build Server

Migrating via app may be good enough if you you're still in dev, intentionally deferring security risks, or are just generally comfortable with the risk.  However, the alternative, when you're ready for it, is to have your build server run EF migrations.  There are several ways to approach it, each with pros and cons.

2. Command Line Tools

As a developer, you're probably already familiar with thedotnet ef database update command.  This option is inconvenient in a DevOps world because it requires source code.  Source code is a pain for a variety of reasons, not the least of which is you'd need to get the exact version of code for the deployment you're trying to run.  The other approaches below are usually preferable.

3. SQL Scripts

If you have your build server run the command dotnet ef migrations script [oldmigration] [newmigration], it'll produce a single file that you can save as an artifact in a build pipeline and then execute for each environment.  Unlike some other options, this approach produces an asset that can be reviewed by a DBA.  However, figuring out which values to use for oldmigration and newmigration would be tricky.  Worse, if each environment is at a different version, as often happens when not every PR is deployed through to production, then it could be impossible to produce a single artifact that would work for all environments because those values would be different for every environment.  Therefore, I wouldn't generally recommend this approach.

4. Idempotent SQL Scripts

Fortunately, if you pass the --idempotent parameter to the dotnet ef migrations script, then it will generate a script that will only run those migrations that need to run.  The resulting file looks like a bunch of if statements like this:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20230110143448_Initial')
BEGIN
    CREATE INDEX [IX_AbpAuditLogActions_AuditLogId] ON [AbpAuditLogActions] ([AuditLogId]);
END;
GO

While this option doesn't grant the option to revert a bad deployment, it works great as a published build artifact can be reviewed by a DBA, and works when each environment is at a different version.  However, this approach isn't transactional, and so a failed migration could leave your database in an inconsistent state.

5. Bundles

Bundles are great.  They solve the problem of applying transactions to your migrations.  Simply have your build server run dotnet ef migrations bundle --self-contained -r [linux-x64|win-x64] and you'll have a single file binary (called efbundle.exe by default) that you can publish as an artifact, that runs migrations within a transaction, and that will run only the migrations that need to be applied.  You can even specify a specific migration, which allows you to revert migrations.  The resulting file looks like this:

.\efbundle.exe --help
Entity Framework Core Migrations Bundle 7.0.1

Usage: efbundle [arguments] [options] [[--] <arg>...]]

Arguments:
  <MIGRATION>  The target migration. If '0', all migrations will be reverted. Defaults to the last migration.

Options:
  --connection <CONNECTION>  The connection string to the database. Defaults to the one specified in AddDbContext or OnConfiguring.
  --version                  Show version information
  -h|--help                  Show help information
  -v|--verbose               Show verbose output.
  --no-color                 Don't colorize output.
  --prefix-output            Prefix output with level.

This option is almost my favorite.  The only thing it lacks is the ability to run custom code outside of a migration.

6. Command Line App

Once upon a time, a project of mine was storing encrypted data in a field in a database.  After we were in production (of course) we realized that the encryption algorithm was too strong and was causing performance problems.  We needed to decrease the strength of the encryption, but that meant we needed to perform a complex data migration involving a method written in C#.  

We would have to run a query to read every row, decrypted it with the old algorithm in C#, re-encrypted it with the new algorithm in C#, and update it back into the database.  Should be easy enough for EF Migrations right?

Surprisingly, it turned out that EF migrations can't do that type of work.  They're designed to run insert or update SQL statements or create or drop DDL statements, but not retrieve data.  Check it out for yourself: see if you can find a single method to retrieve data on MigrationBuilder.

So, since the algorithm was located in a C# method and since EF Migrations can't access it, we had to run code outside of EF Migrations.

Fortunately, because we were using ASP.Net Boilerplate (the predecessor to the ABP Framework), it contained a command line app that was running our migrations.  That provided the flexibility to run code before or after the migration and thus solve our problem.

Command line apps (or main apps with a command line option) that run DbContext.Database.MigrateAsync() from the build server can run in transactions, and when compiled with --self-contained -r [linux-x64|win-x64] are nearly single files (you do have to include Microsoft.Data.SqlClient.SNI.dll).  The downside is they don't allow reverting migrations.  However, they're my personal favorite because they offer the most flexibility when faced with tough migrations.  Also, they work particularly well in database-per-tenant multi-tenant scenarios.

If the details of this approach interest you, check out a DbMigrator sample project I created that compiles and publishes a command line app in one stage of a multi-stage build pipeline, then executes it in another.

Authentication

It's time to acknowledge the elephant in the room.  If you don't run migrations on app startup, how do you obtain a connection to your database from your build server?  Also, assuming you're using SQL Server on Azure and following best practices of only using AD authentication, how do you authenticate in a headless environment with an Active Directory account?  

Generally speaking, there are four steps.  First, create an App Registration.  Second, add the App Registration to your database with CREATE USER [{userName}] FROM EXTERNAL PROVIDER; and grant it permissions to run DDL with EXEC sp_addrolemember 'ddl_admin', '{userName}'.  Third, create a secret for the app registration:

Finally, add a firewall rule to allow access to your database from your custom build agent, or if you're using a Hosted Agent, then "Allow Azure services and resources to access this server", which in Bicep looks like this:

resource firewallRule_AzureIps 'Microsoft.Sql/servers/firewallRules@2021-11-01' = {
  name: 'AllowAzureIps'
  parent: sql
  properties: {
    startIpAddress: '0.0.0.0'
    endIpAddress: '0.0.0.0'
  }
}

Finally, you should be able to use a connection string like this: "Server=tcp:{sqlServerName}.database.windows.net,1433; "Database={dbName}; Encrypt=True; User Id={servicePrincipalAppId}; Password={servicePrinicpalSecret}; Authentication='Active Directory Service Principal';"

And that's all there is.  Plunk in your app registration app id and the secret you generated, and you're off to the races!

Summary

In this article I've discussed six ways to run migrations and followed it up with how to accomplish authentication.  The following chart will help summarize the pros and cons:

As I mentioned my personal favorite is the last, the command line app, because even though it doesn't support an automated revert, it'll run in transactions and it has the flexibility to perform complex migrations outside of EF.

What approaches have you tried?  Did I miss anything?  Feel free to chime in in the comments or hit me up on twitter or mastodon.