My 2018 SQL Server, Docker and Jenkins Presentation In Blog Form, Part II

In the first post in this series I covered why you might want to use Jenkins as a CI engine and how to deploy to SQL Server running in a container using the ‘Sidecar’ pattern. So far so good, however the chances are that for any serious piece of development work there will be multiple developers working on the code base at any one time. If you have say; people working on fixes, new features, releasing code into production how do you manage this ? . . .

Branches Versus Feature Toggles

Whatever you elect to do there will always be a master branch, where you go from here depends on whether you favor branching or feature toggles. Wikipedia provides a nice definition of what a feature toggle is, thus:

A feature toggle^[1] (also feature switch, feature flag, feature flipper, conditional feature, etc.) is a technique in software development that attempts to provide an alternative to maintaining multiple source-code branches (known as feature branches), such that a feature can be tested even before it is completed and ready for release. Feature toggle is used to hide, enable or disable the feature during run time. For example, during the development process, a developer can enable the feature for testing and disable it for other users.^[2]

A branch is initially a clone of the master branch to begin with, developers work on the branch. Once the work on that branch is code complete and it has been tested to satisfaction, it is merged into the master. An overview of the branching and merging process is provided in the Git documentation here.

The continuous integration and delivery purist are not great fans of branches and prefer the ethos of integrating changes into one place to be rigidly adhered to, ergo one code branch only. However, in practice you will find that most projects have to come up with some sane branching strategy. The subject of branching is a topic in its own right, suffice it to say there is an overhead in applying changes across multiple branches and overheads involved in merging into the master branch. Therefore, there needs to be some governance and rigor applied around the number of branches in the source code repository.

Introducing Jenkins Multi Branch Pipelines

Jenkins allow us to create what is called a “Multibranch pipeline”:

This means that every time a new branch is created in the repo, we can rescan the repo for new branches, if Jenkins finds a new branch, it will create a new pipeline:

Again, containers are a great fit for solving the problem of how to spin up an instance to deploy to for each branch. However, there are two challenges we need to overcome, when using the same container host, i.e. the server our containers are running on, we need to:

1. Ensure that each container has a unique name
2. Ensure that each container uses a unique external port on the host

The first problem can be solved by using the name of the branch as part of the container name, done by using ${BRANCH_NAME} as per the code excerpt below:

def StartContainer() {
    PORT_NUMBER = GetNextFreePort()
    bat "docker run -e \"ACCEPT_EULA=Y\" -e \"SA_PASSWORD=P@ssword1\" --name ${CONTAINER_NAME} -d -i -p ${PORT_NUMBER}:1433 microsoft/mssql-server-linux:2017-GA"
    powershell "While (\$((docker logs ${CONTAINER_NAME} | select-string ready | select-string client).Length) -eq 0) { Start-Sleep -s 1 }"
}
.
.
.
pipeline {
    agent any

    environment {
        PORT_NUMBER = 0
        SCM_PROJECT = GetScmProjectName()
        CONTAINER_NAME = "SQLLinux${BRANCH_NAME}"
    }

We can obtain the next free port via this powershell call out:

def GetNextFreePort() {
    def port = powershell(returnStdout: true, script: '((Get-NetTCPConnection | Sort-Object -Property LocalPort | Select-Object -Last 1).LocalPort) + 1')
    return port.trim()
}

However, if multiple branches are detected, there is the possibility that each branch may get the same port number, and the creation of our sidecar containers fails because a port on the host has already been allocated to another container. What we need is some means whereby each pipeline corresponding to a branch gets its own external port. Fortunately there is a really elegant solution to this, firstly we need to install the “Lockable resource” plugin:

The magic that ensures only one branch can obtain an available port number on the host and spin up a container can be found on lines 29 to 31 in the code excerpt below:

def GetNextFreePort() {
    def port = powershell(returnStdout: true, script: '((Get-NetTCPConnection | Sort-Object -Property LocalPort | Select-Object -Last 1).LocalPort) + 1')
    return port.trim()
}

def StartContainer() {
    PORT_NUMBER = GetNextFreePort()
    bat "docker run -e \"ACCEPT_EULA=Y\" -e \"SA_PASSWORD=P@ssword1\" --name ${CONTAINER_NAME} -d -i -p ${PORT_NUMBER}:1433 microsoft/mssql-server-linux:2017-GA"
    powershell "While (\$((docker logs ${CONTAINER_NAME} | select-string ready | select-string client).Length) -eq 0) { Start-Sleep -s 1 }"
}
.
.
.
pipeline {
    agent any

    environment {
        PORT_NUMBER = 0
        SCM_PROJECT = GetScmProjectName()
        CONTAINER_NAME = "SQLLinux${BRANCH_NAME}"
    }
.
.
.
    stage('start container') {
        steps {
            RemoveContainer()
            timeout(time: 20, unit: 'SECONDS') {
                lock ('create SQL Server container') {
                    StartContainer()
                }
            }
        }
    }

The repo containing the Jenkinsfile which implements this pipeline and its associated SQL Server data tools project can be found here. As before the pipeline is exactly the same in nature as that covered in the previous blog post:

The pipeline picks up the Jenkinsfile by being configured to point at the Repo containing it. When a branch is created, the entire contents of the master branch including the Jenkinsfile are copied to the new branch.

The Repo:

Pointing the multi-branch pipeline at the repo:

In the next post in this series I will build on this example by adding tSQLt to the pipeline .