# Dockerising SQL Server for Integration Testing

*2026-11-03*

> Everything I learned Dockerising a legacy SQL Server for CI


<small><i>(Originally drafted in October 2017 during my tenure at Xero. Republished here in November 2026 with updated context and removed proprietary references)</i></small>

Once upon a time, there was a hosting provider called RackSpace.

This hosting provider would provision machines to customers that were [long-lived pets](https://www.theregister.co.uk/2013/03/18/servers_pets_or_cattle_cern/).

These servers were lovingly stroked, petted, kept up-to-date and generally lived a good life.

[![TeamCity before](teamcity-before.png#center)](teamcity-before.png)

Then the big bad hosting provider Amazon Web Services came along.

This hosting provider would allow customers to provision their own machines. Once the customers were finished with them, they were taken out the back of the data centre and are never seen again.

[![TeamCity after](teamcity-after.png#center)](teamcity-after.png)

For data that was long-lived, this was a problem: when the server got shot, the data that was on that server would die as well.

Docker to the rescue!

# So Why Put A Database In Docker?

So why on Earth would someone want to put a legacy database in a Docker container? Are you mad?

Well, as it turns out – yes, the whole team were, in fact, completely insane.

The product was, for various reasons, heavily tied to a legacy database schema. This was mainly due to having the ability to read/write journals and journal lines, read a chart of accounts, as well as other miscellaneous bits and pieces of data needed to operate.

Until we reached the golden age of having our own database, we were tied to this one. But why did we need it in Docker?

## Tests For The Win

The code base was covered by over 5,500 unit tests. However, the eagle-eyed among you will note there were 700+ tests ignored by [NCrunch](https://www.ncrunch.net/). Why?

[![NCrunch ignored tests](ncrunch-ignored-tests.png#center)](ncrunch-ignored-tests.png)

Simple – they were [SpecFlow](https://www.specflow.com/) integration tests.

NCrunch is designed for fast feedback cycles and the SpecFlow tests ran too slowly for this purpose. Therefore, the SpecFlow tests were run manually on local machines through [ReSharper]({{< relref "posts/2024/re-sharpening-your-codebase" >}}).

It's for this reason we needed a local copy of the database – for testing, we had the databases in our local SQL Server installations. However, in AWS-land this wasn't possible, as the build agents did not have SQL Server installed (and nor should they).

Since Microsoft made [SQL Server 2016 Developer Edition completely free](https://www.mssqltips.com/sqlservertip/7201/sql-server-developer-edition-download-install-get-started/), they also created a [Docker image](https://hub.docker.com/r/microsoft/mssql-server/) containing this installation. This was perfect timing, as we could use this as the basis for our SpecFlow tests (talk about bleeding edge).

# In The Beginning

Before the CI server was migrated to AWS, the build server and agents were effectively static resources – they would run 24 hours a day, 7 days a week.

As such, the deployment process for the database looked something like this for each of the four database servers we used as part of our build:

- Download the latest [DACPAC](https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/data-tier-applications/overview) artefacts from the database builds
- Set the databases online
- Drop the databases
- Deploy the DACPAC files to SQL Server
- Verify the databases have been successfully created
- Delete all the SQL Server jobs
- Set the databases offline
- Copy the databases' MDF and LDF files

During the build:

- Mount a copy of the databases' MDF and LDF files
- Update the SpecFlow configuration connection strings
- Drop the databases after running the tests

This process worked for the most part – any time there was a change in the SQL Server DACPAC file, we would simply re-run the build and the agents would be updated.

Deploying the SQL Server DACPAC every time the SpecFlow tests ran was not only incredibly inefficient but also very time-consuming (~15-20 minutes).

If only there was some way to do some of these steps beforehand? Hmm!

# In The New World

There just so happens to be a technology that allows us to do all these steps ahead of time and then re-use them to speed up build times – [Docker!](https://www.docker.com/)

With Docker, we created the database from scratch and saved it to an image. This image meant our state was always clean and consistent between test runs. The other benefit was that when the image was pre-created, only half the steps outlined above needed to be executed – during the build, we just had to:

- Mount a copy of the databases' MDF and LDF files
- Update the SpecFlow configuration connection strings
- Drop the databases after running the tests

First step is to base the Dockerfile off the SQL Server 2016 Developer Edition image:

```dockerfile
#escape=`

FROM microsoft/mssql-server-windows-developer

ENV sa_password "test123..."
ENV ACCEPT_EULA "Y"
```

Then we can add the additional steps required to build the database:

```dockerfile
SHELL ["powershell", "-Command"]

RUN Start-Service MSSQLSERVER

HEALTHCHECK CMD [ "sqlcmd", "-Q", "select 1" ]

# Set the Windows Proxy
COPY 00-SetWindowsProxy.ps1 /
RUN .\00-SetWindowsProxy.ps1 -Proxy "proxy.inside.company.com" -Port "8080" -Bypass "127.0.0.1","localhost"...

# Get DACPAС files
COPY 01-GetDatabases.ps1 /
RUN .\01-GetDatabases.ps1

# Create databases
COPY 02-CreateDatabases.ps1 /
RUN .\02-CreateDatabases.ps1

# Set database compatibility mode to SQL 2012
COPY 03-SetDatabaseCompatibility.ps1 /
RUN .\03-SetDatabaseCompatibility.ps1

# Deploy DACPAC files
COPY 04-DeployDatabases.ps1 /
RUN .\04-DeployDatabases.ps1

# Set database recovery mode to SIMPLE
COPY 05-SetDatabaseRecoveryMode.ps1 /
RUN .\05-SetDatabaseRecoveryMode.ps1

# Delete unnecessary SQL jobs
COPY 06-DeleteJobs.ps1 /
RUN .\06-DeleteJobs.ps1

# Verify databases
COPY 07-VerifyDatabases.ps1 /
RUN .\07-VerifyDatabases.ps1

RUN Stop-Service MSSQLSERVER
```

When running this for the first time, the DACPAC files would get deployed but the container would just stop running.

Turns out the container was running out of memory (SQL Server being a bit of a memory hog), so upping the amount of memory assigned to the container fixed this issue:

```powershell
param(
    [string]$imageID = "db/docker-database"
)

Write-Host "Starting Docker container with image $imageID..." -ForegroundColor "magenta"

$containerID = $(docker run -d -m 4G -p 1433:1433 -e sa_password=test123... --e ACCEPT_EULA=Y $imageID)
$containerIPAddress = $(docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' $containerID)

docker ps -a

Write-Host "Docker container started with IP $containerIPAddress" -ForegroundColor "green"

$containerIPAddress.trim() | clip

Write-Host "IP address copied to clipboard"
```

Once this was changed, the container started and we were able to successfully run the SpecFlow tests against the container!

# Automate That Shizz

Once we had the container running locally, the next hurdle was getting this process running on the CI server. Effectively, all the CI process had to do was:

- Clone the CI repository
- Run the PowerShell script to build the container (which uses the aforementioned Dockerfile)
- Push the container to [Amazon Elastic Container Registry (ECR)](https://aws.amazon.com/ecr/)
- Agents performing a build can pull the image down from ECR before running the tests

[![Build Docker Database Steps](build-docker-database-steps.png#center)](build-docker-database-steps.png)

In a moment of pure genius, we realised the CI server could trigger builds based on a change at a particular URL. TeamCity exposes an unauthenticated [REST API called Ivy](https://blog.jetbrains.com/teamcity/2012/06/teamcity-ivy-gradle-maven/), which lists all the artefacts for a build in a handy XML format – this allowed querying the API to pull back the artefact names and pass those to the Docker container. The container could then use these URLs to grab the correct artefacts for the build.

[![Build Docker Database Triggers](build-docker-database-triggers.png#center)](build-docker-database-triggers.png)

Using these triggers meant that every time a change was made to the database branch, a new DACPAC artefact was produced, the build was triggered, and a new Docker image of the database was created.

So overall everything went swimmingly, right?

Umm… yeah.

# What Went Wrong?

## The Poxy Proxy

All outbound connections in the AWS environments were required to go through a proxy server. This meant whatever host the Docker image was built on (i.e. the CI agents) needed the proxy server set inside the container.

Luckily, the infrastructure team already had a script that did this. One of the first steps in the Dockerfile build process was to call this script, which threw a load of keys into the Windows Registry to define the proxy server to use.

(Note: when building the Docker image locally we didn't have the same proxy server between us and the outside world. This meant commenting out this line to build the image locally.)

```powershell
<#
.SYNOPSIS
    Script to set Windows Proxy settings.

.DESCRIPTION
    Instances that are deployed to into Cells do not have direct access to the
    Internet, and must use the Threat Protection Zone's (TPZ) proxy for
    website access (HTTP and HTTPS).
    This script mangles the registry and environment variables to setup
    the proxy settings.

.PARAMETER Proxy
    Hostname of proxy server. DNS name is preferred.

.PARAMETER Port
    Port proxy server is listening on. This is typically 8080

.PARAMETER Bypass
    Array of patterns that should bypass the proxy.
    These can be DNS based with wildcards (i.e. localhost, *.aws.company.com), or
    IP based (127.0.0.1, 169.254.)

.PARAMETER SetEnvironmentVariables
    Optional switch to set http_proxy, https_proxy and no_proxy environment
    variables for compatible applications (eg chef, python ) which respects
    linux proxy notations.

.PARAMETER AppendBypass
    Optional list of additional Bypass address list to be added to
    the previous settings.

.EXAMPLE
    .\set-windowsproxy.ps1 -Proxy "proxy.inside.company.com" -Port "8080" -Bypass "127.0.0.1","
```

## 'sa' Timeout Error

When the container first starts, it doesn't always respond to SQL Server logins. My guess is that several other Windows Services inside the container take time to start up before SQL Server is loaded and becomes responsive.

[!['sa' Timeout Error](sa-timeout-error.png#center)](sa-timeout-error.png)

To resolve this, the relevant PowerShell script attempted to connect to the Docker container in a loop. If the connection failed, the script waited a set number of seconds before retrying.

If SQL Server couldn't be accessed after a certain number of retries, the script exited by returning '1' and marked the build step as failed.

## Not Enough Memory

As mentioned above, SQL Server is a bit of a memory hog and requires more than the minimum amount of memory Docker initially allocates to the container at runtime (~1GB). This took a bit of head-scratching to figure out.

The workaround was to specify the amount of memory to give the container when it first started, ensuring it [had enough memory](https://docs.docker.com/engine/admin/resource_constraints/#limit-a-containers-access-to-memory) to begin with.

## Baking In The SQL Server Image

The SQL Server 2016 image is rather large, clocking in at around 15GB all up. As you can imagine, this takes quite a while to download (usually around 40 minutes), even with the big pipes that connect the AWS environment to the internet.

[![SpecFlow long build time](specflow-long-build-time.png#center)](specflow-long-build-time.png)

Due to Docker's [clever layering architecture](https://docs.docker.com/engine/userguide/storagedriver/imagesandcontainers/), we could use the base image and then build changes on top. To prevent downloading the image each and every time, the base SQL Server 2016 image was baked into the CI agent AMIs. This meant each agent already had 15GB of the overall image, and our changes were just appended before being uploaded to ECR.

Furthermore, because the base image already existed in ECR, Docker was clever enough to figure out which layers were different and just upload those that had changed.

## Logging Into ECR

Access to [Amazon's Elastic Container Registry](https://aws.amazon.com/ecr/) requires logging into the repository to obtain an access key, which is then used to upload the image.

At the time of development, the only way to log in to ECR was through the [Linux version of the AWS SDK command-line tools](http://docs.aws.amazon.com/cli/latest/reference/ecr/get-login.html). As we wanted everything done through PowerShell Cmdlets, it didn't make sense to rely on the Linux tools to achieve this.

[A bit of Googling](http://btburnett.com/2017/01/docker-login-for-amazon-aws-ecr-using-windows-powershell.html), string-splitting, and base64-ing later, we were able to log in to ECR to obtain an access token and use this going forward in the process.

```powershell
Write-Host "Retrieving ECR token..." -ForegroundColor "magenta"

# Stolen from: http://btburnett.com/2017/01/docker-login-for-amazon-aws-ecr-using-windows-powershell.html
$token = Get-ECRAuthorizationToken -Region $region
$tokenSegments = [System.Text.Encoding]::ASCII.GetString([System.Convert]::FromBase64String($token.AuthorizationToken)).Split(":")
$hostName = (New-Object System.Uri $token.ProxyEndpoint).DnsSafeHost

Write-Host "Logging into ECR..." -ForegroundColor "magenta"

docker login -u $($tokenSegments[0]) -p $($tokenSegments[1]) $hostName

Write-Host "Pushing container to ECR..."

$imageTagLatest = $imageID + ":latest"

docker tag $imageID $imageTagLatest

docker push ${imageTagLatest}

if($versionNumber)
{
    Write-Host "Version number $versionNumber specified - tagging image..."

    $imageTagVersion = $imageID + ":" + $versionNumber

    docker tag $imageID $imageTagVersion

    docker push ${imageTagVersion}
}

Write-Host "Finished pushing container"
```

(Note: it turns out that AWS later added the [`Get-ECRAuthorizationToken` Cmdlet](https://docs.aws.amazon.com/powershell/latest/reference/items/Get-ECRAuthorizationToken.html) to the AWS SDK for PowerShell for retrieving an access token.)

## CI Agent AWS Roles

At first we were able to create the image locally and upload it to ECR using the access key approach. However, when run on the CI agents, the build would simply hang for hours and refuse to continue.

Turns out the build agents needed the correct IAM policy for this to work.

## Simple Transaction Logs

As the Docker container was simply spun up, tests executed, and then the container destroyed, there was no need to keep any SQL Server transaction logs.

Therefore, to save space, we set the recovery mode of the database to `simple`, as there was no need to keep these logs hanging around:

```powershell
param(
    [string]$Server = 'localhost'
)

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

$sql = "ALTER DATABASE DockerDatabase SET RECOVERY SIMPLE`n"
$sql = $sql + "GO`n"

Invoke-SqlCmd -ServerInstance $Server -Query $sql -OutputSqlErrors $true

Write-Host "Set database recovery modes to simple" -foregroundcolor "magenta"

Set-Location $PSScriptRoot
```

This also sped up the tests, because Docker didn't have to keep re-allocating disk space as the transaction logs grew.

## SQL Server Compatibility

The database shards in production ran on SQL Server 2014, yet the Docker container image used SQL Server 2016. Rather than spending time shoehorning SQL Server 2014 into a container, we dropped the database compatibility level back to SQL Server 2014:

```powershell
param(
    [string]$Server = 'localhost'
)

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

$sql = "ALTER DATABASE DockerDatabase SET COMPATIBILITY_LEVEL = 120`n"
$sql = $sql + "GO`n"

Invoke-SqlCmd -ServerInstance $Server -Query $sql -OutputSqlErrors $true | select name
Write-Host "Updated database compatibility levels" -foregroundcolor "magenta"

Set-Location $PSScriptRoot
```

This ensured the tests ran against tables, views, procs, functions, and other SQL Server objects identically to production.

## Removing Old Images

Every time a Docker image was uploaded to ECR, the old images would hang around until someone deleted them manually via the AWS Console.

We could obtain the labels for each of the images in ECR via [the AWS CLI](http://docs.aws.amazon.com/powershell/latest/reference/index.html?page=Get-ECRImageMetadata.html&tocid=Get-ECRImageMetadata), iterate over the list, and delete older images based on their label.

All we had to do was ensure the latest Docker image had the tag `latest` and all other images had a version number based on the current build counter value in TeamCity.

[![ECR Docker Database](ecr-docker-database.png#center)](ecr-docker-database.png)

[![ECR Docker Database Images](ecr-docker-database-images.png#center)](ecr-docker-database-images.png)

[![Docker Build Container Counter](docker-build-container-counter.png#center)](docker-build-container-counter.png)

(Note – since the time of development, Amazon added [ECR Lifecycle Policies](http://docs.aws.amazon.com/AmazonECR/latest/userguide/LifecyclePolicies.html) that can automatically remove old images using a policy you specify.)

## Artefacts Being Cleaned Up

After the Docker database build had been running for a while, we found the build would randomly fail. It turned out the CI servers had a lifecycle policy that cleaned up all old artefacts after 30 days (even if the builds themselves remained).

Development in the database branch was rare, which meant no builds were being triggered. Ergo, after 30 days the DACPAC files were removed. The side effect of this was that the URL Build Trigger in the Build Docker Database build would fire (because the artefacts had technically changed) and the build would subsequently fail, because there were no artefacts left to download.

The only workaround we found was to put a helpful error message in the build log directing someone to the relevant upstream builds.

[![Ensure TeamCity artifacts exist](ensure-teamcity-artifacts-exist.png#center)](ensure-teamcity-artifacts-exist.png)

This required a simple change to the 'Get artefacts' PowerShell script, which checked that the artefacts actually existed at the URL before attempting to continue. By writing the upstream build URL to the build log, someone could kick off the build to generate the DACPAC files, then re-run the Build Docker Database build to grab the latest artefacts.

(A better solution would have been for the Build Docker Database build to call upstream and trigger a build before starting its own. However, this required the CI server to call the other server via the TeamCity REST API to trigger the build, and we didn't allow guest access to this API.)

### Out-Of-Date Agents

The CI build agents were based on Amazon AMIs, so they were terminated after a certain time period to save money. The next morning, the agents were restarted using a fresh copy of the AMI, meaning they didn't have the latest Docker image. Pulling this image the first time round could take upwards of 30 minutes each time.

[![Out-of-date Agent](out-of-date-agent.png#center)](out-of-date-agent.png)

We could have baked the database Docker image directly into the AMI, but this would defeat the purpose of using a pre-built image – the database itself was constantly in flux through development.

Therefore, the only workaround we found was to have a build that simply did a `pull` of the latest Docker image from ECR every morning before the team arrived at the office.

[![Update Compatible Build Agents Build Steps](update-compatible-build-agents-build-steps.png#center)](update-compatible-build-agents-build-steps.png)

Due to the way TeamCity uses Cloud Agent pools, this would only cause one cloud agent to start. The solution was to have a staggered schedule for the build, 10-15 minutes apart. This ensured one agent would start, then 15 minutes later another build would trigger while the first was running, causing another agent to start, and so on until all the agents had been started and were running the pull build.

[![Update Compatible Build Agent Trigger](update-compatible-build-agent-triggers.png#center)](update-compatible-build-agent-triggers.png)

By the time the team got to the office, all four build agents had been started and updated with the latest version of the container.

[![Update Compatible Build Agents](update-compatible-build-agents.png#center)](update-compatible-build-agents.png)

## The End Result

The consequence of all the above? Build time was reduced from 15-20 minutes (an hour if you count the time to download the SQL Server 2016 base image) to just 8 minutes for running the SpecFlow tests – a saving of almost 85%.

Once we changed from a sequential build process to parallel build templates, this reduced the overall build time from 30 minutes to roughly 15 minutes. Not bad!

As a side note, I'd highly recommend [the following book](https://www.goodreads.com/book/show/17288968-beautiful-builds) by Roy Osherove – *Beautiful Builds* – I read a pre-release version a while back, and there were a lot of useful tidbits around cutting down build times.

[![Beautiful Builds](beautiful-builds.jpg#center)](https://www.goodreads.com/book/show/17288968-beautiful-builds)