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.
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
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:\Documents\git\SimpleTalkDemo\SimpleTalkDB\SimpleTalkDB.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:
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.