SQL Change Automation 4

Help for older versions available.

Troubleshooting Slow Build and Deployment Times

If your build or deployment process is taking longer than you would like, the first step is to diagnose how long each component in your build and deployment process is taking. 

To measure this, we recommend running your build and deployment procedures locally to measure timings. This has two big benefits:

  • It eliminates overhead from your pipeline and latency in communicating with your build/deployment agents. If your timing running the build and release components locally is significantly faster than the time is in a pipeline scenario, that gives you an important lead as to the cause of the latency.
  • SQL Change Automation's graphic CI/CD plugins combine some tasks for your convenience. You are able to measure parts of the build and release automation with more granularity when executing these tasks with PowerShell.

Installing and updating SQL Change Automation's PowerShell components

If you are not already set up to use SQL Change  Automation's PowerShell components locally, your first step is to install them

You may also wish to review the supported operating systems and PowerShell versions.

Example build script

This build script:

  • Validates the project
  • Creates a database build artifact
  • Exports the database build artifact
  • Uses simple PowerShell to display the elapsed time for each action

To run this script, review and edit the parameters to work with your project and local environment.

This example script builds a SQL Change Automation Project. Modify line 3 to reflect your project name and the folder structure of your project.

  • You may change this to a hardcoded path if you wish
  • If you are building a SQL Source Control project, you only need specify the folder name for the project (there is no .sqlproj file to reference)


build.ps1 Toggle source code

  1. param
  2. (
  3. [string]$project = (get-item $PSScriptRoot ).parent.FullName + '\LiveDemo\LiveDemo.sqlproj',
  4. [string]$packageVersion = '0.0.001',
  5. [string]$packageID = 'LD',
  6. [string]$buildArtifactPath = 'C:\DBAutomation\BuildArtifacts',
  7. [string]$buildSQLServer = '.'
  8. )
  9.  
  10. $errorActionPreference = "stop"
  11.  
  12.  
  13. # Invoke the build
  14. $StartTime = $(get-date)
  15. $validatedProject = $project | Invoke-DatabaseBuild -TemporaryDatabaseServer "Data Source =$buildSQLServer"
  16.  
  17. $elapsedTime = $(get-date) - $StartTime
  18. Write-Host "Project validated in $elapsedTime"
  19. $StartTime = $(get-date)
  20.  
  21. # Create a database build artifact
  22. # Using -Force only to support re-exporting an artifact in a testing workflow
  23. $buildArtifact = $validatedProject | New-DatabaseBuildArtifact -PackageId $packageID -PackageVersion $packageVersion -Verbose -Debug
  24.  
  25. $elapsedTime = $(get-date) - $StartTime
  26. Write-Host "Build artifact created in $elapsedTime"
  27.  
  28. # Export the build artifact
  29. $StartTime = $(get-date)
  30. $buildArtifact | Export-DatabaseBuildArtifact -Path "$buildArtifactPath" -Force
  31.  
  32. $elapsedTime = $(get-date) - $StartTime
  33. Write-Host "Build artifact exported in $elapsedTime"

Example deployment script

This deployment script is dependent upon the prior script to have successfully exported a NUGET package from the build. The script:

  • Creates a database release artifact
  • Exports the database release artifact 
  • Optionally deploys the database release artifact if you uncomment the code in lines 37-43
  • Uses simple PowerShell to display the elapsed time for each action

Modify the parameters to reflect the project structure as you did in the prior script.

If you would like to specify SQL Compare options, check out the documentation on the options and their aliases.


release.ps1 Toggle source code

  1. param
  2. (
  3. [string]$project = (get-item $PSScriptRoot ).parent.FullName + '\LiveDemo\LiveDemo.sqlproj',
  4. [string]$packageVersion = '0.0.001',
  5. [string]$packageID = 'LD',
  6. [string]$artifactPath = 'C:\DBAutomation\BuildArtifacts',
  7. [string]$releaseArtifactPath = 'C:\DBAutomation\ReleaseArtifacts',
  8. [string]$targetSQLServer = '.',
  9. [string]$targetDatabase = 'LiveDemoDeploy',
  10. [string]$sqlCompareOptions = ''
  11. )
  12.  
  13. $buildArtifact = "$artifactPath\$packageID.$packageVersion.nupkg"
  14.  
  15. # Connect to the database we plan to deploy to
  16. $targetConnection = New-DatabaseConnection -ServerInstance $targetSQLServer -Database $targetDatabase
  17.  
  18. # Create the release artifact
  19. $StartTime = $(get-date)
  20. $releaseArtifact = New-DatabaseReleaseArtifact -Source $buildArtifact -Target $targetConnection -SQLCompareOptions $sqlCompareOptions -Verbose
  21. $elapsedTime = $(get-date) - $StartTime
  22. Write-Host "Release artifact created in $elapsedTime"
  23.  
  24.  
  25. # Export release artifact to the file system
  26. # Use -Force for testing workflows only
  27. $StartTime = $(get-date)
  28. $releaseArtifact | Export-DatabaseReleaseArtifact -Path "$releaseArtifactPath\$targetDatabase.$packageID.$packageVersion\" -Force -Verbose
  29.  
  30. $elapsedTime = $(get-date) - $StartTime
  31. Write-Host "Release artifact exported in $elapsedTime"
  32.  
  33. # One might have a review step in the pipeline here and then chose to import the release artifact like this and deploy it
  34. # Import-DatabaseReleaseArtifact -Path "$artifactPath\$targetDatabase\$packageID.$packageVersion\" | Use-DatabaseReleaseArtifact -DeployTo $targetConnection
  35.  
  36. # Uncomment if you wish to deploy
  37.  
  38. <#
  39. $elapsedTime = $(get-date) - $StartTime
  40. Use-DatabaseReleaseArtifact $releaseArtifact -DeployTo $targetConnection
  41.  
  42. $elapsedTime = $(get-date) - $StartTime
  43. Write-Host "Release artifact deployed in $elapsedTime"
  44. #>

More details on SQL Change Automation PowerShell components

We publish a reference on each PowerShell cmdlet.



Didn't find what you were looking for?