Creating Continuous Integration Build Pipelines With Jenkins, Docker and SQL Server

In the world of continuous integration and delivery where we might want to perform numerous builds a day. Docker is ideally suited for spinning up environments and then tearing them down afterwards in use cases where we only need an environment (a container in this instance) for the duration of the build. This post will outline how to create a simple build pipeline using GIT, Jenkins, Docker and SQL Server for Linux.

What Our Build Pipeline Will Do

Our build pipeline will have the following stages:

1. It will check out a simple SSDT project from a local git repository
2. msbuild build the project into a dacpac, our deploy-able artefact
3. An instance SQL Server (Linux) running inside a container will be created
4. The dacpac will be deployed to the container
5. The container will be stopped and removed as part of a final cleanup stage

Configuring Windows 10 / Windows Server 2016

There are two flavours of containers in windows:

• Windows server containers
  The containers shares the kernel with host O/S
• Hyper-V containers
  The container runs inside a virtual machine, in this blog post, this is what will be used

First off, Hyper-V and Container roles / features need to be enabled on the respective operating systems:

Installing Docker

Download Docker for Windows from this link, once the msi file has downloaded, double clicking on it will launch the installation wizard which is easy to follow.

Configure Docker To Use Linux Containers

Right click on the docker icon in the task tray and make sure “Switch to Windows containers…” is visible, this means that docker is configured to run Linux containers:

Some Useful Docker Commands

When playing around with container, the following commands may come in useful:

docker ps -a
View all instantiated containers

docker logs
View the log for a specified container

docker start
Start a specified container

docker stop
Stop a specified container

docker rm
Remove a container, note that the container needs to be stopped first

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. Once Jenkins 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 SQL Server 2017 Data Tier Applications Framework

Download and install the framework (DacFx) from this link, this is required in order that a version of sqlpackage.exe is available which will work with SQL Server for Linux deployed to a container.

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

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.

Configuring Docker

Make sure that the docker daemon is running, the icon highlighted in the red box should be present in the task tray:

Next pull the SQL Server Linux docker container image from the docker public registry:

docker pull microsoft/mssql-server-linux

Finally, for the SQL Server Linux image to run inside a container, the docker daemon requires at least 3250MB of memory. Right clock on the docker icon in the task tray, select setting from the floating menu that appears, click on advanced and then set the memory slider to a value greater than or equal to 3250 MB:

Creating The Jenkins Build Pipeline

Fire up Jenkins from a browser, its default URL is http://localhost:8080, however this can be changed post install via a configuration file. On the home screen hit new item in the top left hand corner to make the following screen appear, from this screen give your build pipeline a name and then hit “Pipeline”:

Tick GitHub project box and enter the location of the local github repo in the Project URL box:

In the pipeline section select “Pipeline script” for the pipeline definition:

Enter the script below in the Script box, uncheck “Use Grooby Sandbox” hit apply and then save.

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') {
        bat "\"${tool name: 'Default', type: 'msbuild'}\" /p:Configuration=Release"
        stash includes: 'SsdtDevOpsDemo\\bin\\Release\\SsdtDevOpsDemo.dacpac', name: 'theDacpac'
    }

    stage('start container') {
        sh 'docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=P@ssword1" --name SQLLinuxLocal -d -i -p 15566:1433 microsoft/mssql-server-linux'
    }

    stage('deploy dacpac') {
        unstash 'theDacpac'
        bat "\"C:\\Program Files\\Microsoft SQL Server\\140\\DAC\\bin\\sqlpackage.exe\" /Action:Publish /SourceFile:\"SsdtDevOpsDemo\\bin\\Release\\SsdtDevOpsDemo.dacpac\" /TargetConnectionString:\"server=localhost,15566;database=SsdtDevOpsDemo;user id=sa;password=P@ssword1\" /p:ExcludeObjectType=Logins"
    }

    stage('run tests') {
        PowerShell('Start-Sleep -s 5')
    }

    stage('cleanup') {
        sh 'docker stop SQLLinuxLocal'
        sh 'docker rm SQLLinuxLocal'
    }
}

Now return to the main screen, select the name of the build pipeline and hit build now to trigger the build off:

Once the build has successfully completed, this is what you should see in the Jenkins console, each box representing a build stage (highlighted in the red box I have added) should be green:

If for any reason a build fails, click on the build number in the Build History pane and then console in order to view that particular builds build activity logging information.

Using The Docker Flow Plugin

A docker flow plugin exists which allows containers to be spun up in a much more elegant manner, here is an example of spinning up a container and running something inside it:

stage('start container') {
    docker.image('-e "ACCEPT_EULA=Y" -e "SA_PASSWORD=P@ssword1" --name SQLLinuxLocal -d -i -p 15565:1433 microsoft/mssql-server-linux').withRun() {
        unstash 'theDacpac'
        bat "\"C:\\Program Files\\Microsoft SQL Server\\140\\DAC\\bin\\sqlpackage.exe\" /Action:Publish /SourceFile:\"SsdtDevOpsDemo\\bin\\Release\\SsdtDevOpsDemo.dacpac\" /TargetConnectionString:\"server=localhost,15566;database=SsdtDevOpsDemo;user id=sa;password=P@ssword1\""
    }
}