Containerizing the database

Our first task when containerizing a process is to find the appropriate base for our database image. During a PoC, it is a good idea to use a pre-made official image from the Docker Hub. In our case, we will be looking for images created by Microsoft. Please note that, later down the adoption path, you will probably want to create your own hardened-based images for enterprise development. We will talk more about that during the pilot chapter. There are a couple of ways to discover base images. One is to simply Google for the image and look for links to Docker Hub. Another approach is to just search within Docker Hub.

We begin our search for a base image in a most familiar way, by Googling "Docker SQL express 2016 server". The first result is a link to a Microsoft image on Docker Hub—it looks promising so we follow the link to https://hub.docker.com/r/microsoft/mssql-server-windows-express/ and review the image information page. So far, it looks like a good fit. However, I want to make sure that I find a version that's compatible with our application. So, I click on the Tags tab, where we find a 2016-sp1 tag for this image. So, the address of our image is microsoft/mssql-server-windows-express:2016-sp1. This is an image in the Docker Hub public registry, located within the Microsoft namespace, in a repository called mssql-server-windows-express, with a tag of 2016-sp1. 

Congratulations, we have the beginnings of our database Dockerfile. The first line is an important practice for building Windows images. The # escape=` tells the docker build command to treat the backtick (`) as the escape character instead of the default backslash (\). On Windows, the backslash is of course used in file paths and you do not want file path characters to be confused with escape characters.

In the second line, we see the base image that we discovered on Docker Hub:

# escape=`
FROM microsoft/mssql-server-windows-express:2016-sp1

We now return to the Docker Hub repository information page for the sql-server express image for more instructions on how to use this image. We noticed some interesting instructions about setting environment variables. We create a section in our Dockerfile for setting environment variables that will be used by MS SQL when it runs in the container. One variable is for accepting the End User Licensing Agreement (EULA) and the other variable is the system administrator password. Please note the password used here for the DB image will have to match the connection string of our web application as stored in the Web.config file of our application image (we will build that next). Also, please note the use of the backtick (`) character as a line continuation and therefore all of the following lines are part of a single ENV command in the Dockerfile:

ENV ACCEPT_EULA="Y" `
sa_password="DockerCon!!!" `
DATA_PATH="C:\mssql"

The DATA_PATH environment variable is used in conjunction with the Docker volume (shown in the following).

VOLUME ${DATA_PATH}

WORKDIR C:\init
COPY docker\db .
CMD ./init.ps1 -sa_password $env:sa_password -Verbose

Using a Docker volume this way stores MS SQL database files outside of the database container on the host filesystem. Later, we can use this volume to mount our database's files in this volume and share the same files between different runs of the container. Therefore, if the container running the database is stopped and restarted, the database state will be preserved across the new and old containers. 

WORKDIR sets the active directory inside the container you are building, much like a change directory (cd) command, where your location on the file system is preserved as a working directory until change it again. Please note that if I issue a change directory command, it is only valid on the current line of the Dockerfile. This means that if you cd to a directory in one line, and in the next line of the Dockerfile you run PWD, you will see you are back to the default root directory. This relates to how Docker uses an intermediate container to generate image layers; there's more to come on this topic later, when we talk about optimizing Dockerfiles in the pilot chapter.

COPY moves files from the {my-build-context}\docker\db source into the current directory, = WORKDIR = C:\init. So, my files in the docker\db directory end up in the c:\init directory. 

CMD is the default command that's executed when the container is run (without a command in the docker run command line). In our case, CMD runs the init.ps1 PowerShell script. The init.ps1 script hides the logic that determines whether there is already a database present in the C:\mssql directory. If the database files exist, it will use them. If the files don't exist, then a sequel script is run to populate the initial database. This is a pattern that can be really helpful for test applications, where you allow Docker to create an ephemeral volume and therefore generate a fresh database every time it runs:

The HEALTHCHECK section of Elton's Dockerfile (shown in the following) will be used later when we deploy our container into a Docker cluster. A health check defines code that's executed by the orchestrator in order to determine the health of a container. In our case, it's running a SQL command from inside our SQL database container. If an orchestrator finds a container unhealthy, it'll kill it and replace it with a fresh container:

HEALTHCHECK CMD powershell -command `
try { `
$result = Invoke-SqlCmd -Query 'SELECT TOP 1 1 FROM Countries' -Database SignUpDb; `
if ($result[0] -eq 1) { return 0} `
else {return 1}; `
} catch { return 1 }

We now have a complete docker\db\Dockerfile to build our containerized database server image shown in the following code block:

# escape=`
FROM microsoft/mssql-server-windows-express:2016-sp1
SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

ENV ACCEPT_EULA="Y" `
sa_password="DockerCon!!!" `
DATA_PATH="C:\mssql"

VOLUME ${DATA_PATH}

WORKDIR C:\init
COPY .\docker\db .

CMD ./init.ps1 -sa_password $env:sa_password -Verbose

HEALTHCHECK CMD powershell -command `
try { `
$result = Invoke-SqlCmd -Query 'SELECT TOP 1 1 FROM Countries' -Database SignUpDb; `
if ($result[0] -eq 1) { return 0} `
else {return 1}; `
} catch { return 1 }

We've discussed every line in the previous code block, except for the shell line in the file. The key part of this line is the $ErrorActionPreference = 'Stop' section that tells the shell to stop on the first error and not to on error, resume next, the default error preference. In other words, stop running the script if there is an error.

It's time to build an image using Dockerfile. We have copied Elton's code to our Windows build machine, which is either a Windows 10 desktop or a Windows 2016 server. 

Let's assume you copied your files to the mta-netfx-dev directory. We then focus our attention on the db subtree under the docker directory to better understand the docker build command that we are going to run (shown in the following). Please note the location of the database's Dockerfile, init-db.sql, and init.ps1 files:

mta-netfx-dev
    ├── docker
    │ ├── db
    │ │ ├── Dockerfile
    │ │ ├── init-db.sql
    │ │ └── init.ps1

From the mta-netfx-dev directory, we run the docker image build command to create the db-image:v1 image and use Dockerfile in the docker\db folder that we just created. You should see some successfully built and tagged images at the bottom of the output. Don't forget the trailing period in this command. It provides the build context where the COPY command gets its base, in this case, the mta-netfx-dev directory:

mta-netfx-dev$ docker image build -t db-image:v1 --file .\docker\db\Dockerfile .
...
Successfully built 026c23401784
Successfully tagged db-image:v1

Now, we can fire up and test our database server (shown in the following). First, we are going to run a container using the database image we create. We use -d to run it in the background, expose port 5432 on the host, and forward it to port 5432 inside the container. These are the standard MS SQL ports. Next, we test the our database container by running an SQL command against our database from inside the container. In order to do that, we need to get a PowerShell prompt inside the container. We can use the docker container exec command to create a new PowerShell process and Command Prompt. Inside the container, we see the PowerShell PS C:\init> prompt in the WORKDIR. We use the SQL command module (the same one we are using for the health check in the Dockerfile) to query SignUpDb. We see that 1 is returned. You can of course run any SQL you would like to verify the data:

$ docker container run -d -p 5432:5432 db-image:v1
1fe040d1acf242d4cfdb47761db2a59f1bb44b5eedf17376b9455120d86281a5

$ docker container exec -it 1fe0 powershell
PS C:\init> Invoke-SqlCmd -Query 'SELECT TOP 1 1 FROM Countries' -Database SignUpDb

Column1
-------
1