Build once, deploy many: EF Core migrations on Azure SQL

The past few weeks I was creating the CI/CD pipelines for one of my teams. The build and release pipelines for a .NET backend on Azure App Service is something most dev teams working on Azure have already done so I will not go into detail for that part.

What teams struggle more with, is getting their SQL changes deployed. A decade ago, I’ve used solutions like DACPAC. But these days we’re often using EF Core code first migrations for the development cycle.

The problem

If we look back at the CI/CD concepts, we build our codebase once and deploy the same output on multiple environments after testing.

This will result in possibly each environment being on a different version:

Let’s say the next pull request merged on Dev is the actual release for version 2.0, with just code changes and no database changes. The table below shows the number of migrations to be executed on each environment’s database if we can promote this exact build to all environments.

As you can see, the challenge here is creating a single executable which can run on all environments without failure.

Solutions

There are many options to tackle this, but let’s only look at those requiring not to restart from scratch (e.g. writing SQL scripts) since we’re already using EF Core Migrations in the development cycle.

The not so clean DevOps way

Commands like Update-Database or its CLI equivalent dotnet ef database update require source code. In a release pipeline I prefer to work with binaries rather than source code, so let’s drop this one.

Quick and easy: app startup

The solution could be as simple as updating the database from within your app startup with this single line of code:

    await _context.Database.MigrateAsync();

While this might seem a smart idea, it holds a lot off possible issues:

• Slower app startup
• Possible timeout for big migrations
• Harder to troubleshoot (logging required)
• Hard to revert
• Requires elevated SQL rights (change schema rather than only manage data)

Clearly less smart than we initially thought, certainly for a production environment.

A DevOps way: EF Bundles

EF Bundles are single-file executables that can be used to apply migrations to a database. They address some of the shortcomings of the SQL script and command-line tools:

• Executing SQL scripts requires additional tools.
• The transaction handling and continue-on-error behavior of these tools are inconsistent and sometimes unexpected. This can leave your database in an undefined state if a failure occurs.
• Bundles can be generated as part of your CI process and easily executed later as part of your deployment process, even with different target versions.
• Bundles are self-contained executables and don’t require installing the .NET SDK or EF Tool.

Build pipeline

The first step is creating the EF bundle and add it as an artifact to our pipeline output.

  # Only doing this after API publish to keep output clean and not mess with publish paths
  - task: DotNetCoreCLI@2
    displayName: 'Install Dotnet EF Tool'
    inputs:
      command: custom
      custom: "tool"
      arguments: "install --global dotnet-ef"

  - task: DotNetCoreCLI@2
    displayName: EFBundle
    inputs:
      command: custom
      custom: 'ef '
      arguments: 'migrations bundle --self-contained --project "src/Database.csproj" --startup-project "src/Api.csproj" -r win-x64 -o $(Build.ArtifactStagingDirectory)/SQL/Bundle.exe --verbose'

  - task: CopyFiles@2
    displayName: 'Copy AppSettings for EF Bundle'
    inputs:
      contents: 'src/Api/appsettings.json'
      targetFolder: '$(Build.ArtifactStagingDirectory)/SQL'
      flattenFolders: true ## copy to the root instead of keeping the full directory structure

  - task: PublishBuildArtifacts@1
    displayName: 'PublishArtifacts - SQL Bundle'
    inputs:
      pathToPublish: '$(Build.ArtifactStagingDirectory)/SQL'
      artifactName: 'SQL'

You could add a condition to your tasks to only do this on e.g. the main branch, or not when a PR is created to speed up those builds.

    condition: and(succeeded(), ne(variables['Build.Reason'], 'PullRequest'))

Release pipeline

Since you’re probably used to create backend release pipelines already, I will stick to executing the EF Bundle only:

    - task: AzureCLI@2
        displayName: 'Azure SQL Schema Deployment'
        inputs:
        azureSubscription: ${{ parameters.AzureSubscription }}
        scriptType: pscore
        scriptLocation: inlineScript
        inlineScript: |
            cd $(Pipeline.Workspace)\buildpipeline\SQL
            .\Bundle.exe --connection 'Server=tcp:$(SqlServer).database.windows.net;Authentication=Active Directory Default; Database=$(SqlDatabase);'            

Here we’re using variable groups for the Azure SQL Server name and SQL Database name. Security is done through Azure Entra ID, this means that the service principal executing the Azure DevOps pipeline has access to Azure SQL and is db_ddladmin.

Output

If you run your pipeline on a new environment, you might see output similar to:

Applying migration '20240317205346_InitialMigration'.
Applying migration '20240319122218_AddUser'.
Done.

If the initial migration was already applied, it would only run the second migration. Running the deployment pipeline once again will just say:

No migrations were applied. The database is already up to date.
Done.