SQL Change Automation 4

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

param
(
      [string]$project = (get-item $PSScriptRoot ).parent.FullName + '\LiveDemo\LiveDemo.sqlproj',
      [string]$packageVersion = '0.0.001',
      [string]$packageID = 'LD',
      [string]$buildArtifactPath = 'C:\DBAutomation\BuildArtifacts',
      [string]$buildSQLServer  = '.'
)

$errorActionPreference = "stop"


# Invoke the build
$StartTime = $(get-date)
$validatedProject = $project | Invoke-DatabaseBuild -TemporaryDatabaseServer "Data Source =$buildSQLServer"

$elapsedTime = $(get-date) - $StartTime
Write-Host "Project validated in $elapsedTime"
$StartTime = $(get-date)

# Create a database build artifact
# Using -Force only to support re-exporting an artifact in a testing workflow
$buildArtifact = $validatedProject | New-DatabaseBuildArtifact -PackageId $packageID -PackageVersion $packageVersion -Verbose -Debug

$elapsedTime = $(get-date) - $StartTime
Write-Host "Build artifact created in $elapsedTime"

# Export the build artifact
$StartTime = $(get-date)
$buildArtifact | Export-DatabaseBuildArtifact -Path "$buildArtifactPath" -Force

$elapsedTime = $(get-date) - $StartTime
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

param
(
    [string]$project = (get-item $PSScriptRoot ).parent.FullName + '\LiveDemo\LiveDemo.sqlproj',
    [string]$packageVersion = '0.0.001',
    [string]$packageID = 'LD',
    [string]$artifactPath = 'C:\DBAutomation\BuildArtifacts',
    [string]$releaseArtifactPath = 'C:\DBAutomation\ReleaseArtifacts',
    [string]$targetSQLServer  = '.',
    [string]$targetDatabase = 'LiveDemoDeploy',
	[string]$sqlCompareOptions = ''
)

$buildArtifact = "$artifactPath\$packageID.$packageVersion.nupkg"

# Connect to the database we plan to deploy to 
$targetConnection = New-DatabaseConnection -ServerInstance $targetSQLServer -Database $targetDatabase

# Create the release artifact
$StartTime = $(get-date)
$releaseArtifact = New-DatabaseReleaseArtifact -Source $buildArtifact -Target $targetConnection -SQLCompareOptions $sqlCompareOptions -Verbose 
$elapsedTime = $(get-date) - $StartTime
Write-Host "Release artifact created in $elapsedTime"


# Export release artifact to the file system 
# Use -Force for testing workflows only
$StartTime = $(get-date)
$releaseArtifact | Export-DatabaseReleaseArtifact -Path "$releaseArtifactPath\$targetDatabase.$packageID.$packageVersion\" -Force -Verbose 

$elapsedTime = $(get-date) - $StartTime
Write-Host "Release artifact exported in $elapsedTime"

# One might have a review step in the pipeline here and then chose to import the release artifact like this and deploy it
# Import-DatabaseReleaseArtifact -Path "$artifactPath\$targetDatabase\$packageID.$packageVersion\" | Use-DatabaseReleaseArtifact -DeployTo $targetConnection

# Uncomment if you wish to deploy

<#
$elapsedTime = $(get-date) - $StartTime
Use-DatabaseReleaseArtifact $releaseArtifact -DeployTo $targetConnection

$elapsedTime = $(get-date) - $StartTime
Write-Host "Release artifact deployed in $elapsedTime"
#>

More details on SQL Change Automation PowerShell components

We publish a reference on each PowerShell cmdlet.

SQL Change Automation 4

Troubleshooting Slow Build and Deployment Times

Installing and updating SQL Change Automation's PowerShell components

Example build script

Example deployment script

More details on SQL Change Automation PowerShell components