A Quick SQL Change Automation Build

I was going through a few of the Redgate products with a customer recently and they wanted to perform some validation of the build and release process with SQL Change Automation without setting up Azure DevOps or Jenkins or any server. Not a  bad idea, and a good way to actually learn how to do things.

Working with PoSh is a little funny, since many of the Redgate cmdlets require objects, not strings, so this is a quick post on how to get a build working and output a nuget package with your database code.

Prerequisites

The things you need to do before we get started:

• Have a SQL Server instance you are a sysadmin of (or at lease create/own dbs)
• Install SQL Change Automation
• Set up a SQL Change Automation Project
• Create a folder for storing build artifacts

The Script

I’m going to show the script first, and then I’ll describe how a few things work, since I expect some people want to just get a build working.

# Build script for SCA projects
param( $OverrideVersion="3.2")
# Instance variables
#    BuildInstance - SQL Server instance name for building 
$BuildInstance = "Aristotle"
# Database Variables
#    BuildDB - existing database used for build connection. Won't be altered
$BuildDB = "builddb"
# Package Variables
#    PackageID - Set the nuget package name to be used
#    PackageVersion - suffix on nuget package id.
$PackageID = "SimpleTalkDB"
$PackageVersion = $OverrideVersion
# Path variables
#   ProjectFile - full local path to the SCA project file (.sqlproj)
#   BuildArtifactPath - Path where the Nuget package is stored
$ProjectFile = "E:DocumentsgitSimpleTalkDemoSimpleTalkDBSimpleTalkDB.sqlproj"
$BuildArtifactPath = "E:buildartifacts"
# Debug
# Use Continue to get more output
$DebugPreference = "SilentlyContinue"
# Setup database connections using variables from above to the build instance
$BuildConnection = New-DatabaseConnection -ServerInstance $BuildInstance -Database $BuildDB
# Build the database with a validate
$ValidProject = Invoke-DatabaseBuild $ProjectFile -TemporaryDatabaseServer $BuildConnection 
# Get the artifact and write to disk. Note the name comes from the package vars above.
$buildArtifact = New-DatabaseBuildArtifact $ValidProject -packageId $PackageID -PackageVersion $Version
Export-DatabaseBuildArtifact $buildArtifact -Path $BuildArtifactPath

This is a basic script that validates a SQL Change Automation project and packages up a particular version of your project.

The way this works is as follows. First variables. For the build I need to change these:

• BuildInstance – where will I run the build
• BuildDB – I just need an existing database for the build. We actually won’t use this.
• PackageID – Name for the nuget package
• PackageVersion – What version. This can be passed into the script as a parameter
• ProjectPath – Where is the sqlproj file from SQL Change Automation
• BuildArtifactPath – Where am I storing the artifacts.

The flow of the script is:

• Set variables
• Create a connection to a SQL Server (New-DatabaseConnection)
• Build, which is to perform a project validation on a SQL Server (Invoke-DatabaseBuild)
• Create the artifact in memory (New-DatabaseBuildArtifact)
• Write the nuget package to disk (Export-DatabaseBuildArtifact)

From here, I’d use the Nuget package as the start of a release process, another post for another day.

I can override the package version with a parameter, so I can enter this on the cmd line:

.builddb.ps1 3.3

That gives me a build that works on my system.

In my folder for BuildArtifacts, I see the new package.

Give it a try, and let me know if this works for you.