What are the challenges you face when working across database platforms? Take the survey
Options

Powershell Deployment speed

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

Answers

  • Options
    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)?
    Development Lead
    Redgate Software
  • Options
    AdamBowlerAdamBowler Posts: 8 New member
    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?

  • Options
    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.
    Development Lead
    Redgate Software
  • Options
    AdamBowlerAdamBowler Posts: 8 New member
    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
  • Options
    AdamBowlerAdamBowler Posts: 8 New member
    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?
  • Options
    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.
    Development Lead
    Redgate Software
  • Options
    AdamBowlerAdamBowler Posts: 8 New member
    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.
  • Options
    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?
    Development Lead
    Redgate Software
  • Options
    AdamBowlerAdamBowler Posts: 8 New member
    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?
  • Options
    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)
    Development Lead
    Redgate Software
  • Options
    AdamBowlerAdamBowler Posts: 8 New member
    $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++
    }

  • Options
    AdamBowlerAdamBowler Posts: 8 New member
    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
  • Options
    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.
    Development Lead
    Redgate Software
Sign In or Register to comment.