The aim of this blog post is twofold, it is to explain how:
- A “Self building pipeline” for the deployment of a SQL Server Data Tools project can be implemented using open source tools
- A build pipeline can be augmented using PowerShell
What You Will Need
- Jenkins automation server
- cURL
- SQL Server 2016 (any edition will suffice)
- Visual Studio 2015 community edition
- A windows server, physical or virtual to install all of the above on, I will be using Windows Server 2012 R2 as the operating system
What Our End Goal Looks Like
The aim of all of this is to be able to open a SQL Server data tools projects in visual studio, make a change to it, commit the change to git, build the project into a dacpac file and then deploy this to SQL Server. However, we will require something to trigger Jenkins into life . . .
The ‘Magic’ That Triggers The “Self-Build”
Jenkins can be configured to listen for certain git events and when I say git, this can be a local git repository, github (cloud) or gitlab (hosted somewhere on premises). In the interests of keeping things simple so as to avoid the nuances of NAT’ed ip addresses or fire walls, a local git repository will be used in this example. The thing that triggers the event that causes Jenkins to perform a build is called a web hook. When the project is added to git, a .git directory should appear in the top level project directory:
#!/bin/sh curl http://localhost:8080/git/notifyCommit?url=file:///C:/Projects/SsdtDevOpsDemo
Observant readers will note that most development projects require more than one developer and therefore a central repository that is pushed would be more appropriate. In the interests of keeping this as simple as possible I have elected to use a local repository, if a remote centralised repository required, the post-update hook file should be modified and used.
Installing Jenkins
A link to the Jenkins installer can be found here (note that this link will trigger the download immediately). Select the option to “Install Recommended Plugins” when installing this and once it is installed select the option to create a windows service for Jenkins, otherwise Jenkins will have to be started every time it is required using the java runtime.
Installing The MSBuild Plugin
The MSBuild plugin needs to be installed separately from:
http://localhost:8080/pluginManager/available
or by clicking through Jenkins -> Manage Jenkins -> Manage Plugins -> Available. We need to tell Jenkins where it can find MSBuild.exe, this is done on http://localhost:8080/configureTools/ (Jenkins -> Manage Jenkins -> Global Tool Configuration). You can find out where MSBuild is installed by opening the “Developer Command Prompt for Visual Studio” and typing:
C:\Program Files (x86)\Microsoft Visual Studio 14.0>where msbuild
C:\Program Files (x86)\MSBuild\14.0\Bin\MSBuild.exe
C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe
Use the first of these, as this is the one installed by Visual Studio 2015. Git should already be installed and in the path, so there was no need to configure this separately in Jenkins. At the time of writing this post, avoid the temptation to use Visual Studio 2017 as this contains a bug which results in the git web hook not being fired correctly.
$ git --version
git version 2.11.0.windows.3
Allow Jenkins To Connect To SQL Server
The Jenkins service will run as “Local System” on Windows by default. In order to allow this account – which will authenticate to SQL Server as the machine account – to deploy the database, I made “Local System” an sa of the SQL Server:
EXEC sp_addsrvrolemember 'NT AUTHORITY\SYSTEM', 'sysadmin';
However, there is absolutely no reason why you cannot create a service style account (set password to not expire and no change password on first login) for Jenkins to run under. In addition to this, this account will need to be given the necessary permissions to create database objects in the target database used by sqlpackage.exe.
Download The Example SQL Server Data Tools Project
We need a project to build in the first place, for demonstration purposes there is already one in github we can use:
C:\Projects>git clone https://github.com/chrisadkin/SsdtDevOpsDemo.git
Cloning into 'SsdtDevOpsDemo'...
remote: Counting objects: 26, done.
remote: Compressing objects: 100% (20/20), done.
remote: Total 26 (delta 5), reused 26 (delta 5), pack-reused 0
Unpacking objects: 100% (26/26), done.
C:\Projects>tree
Folder PATH listing
Volume serial number is 000000B7 806E:E890
C:.
└───SsdtDevOpsDemo
└───SsdrDevOpsDemo
└───dbo
└───Tables
C:\Projects>
Now remove the reference to the GitHub remote with the following command:
C:\Projects\SsdtDevOpsDemo>git remote rm origin
From here on, everything is local.
Creating The Jenkins Build Pipeline
The first step is to create the Jenkins job that will build our project into a dacpac, and deploy it to a local SQL Server.
The job definition is contained in the following groovy script excerpt, this consists of three fairly self-explanatory stages:
- The first stage
git checkoutchecks out our master branch from the local (indicated by thefile:\\prefix) repo. - The second stage calls the
MSBuildtool we defined earlier, taking advantage of the fact that our project is very simple to provide very few parameters on the command line. - The third calls out to the shell to call
sqlpackage.exe, again with very few parameters. - This last stage is the one that requires whatever account Jenkins is running under to be able to authenticate to the SQL Server (though there are alternatives involving storing credentials in Jenkins).
node {
stage('git checkout') {
git 'file:///C:/Projects/SsdtDevOpsDemo'
}
stage('Build Dacpac from SQLProj') {
bat "\"${tool name: 'Default', type: 'msbuild'}\" /p:Configuration=Release"
stash includes: 'SsdtDevOpsDemo\\bin\\Release\\SsdtDevOpsDemo.dacpac', name: 'theDacpac'
}
stage('Deploy Dacpac to SQL Server') {
unstash 'theDacpac'
bat "\"C:\\Program Files (x86)\\Microsoft SQL Server\\130\\DAC\\bin\\sqlpackage.exe\" /Action:Publish /SourceFile:\"SsdtDevOpsDemo\\bin\\Release\\SsdtDevOpsDemo.dacpac\" /TargetServerName:(local) /TargetDatabaseName:Chinook"
}
}
In this example we are entering the groovy script directly into the pipeline job editor:
In addition to defining the pipeline build node and its associated stages, the pipeline job needs to be pointed at the local GIT repository:
Configure Jenkins To Respond To The GIT Web Hook
The job needs to be set to “Poll SCM”, leave the schedule empty (ignore the warning), this isn’t required to test the build, but will be required later on to trigger the build on a commit to our GIT repository:
Testing the Build
The build can now be triggered from the Jenkins dashboard, if you navigate from
job -> build history -> build number -> Console Output, the detailed log of the build activity can be viewed. For a more high level view of the job status simply highlight the job, ideally each stage should be green as per builds 10 and 11 🙂 and not red as is the case for builds 8 and 9 😦
def PowerShell(psCmd) {
bat "powershell.exe -NonInteractive -ExecutionPolicy Bypass -Command \"\$ErrorActionPreference='Stop';$psCmd;EXIT \$global:LastExitCode\""
}
node {
stage('git checkout') {
git 'file:///C:/Projects/SsdtDevOpsDemo'
}
stage('Build Dacpac from SQLProj') {
bat "\"${tool name: 'Default', type: 'msbuild'}\" /p:Configuration=Release"
stash includes: 'SsdtDevOpsDemo\\bin\\Release\\SsdtDevOpsDemo.dacpac', name: 'theDacpac'
}
stage('Refresh test from production') {
PowerShell(". 'C:\\scripts\\Refresh-Test-Checkpoint-2016.ps1'")
}
stage('Deploy Dacpac to SQL Server') {
unstash 'theDacpac'
bat "\"C:\\Program Files (x86)\\Microsoft SQL Server\\130\\DAC\\bin\\sqlpackage.exe\" /Action:Publish /SourceFile:\"SsdtDevOpsDemo\\bin\\Release\\SsdtDevOpsDemo.dacpac\" /TargetServerName:(local) /TargetDatabaseName:SsdtDevOpsDemo"
}
}
What Next ?
You might like to look at adapting this example to:
- use gitlab or github
- add unit testing via Pester and/or tSQLt
- experiment with pulling the pipeline script from GIT gist
And A Very Special Thanks To . . .
The multi talented Gavin Campbell
(@Gav_the_unready on twitter) who along with his innate ability to sniff out real ale pubs in central London like a blood hound, has been incredibly generous with his time when helping me to get my self building pipeline up and running.
Fear Not Hardcore SQL Server Internals And Performance Junkies !!!
If you are disappointed at the fact that my blogging hiatus was not ended by a deep blog post on SQL Server performance and internals, I have such a post baking in the oven which will satiate your hunger (and then some) for such material.
chrisadkin.io 
Testing The GIT Web Hook
To do this simply open up the SSDT project, make a simple change to the project, for example, change the width of the Name column in the artist table and then commit the change to the local GIT repository:
Adding PowerShell To The Mix
In the build history, there are four stages present and not three, an extra stage has been added to the pipeline, in this instance its a call to a script which will cause our storage to refresh the test database from a production one. However, this opens the door to the integration of anything which can be invoked through PowerShell into the build pipeline such as the PowerShell based unit test framework Pester. This is how the groovy script has been modified to invoke the PowerShell script Refresh-Test-Checkpoint-2016.ps1: