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.

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

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

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, $database.name)
    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 :)