Powershell Deployment speed

AdamBowler · January 21, 2019 9:08AM

I'm trying to speed up our automated releases but the biggest chunk is taken up by the powershell deployment with SQL Change Automation.

We have a project for our customer databases and loop through these databases running the powershell deployment script in our release. but this is gradually increasing in time as we add more scripts. We are aware we can rebase or consolidate the scripts but having to do this too regularly is a maintenance burden.

Has any encountered this or have an alternative solution?

Thanks,

Adam

Mike U · January 21, 2019 5:15PM

Are you just running the Deploy.ps1 included in the build output (which uses the Package deployment scripts method described here, and gets larger the more migration scripts you have), or are you using the SQL Change Automation PowerShell cmdlets (which use the Patch deployment scripts based method, and only need to run undeployed migrations)?

AdamBowler · January 21, 2019 5:35PM

We are using the DeployPackage.ps1 that's is in the build output. We haven' tried the SQL Change Automation PowerShell cmdlets I don't believe, is it significantly quicker?

Mike U · January 21, 2019 6:06PM

Whether it's significantly quicker or not depends on the size of your project (in terms of number of migration scripts and programmable objects), and how often you deploy. The patch-based method only deploys updates, so if you keep your target database reasonably up-to-date and you have a large number of scripts it should be significantly quicker.

AdamBowler · January 22, 2019 9:27AM

Mike U said:

Whether it's significantly quicker or not depends on the size of your project (in terms of number of migration scripts and programmable objects), and how often you deploy. The patch-based method only deploys updates, so if you keep your target database reasonably up-to-date and you have a large number of scripts it should be significantly quicker.

Thanks Mike, we'll give it a go

AdamBowler · January 22, 2019 11:11AM

Sorry Mike, it appears we are using the SQL Change Automation PowerShell cmdlets, I got confused. It does only run the undeployed migrations but it is still taking a long time to run. We currently have around 100 scripts, is this too large or would you not expect this to impact performance?

Mike U · January 22, 2019 11:56AM

Can you see from the logs which bit is taking a long time? You might need to turn up the log verbosity to figure that out (how you do that depends on exactly how you're running the PowerShell).

It will also depend, of course, on what's in the scripts to be deployed. If one of them does a massive index rebuild, fox example, it's bound by the performance of SQL Server itself.

AdamBowler · January 22, 2019 1:16PM

There appears to be 5 to 10 seconds delay between the Beginning Transaction log to it executing the first script which appears to be taking up the majority of the time.

Mike U · January 22, 2019 1:34PM

I suspect that's unavoidable overhead of setting up a transaction - but to be honest, 5 to 10 seconds doesn't seem that bad. Is the problem that you're deploying lots of databases, rather than the length of time for a single database? If so, have you tried running the deployments in parallel?

AdamBowler · January 22, 2019 4:14PM

We have but that then opens up more room for error on partitioning out the database which we'd like to avoid, is there any other deployment alternatives that may decrease the length of the deployment?

Mike U · January 22, 2019 4:26PM

Can you share the PowerShell script you're using, and the log output? If you're not happy sharing it here on the forum, you could contact support instead (assuming you have an active support contract)

AdamBowler · January 24, 2019 2:28PM

$customers = Invoke-Sqlcmd -Query $databaseListQuery -ServerInstance $DatabaseServerName -Database $database
Write-host "customercont=" + ($customers.length)
write-host $customers
$i = 1

foreach ($customer in $customers)
{
        Write-Host "Executing script for $($customer.dbname) ($i of $($customers.length))"
        write-host "dbname=$($customer.dbname)"

        & { $DatabaseServer=$($customer.hostname); $DatabaseName = $($customer.dbname); $ReleaseVersion = $ReleaseVersion; $UseWindowsAuth = $false; $DatabaseUserName = $($customer.dbusername); $DatabasePassword = $DatabasePassword; $ForceDeployWithoutBaseline = $ForceDeployWithoutBaseline; & $PathToDatabaseScript}

    $i++
}

AdamBowler · January 24, 2019 2:38PM

Executing script for databaseName (1 of x)
dbname=databaseName
If you require that all SqlCmd variable values be passed in explicitly, specify UseSqlCmdVariableDefaults=False.
Using SQL Server Authentication
Using default value for DefaultFilePrefix variable: ReadyRollCustomer
Using default value for DefaultDataPath variable: S:\Databases\
Using default value for DefaultLogPath variable: S:\Databases\
Using default value for DefaultBackupPath variable: S:\Backups\
Starting 'databaseName' Database Deployment to 'server ip'
Sqlcmd.exe -b -S "serverIp" -v DatabaseName="databaseName" ReleaseVersion="version" DeployPath="S:\Release Supporting Functions\RrDatabaseScripts\solution name\version\projectName\" ForceDeployWithoutBaseline="True" DefaultFilePrefix="ReadyRollCustomer" DefaultDataPath="S:\Databases\" DefaultLogPath="S:\Databases\" DefaultBackupPath="S:\Backups\" -d "databaseName" -i "S:\Release Supporting Functions\RrDatabaseScripts\solution name\version\ReadyRollCustomer\ReadyRollCustomer_Package.sql" -U "username"
The database already exists. An incremental deployment will be performed.
----- executing pre-deployment script "Pre-Deployment\01_Create_Database.sql" -----
# Beginning transaction

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {5d53085d-0483-4717-bca2-3f1b5dd7b1dd} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {5d53085d-0483-4717-bca2-3f1b5dd7b1dd} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {405ab026-2d9f-404d-a52a-e01efd8863c3} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {405ab026-2d9f-404d-a52a-e01efd8863c3} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {e78b1b52-ed7c-4eef-9983-e95f0cb20be5} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {e78b1b52-ed7c-4eef-9983-e95f0cb20be5} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {2de357b0-23da-487c-9ec8-e4a8016feaa0} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {2de357b0-23da-487c-9ec8-e4a8016feaa0} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {773ca2a1-2d39-41c1-9e79-25d527c9e308} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {773ca2a1-2d39-41c1-9e79-25d527c9e308} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {cce6ade6-1600-4b65-9f1c-64af76b484c5} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {cce6ade6-1600-4b65-9f1c-64af76b484c5} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {72544fa5-1d47-4613-98ab-4886c9473099} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {72544fa5-1d47-4613-98ab-4886c9473099} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {d1e08785-05ce-4b83-94ee-9ab72b9fb4e3} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {d1e08785-05ce-4b83-94ee-9ab72b9fb4e3} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {c6994e6e-117c-445c-81a5-55d90554048a} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {c6994e6e-117c-445c-81a5-55d90554048a} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {851bda30-3808-4736-bb73-fe8ab396b1b9} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {851bda30-3808-4736-bb73-fe8ab396b1b9} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {4d4f4a23-0da8-4c91-b23c-c53369170629} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {4d4f4a23-0da8-4c91-b23c-c53369170629} *****

***** EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {5e64a6f2-f49d-400c-a53d-50a503943a34} *****
***** FINISHED EXECUTING MIGRATION "Migrations\1.1.0-Changes\file_name.sql", ID: {5e64a6f2-f49d-400c-a53d-50a503943a34} *****

# Committing transaction
----- executing post-deployment script "Post-Deployment\01_Finalize_Deployment.sql" -----
Deployment completed successfully.
Skipping schema snapshot deployment as a snapshot file could not be found. As a result, preview/drift reports will be unavailable for the next deployment. To enable schema snapshot creation, specify the ShadowServer property in your build configuration https://www.red-gate.com/sca/continuous-integration

Mike U · January 31, 2019 4:21PM

From that log I can see that you're using the package deployment script method, which contains every migration you've ever written, and has to check each against the migration log at deployment time to decide which ones to execute. You're not actually using the SQL Change Automation PowerShell cmdlets there.

I would suggest using the SQL Change Automation PowerShell cmdlets (https://documentation.red-gate.com/sca3/automating-database-changes). You'd use the build/package cmdlets to create a single deployment package, and then use the release cmdlets for each target database to create and execute a targeted deployment script for each one that will only contain the undeployed migrations for that database.

Powershell Deployment speed

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk