Run EF Core Migrations in multiple databases with Azure DevOps

August 12, 2021 by Anuraj

Azure DevOps EFCore

This is a follow-up post. Last year I wrote a blog post running EF Core migrations in Azure DevOps. And I received few comments asking about how to deploy the script to multiple databases instead of a single database. So in this post, I will explain how to deploy the script in multiple databases.

In this blog post, I am implementing the solution for the SQL Server database. This solution is useful if you’re developing SAAS applications with isolated databases for each tenant or customer. Here is my existing build pipeline, which deploys the generated script to SQL Server using Azure SQL Database deployment task.

Azure DevOps Build Pipeline

To deploy to multiple databases, first I am creating build environment variable with the connection string to master database. It can be any database.

Azure DevOps Build Pipeline variable

Next, I am disabling the Azure SQL Database deployment task and adding a Powershell Script task. You can choose the Inline Type.

Powershell Task added

And add the following code in the script, you can commit this code into source control and configure it as file, for the demo purposes I am using Inline option.

$masterdb = "master"
$scriptFile = "$(build.artifactstagingdirectory)/tododbscript.sql"
$databases = Invoke-Sqlcmd `
  -Query "SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')" `
  -ConnectionString $env:MasterConnection
foreach($database in $databases)
    $databaseConnection = $env:MasterConnection.Replace($masterdb, $
    Invoke-Sqlcmd -InputFile $scriptFile -ConnectionString $databaseConnection

In this code, first, we will enumerate all databases from the master database - you can customize this step based on your requirements. And in the loop, I am changing the connection string with the database name and then executing the script file generated by EF Core migration with the replaced connection string. This way you can deploy EF Core migration script to multiple databases with out repeating the Azure SQL Database deployment task.

Happy Programming :)

Copyright © 2024 Anuraj. Blog content licensed under the Creative Commons CC BY 2.5 | Unless otherwise stated or granted, code samples licensed under the MIT license. This is a personal blog. The opinions expressed here represent my own and not those of my employer. Powered by Jekyll. Hosted with ❤ by GitHub