Introduction

You won’t need me to tell you that Docker has been a dominating force in automated infrastructure for the last couple of years. For the uninitiated, a container is an isolated, lightweight execution context for an app/service (and its dependencies) that share a kernel with other containers. Because an app can be delivered in its deployed state and run consistently by any Docker host, using containers greatly reduces the scope for environmental issues, e.g. incompatible versions of libraries on the host machine, interfering external processes, etc. Containers are the biggest advancement in application delivery since the birth of server virtualization, so they’re worth learning how to use.

Docker containers were born on Linux, but can now be built and run on Windows 10 and Windows Server 2016 also. Last week, I was reading the highlights from DockerCon 2017, where I discovered that you can now use a mixture of Windows and Linux hosts in a Docker Swarm (a pool of container hosts) to orchestrate a stack using both types of container. I decided this was something I wanted to try for myself.

I thought the easiest way to get a Windows container into a hybrid application stack was to deploy one containing a SQL Server database. Microsoft is quite good for providing sample databases that you can use to learn the latest features of SQL Server, the most well-known of these is the AdventureWorks database but this has been retired recently and replaced by one called Wide World Importers. Either of these would have been a low-cost route to getting a realistic database, running in a Windows Container, in my experiment.

A quick scan of Docker Hub (the official repository for Docker images) told me that nobody had containerized either of these sample databases yet, so the task fell to me. This blog post is a walkthrough of what I did to build a Docker image for the Wide World Importers sample database on SQL Server 2016 Express and Windows Server 2016 Core.

Requirements

Tool Version used in this post Link
Docker for Windows 17.03.1-ce https://www.docker.com/docker-windows

Once installed, ensure that Docker for Windows is set to run Windows containers. Just right click on Docker icon in the systray and click “Switch to Windows containers…” if you need to. You’ll see strange error messages about an “unknown blob” if you don’t do this.

Getting started

Open PowerShell as Administrator* and follow along:

*Hyper-V is heavily involved in Docker for Windows and needs the privileges.

# Create a project directory to build the image from.
New-Item -ItemType Directory -Path "$env:UserProfile\Projects\DockerWideWorldImporters"
Set-Location -Path "$env:UserProfile\Projects\DockerWideWorldImporters"

# Download WideWorldImporters-Full.bak from GitHub.
Invoke-WebRequest -UseBasicParsing -Uri https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak -OutFile .\WideWorldImporters-Full.bak

# Create Dockerfile for image.
New-Item -Name Dockerfile

Building our Dockerfile

Docker images are defined by and built from a Dockerfile. Here is the Dockerfile for my wideworldimporters image. Paste it into the Dockerfile you created for yourself earlier.

# escape=`

FROM microsoft/mssql-server-windows-express

ENV user_name _
ENV user_password _

WORKDIR C:\

ADD .\WideWorldImporters-Full.bak C:\
ADD .\Invoke-BuildActions.ps1 C:\
ADD .\Invoke-RunActions.ps1 C:\

SHELL ["powershell", "-File"]

RUN .\Invoke-BuildActions.ps1

EXPOSE 1433

CMD .\Invoke-RunActions.ps1 -Username $env:user_name -Password $env:user_password -Verbose

Let’s go through it line-by-line.

# escape=`

The first thing we do is change the Dockerfile escape character from \ to ` (as in PowerShell). Since we’re defining a Windows container we need to be able to use \ liberally for paths and we don’t want Docker thinking we’re trying to escape everything.

FROM microsoft/mssql-server-windows-express

Every Docker image has a parent image and defines only the differences between the parent and itself. This is great because you don’t have to work from scratch every time. That’s something of an in-joke, as the root of all Docker image hierarchies is the empty image, called scratch.

Our parent is the microsoft/mssql-server-windows-express image built by Microsoft, which installs SQL Server 2016 Express SP1 and resets the password of the sa account to something you provide. That image’s parent is microsoft/windowsservercore, a GUI-less Windows Server 2016 image.

ENV user_name _
ENV user_password _

The ENV instruction sets environment variables in the runtime environment. The user typically provides values for these when they run: docker run to create a container from the image. You can think of these like parameters.

Our wideworldimporters image creates a user account with read/write permissions on the WideWorldImporters database, as the parent image only configures the sa account, which no sane person would really use directly in an app as it’s the superuser. These environment variables represent the user-provided username and password to use in the creation of that account. The underscore is just a default value.

WORKDIR C:\

This sets the working directory to use when running any of the other ADD, RUN or CMD instructions in this Dockerfile.

ADD .\WideWorldImporters-Full.bak C:\
ADD .\Invoke-BuildActions.ps1 C:\
ADD .\Invoke-RunActions.ps1 C:\

The ADD instruction adds files and directories from the host filesystem to the image filesystem. The files we’re adding here are the backup file (.bak) of the WideWorldImporters database (that we downloaded earlier) and two PowerShell scripts. I’ll explain these scripts later on.

SHELL ["powershell", "-File"]

The SHELL instruction sets the default shell to use when executing RUN or CMD instructions. The default shell command of Windows containers is [“cmd”, “/S”, “/C”] and we’re using PowerShell scripts, so I’ve overridden this to be [“powershell”, “-File”].

RUN .\Invoke-BuildActions.ps1

RUN instructions are executed as part of the build process of the Docker image, this is what differentiates your image from the parent image you’re using. This instruction just runs the Invoke-BuildActions.ps1 script, I’ll show you the contents of this script later on.

EXPOSE 1433

The EXPOSE instruction tells Docker that the container is allowed to listen on the specified port at runtime. Port 1433 is the default port for SQL Server.

CMD .\Invoke-RunActions.ps1 -Username $env:user_name -Password $env:user_password -Verbose

The CMD instruction defines the command that runs when a container is instantiated from this image. There can only be one of these per image.

If you take nothing else away from this walkthrough, remember that RUN instructions are executed at build-time and CMD instructions are executed at run-time (when a container is started). Not knowing the difference will lead to lots of confusion.

Build-time actions

Now we just need to create the PowerShell scripts that will be run during the build of the image and on starting a new container from it. Back in PowerShell execute:

New-Item -Name Invoke-BuildActions.ps1
New-Item -Name Invoke-RunActions.ps1

# Open the scripts in PowerShell ISE.
powershell_ise.exe "Invoke-BuildActions.ps1, Invoke-RunActions.ps1"

Paste and save the following into Invoke-BuildActions.ps1:

# Install SqlServer module.
Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force
Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
Install-Module SqlServer -Force -AllowClobber

Import-Module SqlServer

# Create location for database artifacts.
New-Item -Type Directory -Path C:\SqlData

# Restore the WideWorldImporters database from the .bak file.
Invoke-Sqlcmd -ServerInstance localhost -Query `
"USE [master] RESTORE DATABASE [WideWorldImporters] `
FROM DISK = N'C:\WideWorldImporters-Full.bak' `
WITH MOVE N'WWI_Primary' TO N'C:\SqlData\WideWorldImporters.mdf', `
MOVE N'WWI_UserData' TO N'C:\SqlData\WideWorldImporters_UserData.ndf', `
MOVE N'WWI_Log' TO N'C:\SqlData\WideWorldImporters.ldf', `
MOVE N'WWI_InMemory_Data_1' TO N'C:\SqlData\WorldWideImporters_InMemory_Data_1'"

What this script does is:

  • Install the SqlServer module from PowerShell Gallery. Our parent Docker image does come with the SQLPS PowerShell module, but the SqlServer module is a much-enhanced version of it.
  • Create a new directory to move some of the WideWorldImporters database files to when we restore it.
  • The RESTORE SQL command to add the WideWorldImporters database from the .bak file. We need to change the location of the database files with MOVE declarations because otherwise, RESTORE will try to use the same file locations where the backup was taken from. We’re in a completely different filesystem now and these locations don’t exist, so we need to redirect them.

Run-time actions

Now for the container startup script. This contains the things that we might want to data-drive and customize on a per-container basis. Paste and save the following into Invoke-RunActions.ps1:

Param (
    [Parameter(Mandatory=$true)][string] $Username,
    [Parameter(Mandatory=$true)][string] $Password
)

# If $Username or $Password is a variable, resolve them.
if($Username -like "$*") {
    $Username = Invoke-Expression -Command $Username
}
if($Password -like "$*") {
    $Password = Invoke-Expression -Command $Password
}

# Validate $Username and $Password are not defaults
if($Username -eq "_") {
    Write-Host "ERROR: A name for the new user account is required. Please supply a '--env user_name' variable with the 'docker run' command."
    Exit(1)
}
if($Password -eq "_") {
    Write-Host "ERROR: A password for the new user account is required. Please supply a '--env user_password' variable with the 'docker run' command."
    Exit(1)
}

# Create login for user.
$SecurePassword = ConvertTo-SecureString -String $Password -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $Username, $SecurePassword
Add-SqlLogin -ServerInstance localhost -LoginName $Username -LoginType SqlLogin -DefaultDatabase WideWorldImporters -Enable -GrantConnectSql -LoginPSCredential $Credential | Out-Null
Write-Verbose "Created SQL login for user $Username."

# Create user for WideWorldImporters database
$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList localhost
$Database = $Server.Databases['WideWorldImporters']

$User = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User -ArgumentList $Database, $Username
$User.Login = $Username
$User.Create()
Write-Verbose "Created user $Username."

# Assign user to roles with read and write privileges.
Invoke-Sqlcmd -ServerInstance localhost -Database WideWorldImporters -Query "ALTER ROLE db_datareader ADD MEMBER $Username"
Invoke-Sqlcmd -ServerInstance localhost -Database WideWorldImporters -Query "ALTER ROLE db_datawriter ADD MEMBER $Username"
Write-Verbose "Granted user $Username read and write access to Database: WideWorldImporters."

# This is the CMD script from the parent Docker image (microsoft/mssql-server-windows-express). By running it we inherit its functionality.
.\start.ps1 -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs "$env:attach_dbs" -Verbose

What this script does is:

  • Expand the environment variables defining the username and password to use for the new user account. Validates that values for these have actually been provided by the user as part of docker run, i.e. they’re not ‘_’, our default value.
  • Creates a SQL login with the provided username and password, creates the user account and assigns it to roles with read and write permissions on the WideWorldImporters database.
  • Executes start.ps1 – which is actually the script run by the CMD instruction of our parent Docker image: mssql-server-windows-express. As this script overrides that script (there can be only one CMD), we execute it here so that we don’t lose its runtime behavior. You can see what the script does from the parent image’s GitHub page. This script requires the user to accept the SQL Server EULA and I can’t really intercept that on behalf of anybody, so it was important to preserve the original behavior of the parent image.

Building the image and running the container

Now we can build the image, run it and test it. Back in PowerShell, execute:

# This will probably take a few minutes to acquire the parent image from Docker Hub,
# restore the .bak file, etc. Go and make yourself a coffee and come back.
docker build --tag wideworldimporters .

# Now let's run it. Remember we need to provide those runtime environment variables.
# - sa_password is an env variable that our parent image uses to change the password
#   of the sa account. Make sure it complies with minimum complexity requirements.
# - user_name is the username of the account we will create with access to the
#   WideWorldImporters database.
# - user_password is the password to use for the user account we will create. Make
#   sure it complies with minimum complexity requirements.
# - ACCEPT_EULA is required by our parent image. Passing 'Y' means you accept the
#   SQL Server Express license agreement.
docker run --detach --env sa_password=AdminPass123! --env user_name=WWIUser --env user_password=UserPass123! --env ACCEPT_EULA=Y wideworldimporters

# Assuming that ran without any trouble, let's retrieve the ContainerId of our newly
# created container. It will be output by the following command.
docker ps

# Start a PowerShell session on the container.
docker exec --interactive <ContainerId> powershell

That final command will attach us to a PowerShell session inside the container, now we can test that we have access to the WideWorldImporters database with the credentials we provided at runtime.

Invoke-Sqlcmd -ServerInstance localhost -Database WideWorldImporters `
-Username WWIUser -Password UserPass123! `
-Query "SELECT Count(CityId) AS NumCities FROM Application.Cities"

You should see the following response, indicating that the database and our new user account is operational:

NumCities
---------
    37940

Closing thoughts

Hopefully the experience of building that Docker image will be a good foundation for deploying other apps and services into Windows Containers in future. Docker images are intended to provide a single service each, so it shouldn’t get a lot more difficult than you’ve seen.

I’ve made this image publicly available in Docker Hub in the kmacphee/wideworldimporters repository. Feel free to make use of it if you want to experiment with a Dockerized SQL Server database.

About the Author Kirk MacPhee

An experienced software developer and technical lead, specializing in automation technologies and their application.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s