Creating A Docker Containerised Environment For SQL Server and Continuous Integration

This post covers building a simple continuous integration environment using Jenkins and SQL Server data tools which is fully containerised. There are two github repositories associated with this post, the first contains the files for building the environment and the second; the files for the SQL Server data tools visual studio project which is the ‘Thing’ that will be built:

• https://github.com/chrisadkin/SsdtJenkinsCiInDocker
  
• https://github.com/chrisadkin/SsdtDevOpsDemo

The docker image for the Jenkins SSDT build environment can be found on Dockerhub here.

For readers who are up to speed with working with Docker section 2 of this blog post can be skipped.

1. What We Are Going To Do

The build phase will involve checking a visual studio SQL Server project out of a local git repository and turning it into a DACPAC via msbuild, to then be deployed to a SQL Server database via sqlpackage.

2. What Do We Need ?

In order to carry out this exercise we require the following:

• A server / laptop or virtual machine with Windows 10 or Windows server 2016 installed.
• Hardware virtualization enabled for the host operating system:

• The hyper-v and containers roles / features enabled:
  
• Docker Community Edition, this can be downloaded for Windows from this link.
• Once installed the Docker engine needs to be configured to run windows container images. Right click on the Docker “MobyDock” icon in the speed tray on the Windows desktop, if the Docker engine is configured to run Windows container images the option to switch to Linux container images should be present, otherwise select “Switch to Windows container images…”:

• Open up a web browser, enter the following URL: https://github.com/chrisadkin/SsdtJenkinsCiInDocker, download and extract the master.zip file on the machine that the CI environment will be deployed to:

3. Building The Image For The Jenkins CI Container

First of all the jenkins ci container requires an image, this Dockerfile will be used for this purpose:

# escape=`
FROM microsoft/windowsservercore

SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop';"]

RUN mkdir \jenkins 
RUN powershell -Command `
 wget -Uri 'https://updates.jenkins-ci.org/download/war/2.0/jenkins.war' -UseBasicParsing -OutFile '/jenkins/jenkins.war'

RUN powershell -Command ` 
 Set-ExecutionPolicy Bypass; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))

RUN Install-PackageProvider -Name chocolatey -RequiredVersion 2.8.5.130 -Force

RUN choco install netfx-4.5.2-devpack -y --force
 
RUN powershell -Command ` 
 wget 'http://javadl.oracle.com/webapps/download/AutoDL?BundleId=210185' -Outfile 'C:\jreinstaller.exe' ; `
 Start-Process -filepath C:\jreinstaller.exe -passthru -wait -argumentlist "/s,INSTALLDIR=c:\Java\jre1.8.0_91" ; `
 del C:\jreinstaller.exe

RUN powershell -Command `
 Install-Package nuget.commandline -RequiredVersion 3.5.0 -Force
 
RUN C:\Chocolatey\bin\nuget install Microsoft.Data.Tools.Msbuild -Version 10.0.61026

RUN choco install microsoft-build-tools -version 14.0.25420.1 -y --force
 
RUN choco install git -y

ENV HOME /jenkins 
ENV JENKINS_VERSION 2.0 
ENV JAVA_HOME c:\\Java\\jre1.8.0_91

EXPOSE 8080 
EXPOSE 50000

RUN mkdir C:\Projects

CMD c:\\Java\\jre1.8.0_91\\bin\\java -jar C:\\jenkins\\jenkins.war

To create the image for the Jenkins container, simply run the following command in the directory containing the Dockerfile:

docker image build --tag chrisadkin/jenkins-ssdt-master:v1 .

4. A Note On Structuring Dockerfiles

When building an image Docker leverages a caching mechanism to avoid performing steps that have already been carried out previously, this is summarised in the Docker documentation as follows:

During the process of building an image Docker will step through the instructions in your Dockerfile executing each in the order specified. As each instruction is examined Docker will look for an existing image in its cache that it can reuse, rather than creating a new (duplicate) image.

If for any reason the contents of a Dockerfile need to be changed, any steps that are cached from the point in the file the change was made down, will have their cache entries invalidated. Docker ‘Caches’ a step by creating an intermediate container image for that step. It is therefore a good practice to place the expensive steps, in terms of the time it takes them to complete, towards the top of the Dockerfile and the less expensive steps towards the bottom.

5. Leveraging Docker Compose

Another good practice is to make containers as single minded as possible, this is why we will use two containers, one for the actual build and one for the SQL Server instance to deploy to. Docker compose, a tool used for starting up containers as part of a multi-container application will be used for this purpose.

By default, when docker-compose up is issued, Docker will look for a file called docker-compose.yml in the current working directory, this is the docker-compose.yml file that will be used in this particular instance:

################################################################################
#
# title : Docker Compose (version 3.3) YAML file for a self contained
#         continuous integration environment to build a DACPAC using
#         Jenkins and deploy it to SQL Server express running inside
#         its own container.
#
# author: Christopher J. Adkin (http://chrisadkin.io)
#
# url   : https://github.com/chrisadkin/SsdtJenkinsCiInDocker 
#
# description: Pulls (1) image, builds (1) image, and starts two containers
#
# - jenkins-master
#   This is the Jenkins CI engine running inside a windows
#   2016 server core container with GIT, msbuild and ssdt
#   tools installed
#
# - mssql-win-express
#   SQL Server express (windows image) 
#
# to run: docker-compose
#
################################################################################
version: "3.3"
services:
    jenkins:
        build: .
        image: chrisadkin/jenkins-ssdt-master:v1
        container_name: jenkins-master
        ports: ["8080:8080"]
        links:
            - "sqlserver:database"
 
    sqlserver:
        image: microsoft/mssql-server-windows-express
        container_name: mssql-win-express
        ports: ["1433:1433"]
        environment:
            sa_password: P@ssW0rd!
            accept_eula: Y
        networks:
            default:
                aliases:
                    - db
 
networks:
    default:
        external:
        name: nat

To start up our continuous integration environment; open a dos or powershell session and change directory to the one holding the Dockerfile and docker-compose.yml files and issue the following command:

docker-compose up

From the output of docker-compose, make a note of the initial administration password for Jenkins:

6. Configuring Jenkins

Next, open a session to the Jenkins console, first we will have to find the ip address for the container, to do this issue:

docker inspect jenkins-master

Running this command should result in output which looks like:

If there is no ip address present in the Networks section of the Docker inspect output, then it is likely that the jenkins-master container has fallen over for some reason, this can be investigated by looking at the output of docker logs jenkins-master.

Take the ip address, append :8080 to it (this will result in 172.24.84.246:8080 in this case) and enter this into the URL bar of a browser, this should make the Jenkins unlock screen appear:

Enter the password from the output of docker-compose up and hit continue.

Next, hit “Select plugins to install”:

Select MSbuild Plugin (from Build Tools (2/4)). Once the plugins have installed, create an admin user:

Hit “Save and Finish” then go to “Start using Jenkins” -> “Manage Jenkins” -> “Global Tool Configuration”. Configure msbuild by entering the text highlighted in the red boxes, note that the warning message can be safely ignored:

7. Upgrade Jenkins (If necessary)

If a message is displayed on the configure Jenkins screen stating that an upgrade is available, vis:

Click on “Or Upgrade Automatically”, once the upgrade is complete, the environment will need to be re-started by issuing:

docker-compose restart

in the directory containing the docker-compose.yml file. docker inspect jenkins-master will also have to be issued again in order to get the new ip address for the Jenkins web user interface. To pre-empt the question “Why not give the Jenkins container a static IP address”, the following is what I came across when looking into this:

8. Obtaining Something To Build

To do this, we will run a Dos command shell session on the jenkins-master container and clone a repo from GitHub as follows:

docker exec -it jenkins-master cmd

cd :c\Projects

git clone https://github.com/chrisadkin/SsdtDevOpsDemo.git 

cd SsdtDevOpsDemo

git remote rm origin

exit

9. Creating The Build Pipeline

On the main Jenkins screen go to “New Item” -> “Enter an item name” -> highlight ‘Pipeline’ -> and then click on ‘OK’. On the advanced Options tab, scroll down to the ‘Pipeline’ section:

Paste the following groovy script excerpt into the pipeline script box (highlighted above):

def DeployDacpac() {
    def SqlPackage = "C:\\Microsoft.Data.Tools.Msbuild.10.0.61026\\lib\\net40\\sqlpackage.exe"
    def SourceFile = "SsdtDevOpsDemo\\bin\\Release\\SsdtDevOpsDemo.dacpac"
    def ConnString = "server=db,1433;database=SsdtDevOpsDemo;user id=sa;password=P@ssW0rd!"
    unstash 'theDacpac'
 
    bat "\"${SqlPackage}\" /Action:Publish /SourceFile:\"${SourceFile}\" /TargetConnectionString:\"${ConnString}\" /p:ExcludeObjectType=Logins"
}

node {
    stage('git checkout') {
        git 'https://github.com/chrisadkin/SelfBuildPipeline' 
    }
 
    stage('build dacpac') {
        bat "\"${tool name: 'Default', type: 'msbuild'}\" /p:Configuration=Release /p:SQLDBExtensionsRefPath=\"C:\\Microsoft.Data.Tools.Msbuild.10.0.61026\\lib\\net40\" /p:SqlServerRedistPath=\"C:\\Microsoft.Data.Tools.Msbuild.10.0.61026\\lib\\net40\""
        stash includes: 'SsdtDevOpsDemo\\bin\\Release\\SsdtDevOpsDemo.dacpac', name: 'theDacpac'
    }
 
    stage('deploy dacpac') {
        try {
            DeployDacpac()
        }
        catch (error) {
            throw error
        }
    }
}

Hit Apply and then Save, on the main screen for the pipeline job, I have called it “Ssdt DevOps Demo” in this particular case, hit “Build Now”, the following is what you should see when the job completes successfully:

What Next

The natural evolution of this little exercise to create an environment which includes Jenkins build slaves running inside containers, the creation and start-up of which is  orchestrated by docker-compose.