Jenkins Multi-Branch Pipeline Builds Using Docker Containers and SQL Server

In this post I am going to demonstrate how to use one of Jenkins more powerful features ; its ability to create multi-branch build pipelines.

Source Code Control and Branching 101

The very first step in a continuous integration / delivery journey is to get a projects code under source code control, followed by coming up with a branching strategy. A ‘Branch’ represents a strand of lineage for the code base in a particular source code control repository. The source code control repository will always contain a master branch. However, as most development teams will invariably consist of more than one developer, working off one branch is unpractical, hence the need for a branching strategy.

This is where Jenkins multi-branch pipeline feature comes into play, and this is how to create one . . .

Creating A Multi-branch Pipeline

For guidance on how to install and configure Jenkins, Docker and the SQL Server Dacfx framework refer to my previous CI/CD blog post . Without further ado, here are the steps for creating the multi-branch build pipeline:

1. Login to Jenkins, the default URL for accessing this is http://localhost:8080, on the main screen hit “New Item” in the top left hand corner.
2. Enter a name for your pipeline, I have used “Multibranch Pipeline” in this example, select “Multibranch pipeline” as the type of pipeline you wish to create and then hit OK.
3. Add the source of the of repository, GitHub in this particular example:
4. Now add the credentials to the GitHub repository:
5. Enter your GitHub credentials:
6. Now select the credentials added in the previous step:
7. Enter the owner of the repository, select the repository to be used by the pipeline from the drop down list, hit Apply followed by Save:
8. If there is a requirement for Jenkins to periodically scan the repository and create a new pipeline(s) if it finds new branch(es) automatically,  check the tick box under “Scan Repository Triggers” and set the scan polling interval as appropriate:
9. Jenkins will now present a screen which has no build pipelines:
10. To instigate Jenkins into creating a build pipeline for each branch, hit “Scan Repository Now” on the left of the screen:
11. Once Jenkins has scanned the repository to determine how many branches it contains and then built a pipeline for each one, this is what we should see (note that the repository contained the master and a ‘HotFix’ branch to begin with):

Build Pipelines As Code

The build pipeline in this example contained in the jenkinsfile is going carries out the following tasks:

One of the major strengths of Jenkins is that it allows build pipelines to be specified as code. There are two flavours of syntax, there is what is called the ‘Declarative’ pipeline syntax as illustrated by this example from the Jenkins site:

pipeline {
    agent any
    stages {
        stage('Example Build') {
            steps {
                echo 'Hello World'
            }
        }
        stage('Example Deploy') {
            when {
                expression { BRANCH_NAME ==~ /(production|staging)/ }
                anyOf {
                    environment name: 'DEPLOY_TO', value: 'production'
                    environment name: 'DEPLOY_TO', value: 'staging'
                }
            }
            steps {
                echo 'Deploying'
            }
        }
    }
}

The second flavour of build pipeline as code is the the older “Groovy script” syntax, which is what I have used to date. Microsoft VSTS will have the ability for build pipelines to be specified declaratively in Q4 of 2017 through YAML, as per this link. However, we have a small hurdle to overcome; if we want to spin up one container per branch, assuming that each container will run on the same host, each SQL Server container has to have a unique name and port 1433 needs to map to a port that is not in use on the host. Using some clever groovy code, this challenge can easily be overcome :-), I will first present the code prior to explaining how it works:

def BranchToPort(String branchName) {
    def BranchPortMap = [
        [branch: 'master'   , port: 15565],
        [branch: 'Release'  , port: 15566],
        [branch: 'Feature'  , port: 15567],
        [branch: 'Prototype', port: 15568],
        [branch: 'HotFix'   , port: 15569]
    ]
    BranchPortMap.find { it['branch'] ==  branchName }['port']
}

def StartContainer() {
    bat "docker run -e \"ACCEPT_EULA=Y\" -e \"SA_PASSWORD=P@ssword1\" --name SQLLinux${env.BRANCH_NAME} -d -i -p ${BranchToPort(env.BRANCH_NAME)}:1433 microsoft/mssql-server-linux"
}

def DeployDacpac() {
    def SqlPackage = "C:\\Program Files\\Microsoft SQL Server\\140\\DAC\\bin\\sqlpackage.exe"
    def SourceFile = "SelfBuildPipeline\\bin\\Release\\SelfBuildPipeline.dacpac"
    def ConnString = "server=localhost,${BranchToPort(env.BRANCH_NAME)};database=SsdtDevOpsDemo;user id=sa;password=P@ssword1"

    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"
        stash includes: 'SelfBuildPipeline\\bin\\Release\\SelfBuildPipeline.dacpac', name: 'theDacpac'
    }
    stage('start container') {
        StartContainer()
    }
    stage('deploy dacpac') {
        try {
            DeployDacpac()
        }
        catch (error) {
            throw error
        }
        finally {
            bat "docker rm -f SQLLinux${env.BRANCH_NAME}"
        }
    }
}

The magic is in the BranchToPort function and the use of ${env.BRANCH_NAME}. Our pipeline assumes that there is a simple branching scheme using the following branches:

• master (of course !!!)
• Release
• Feature
• Prototype
• Hotfix

On lines 13:

bat "docker run -e \"ACCEPT_EULA=Y\" -e \"SA_PASSWORD=P@ssword1\" --name SQLLinux${env.BRANCH_NAME} -d -i -p ${BranchToPort(env.BRANCH_NAME)}:1433 microsoft/mssql-server-linux"

and 19:

bat "\"${SqlPackage}\" /Action:Publish /SourceFile:\"${SourceFile}\" /TargetConnectionString:\"${ConnString}\" /p:ExcludeObjectType=Logins"

the branch is passed into this function via ${env.BRANCH_NAME}, this results in the appropriate port number being used when the container is started (line 13) and when the connect string is created for the container (line 19), ${env.BRANCH_NAME} is also used on line 13 to specify the name of the container being started.

In this example I have used a branching scheme with five branches, however readers of this post are welcome to fork my github repo and modify the contents of BranchPortMap as they see fit. The only caveat to watch out for is that the code assumes that branch names cannot contain spaces, otherwise line 13 in the script which uses the branch name as a suffix for the container name will fail.

To Wrap Things Up, What We Have Covered

• How to create a multi-branch build pipeline with Jenkins
• A brief overview of build pipeline as code
• How to develop an extensible build pipeline using docker containers and SQL Server.