How to use PowerShell to batch call Update-Database

Question

We use an Azure Elastic Pool resulting in multiple client databases and one master database with references to the client database.

We already have multiple databases and are working on a new version of the code. We use EF6 Code-First. When we make a change to our model (add a property) we create the migration file and need to call Update-Database for all existing client databases. This is monkey work we want to skip.

I already have a Powershell script to connect to the master database and execute a query on a table. This returns the names of the child databases. With it I can change the Web.config and replace the Template database name with the proper name of the child database.

Now I need to call Update-Database to execute the migration scripts. With this last part I'm struggling because I'm running the ps1-script outside Visual Studio and thus the command Update-database is unknown. I tried using migrate.exe but then I get lots of errors.

I think the easiest solution is to run my script within the Package manager console but I can't figure out how to do that.

Answer 1

I managed to get it working. After I placed the ps1-file in the root of my code folder I could run it in the Package Manager Console using .\UpdateDatabases.ps1.

For completeness here's the script I created. I'm new to PowerShell so some optimizations might be possible.

cls
$currentPath = (Get-Item -Path ".\" -Verbose).FullName
#Read Web.config
$webConfig = $currentPath + "\<your project>\Web.config"

$doc = (Get-Content $webConfig) -as [Xml]
$DatabaseNamePrefix = $doc.configuration.appSettings.add | where {$_.Key -eq 'DatabaseNamePrefix'}

#Get Master connectionstring
$root = $doc.get_DocumentElement();
foreach($connString in $root.connectionStrings.add | where {$_.Name -eq "Master"})
{
   $masterConn = $connString.connectionString
}

#Connect to master database
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $masterConn

#Query Client table for the child database names
$SqlQuery = "select Code from Clients"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
#Put query result in dataset
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

foreach ($row in $DataSet.Tables[0].Rows)
{
    $clientDbName = $row[0].ToString().Trim()    
    #Change Web.Config
    foreach($connString in $root.connectionStrings.add | where {$_.Name -eq "DevelopmentDb"})
    {
       $newDatabaseName = "Database=" + $DatabaseNamePrefix.value + $clientDbName + ";";
       $newConn = $connString.connectionString -replace "(Database=.*?;)",$newDatabaseName
       $connString.connectionString = $newConn;
    }
    $doc.Save($webConfig)

    #Update database
    Update-Database -ConfigurationTypeName Application
}    
"Finished"

Answer 2

You may want to take a look at Azure Elastic Database Jobs. Which is designed to work with the elastic database pools.

The Elastic Database Jobs SDK includes also PowerShell components.