Parallel DACPAC Deploy

DeployDatabases.ps1

param (
    [string]$servername,
    [string]$user,
    [string]$pwd,
    [string]$dacpac,
    [string]$profile,
    [string]$prefix = "Portal",
    [bool]$debug = $false
)

Write-Host "~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"
Write-Host "Database Deployment"
Write-Host ""
Write-Host "Parameters"
Write-Host "----------"
Write-Host "Server:  $servername"
Write-Host "DACPAC:  $dacpac"
Write-Host "Profile: $profile"
Write-Host ". . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ."
Write-Host ""

# Connect to the server's master database to get
# a listing of all the databases that exist on the
# server for this particular DACPAC
#
$sql_server = New-Object System.Data.SqlClient.SqlConnection
$sql_server.ConnectionString = "Server=$servername;User ID=$user;pwd=$pwd;Initial Catalog=master"
$sql_server.Open()

$database_cmd = $sql_server.CreateCommand()
$database_cmd.CommandText = "select d.name from	sys.databases d where d.name like '$prefix[_]%' order by d.name"

$adp = New-Object System.Data.SqlClient.SqlDataAdapter $database_cmd
$table = New-Object System.Data.DataTable
$adp.Fill($table) | Out-Null
$sql_server.Close()

# Convert this list over to a collection of
# object parameters to send to the parallel
# process script
#
$databases = @($table | select -ExpandProperty name )

$params = New-Object System.Collections.ArrayList
foreach ($db in $databases) {
    $params.Add(@{
        ServerName = $servername
        Database = $db
        Username = $user
        pwd = $pwd
        Dacpac = $dacpac
        Profile = $profile
        Debug = $debug
    }) >$null
}

# For each of the parameter objects we
# just built, pass them into a parallel
# loop, limiting the number of threads
# to 5.
#
$params.ToArray() | ForEach-Object -ThrottleLimit 5 -Parallel {
    Invoke-Command -ScriptBlock {
        param($deploy_params)
    
        $dacpac = $deploy_params.Dacpac
        $servername = $deploy_params.ServerName
        $db = $deploy_params.Database
        $user = $deploy_params.Username
        $pwd = $deploy_params.pwd
        $result = 0

        Write-Host "Started deploying database $db ..."

        try {
            if ($deploy_params["Debug"]) {
                Write-Host $deploy_params
            }
            else {
                & "C:\Program Files\Microsoft SQL Server\150\DAC\bin\sqlpackage.exe" `
                    /Action:Publish `
                    /SourceFile:"$dacpac" `
                    /TargetServerName:"$servername" `
                    /TargetDatabaseName:"$db" `
                    /TargetUser:"$user" `
                    /TargetPassword:"$pwd" `
                    /p:BlockOnPossibleDataLoss=False `
                    /p:TreatVerificationErrorsAsWarnings=True `
                    1>$null
                $result = $LASTEXITCODE
            }
        }
        catch {
            $output = "ERROR: Unable to publish database $db. $_.Exception.Message \n $_.ScriptStackTrace"
			Write-Host $output -ForegroundColor Red
        }
        finally
		{
			Write-Host "Finished deploying database $db with exit code ($result)"
		}
    } -ArgumentList $_
}

Write-Host ""
Write-Host "Database deployment completed"
Write-Host "~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~"