Good Practices For Spinning Up SQL Server Containers In Jenkins Build Pipeline As Code

Spinning up containers should generally be very rapid, however some container images, in particular those for SQL Server may take of the order of seconds to spin up. Take the following Jenkins pipeline groovy script excerpt as an example:

PowerShell "If (\$((docker ps -a --filter \"name=SQLLinux${env.BRANCH_NAME}\").Length) -eq 2) { docker rm -f SQLLinux${env.BRANCH_NAME} }"
docker.image('microsoft/mssql-server-linux').run("-e ACCEPT_EULA=Y -e SA_PASSWORD=P@ssword1 --name SQLLinux${env.BRANCH_NAME} -d -i -p ${BranchToPort(env.BRANCH_NAME)}:1433")
bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -Q \"EXEC sp_configure 'show advanced option', '1';RECONFIGURE\""
bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -Q \"EXEC sp_configure 'clr enabled', 1;RECONFIGURE\""
bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -Q \"EXEC sp_configure 'clr strict security', 0;RECONFIGURE\""

There are three potential problems with this code:

Problem 1 Image Tag

There is no image tag specified for the microsoft/mssql-server-linux image, therefore, if Microsoft push a newer version of the image to docker hub, this will be pulled down from docker hub when the build pipeline runs. This is easily fixed by tagging the image with a tag for an explicit version, e.g. microsoft/mssql-server-linux:2017-GA.

Problem 2 Waiting For The Instance To Startup

The second problem is that the line immediately following the call to docker.image will fail, this is because the instance will not be ready to accept client connections. Again, the solution to this is relatively straight forward, this is to check for a ready to accept client connections message in the output from docker logs:

 PowerShell "While (\$((docker logs SQLLinux${env.BRANCH_NAME} | select-string ready | select-string client).Length) -eq 0) { Start-Sleep -s 1 }" 

Due to the nuances of escape sequences in Jenkins groovy script I had to perform a select-string on ready and then pipe the output this another select-string to check for client.

Problem 3 Handling Timeouts

So far so good, however there is the chance that the command to start the container might still hang, it would be quite easy to come up with some PowerShell code to set a limit on the timeout. However, Jenkins provides the means of coding this directly into the pipeline without having to make any call outs to other scripting languages:

timeout timeout(time: 20, unit: 'SECONDS') {
    PowerShell "If (\$((docker ps -a --filter \"name=SQLLinux${env.BRANCH_NAME}\").Length) -eq 2) { docker rm -f SQLLinux${env.BRANCH_NAME} }"
    docker.image('microsoft/mssql-server-linux:2017-GA').run("-e ACCEPT_EULA=Y -e SA_PASSWORD=P@ssword1 --name SQLLinux${env.BRANCH_NAME} -d -i -p ${BranchToPort(env.BRANCH_NAME)}:1433")
    PowerShell "While (\$((docker logs SQLLinux${env.BRANCH_NAME} | select-string ready | select-string client).Length) -eq 0) { Start-Sleep -s 1 }"
    bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -Q \"EXEC sp_configure 'show advanced option', '1';RECONFIGURE\""
    bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -Q \"EXEC sp_configure 'clr enabled', 1;RECONFIGURE\""
    bat "sqlcmd -S localhost,${BranchToPort(env.BRANCH_NAME)} -U sa -P P@ssword1 -Q \"EXEC sp_configure 'clr strict security', 0;RECONFIGURE\""
}

So there you have it three very simple best practices for handling container start-ups in a Jenkins build pipeline implemented using the Groovy DSL.