Windows containers & SQL Server – Part 2 – Creating new images

Part 1 of this series covered installing Docker for Windows and the basic comands for managing SQL Server images and containers. This post will cover creating customized container images.

There are 2 methods for creating a custom container image:

  1. Save a modified container as a new image using the commit command.
  2. Use a dockerfile to script the creation of a new image using the build command.

Save a modified container with docker commit

Before a container is saved as a new image, it should have some kind of changes made to it. With SQL containers, the most common methods for making modifications are SQL Server Management Studio (SSMS) and PowerShell.

  • To modify a container using SSMS, connect to the containerized SQL instance using the host computer name and the port assigned to the container (from the docker run command).
    eg. .,1433 or localhost,1433
  • To modify a container using PowerShell, use the docker exec command to open an interactive PowerShell session to the container.
    docker exec -it <container name or id> powershell

Once the container has been modified, the docker commit command is used to save (commit) the container as a new image to a repository. By default, the commit command will pause a running container while the image is saved. However, if the container is running a SQL Server instance, it may be advisable to stop the container before committing it. Stopping the container will reduce the risk of database corruption from an uncommitted database transaction. The process for stopping and committing an image is:

  1. (Optional) stop the container
    docker stop <container id or name>.
  2. Save the new image (note: if saving to the local machine, the repository name and / can be ommitted.)
    docker commit <container id or name> <repository>/<image name>:<tag>

Scripting image creation with dockerfiles

The docker build command sends the contents of the working directory, along with a dockerfile, to the Docker daemon, as a build context, to create the new image. A dockerfile is a plain text file that contains the name of a (base) image, along with a set of instructions for modifying the image. By default, the dockerfile is assumed to be in the root of the working directory, but a separate location can be specified using the -f parameter in the build command. Additionally, the -t parameter can be used to specify a repository and tag for the new image. Finally, the working directory can be specified using a Path or URL. In the example below, the current directory (.) is being used as the working directory (the docker build command is being run at the root level of the working directory).

docker build -f c:\dockerdata\dockerfile -t gitlab/newimage:ver2 .

The traditional (default) filename for a dockerfile is dockerfile, with no file extension. Azure Data Studio however, supports using a .dockerfile file extension. Which can be confusing when looking at various online documentation, where the dockerfile example filename can look like: dockerfile, config.dockerfile, <image name>.dockerfile, or even dockerfile.dockerfile.

Below is an example of a basic dockerfile:

# escape=`

# Use SQL 2017 Express for the base image.
FROM microsoft/mssql-server-windows-developer

# Metadata indicating an image creator, image version & description
LABEL Maintainer="JW"
LABEL version="1.0"
LABEL description="Demo SQL build"

# Use PowerShell to create a folder inside the image.
RUN powershell -command New-Item -ItemType "directory" -Path "c:/DemoData/db"

# Add contents of the local db folder to the folder created in the image.
ADD db "c:/DemoData/db"


Some basic syntax for dockerfiles:

  • The hash symbol (#) is used for comments.
  • The (optional) parser directive must be the top line of the dockerfile, if present. It is used to change the default escape character from a backslash \ to a backtick ` for the rest of the file. The escape character can be used to escape characters in a line, or to escape a new line. Changing the escape character to a backtick allows Windows-based hosts to use an escape character that is not used in directory paths.
  • FROM is the first command in the dockerfile (except for the optional parser directive). It specifies which image the dockerfile changes will use as a base (starting point).
  • The LABEL instruction(s) (tags) add metadata about the image. If the label already exists in the base image, the newer value in the dockerfile will be used in the new image.
  • RUN will execute commands (ie CMD or PowerShell) in the new image.
  • EXPOSE can be used to provide information about what ports (tcp/udp) are used by the image. EXPOSE does not expose the port, it just provides documentation to the image user about what ports are intended to be exposed. The actual port(s) are exposed using -P with the docker run command.
  • ADD can be used to copy files, directories and remote files (via URL) into the image filesystem.
  • VOLUME creates a mount point in the image for external volumes from the host or other containers. For Windows-based containers, the path of the mount point inside the image must be a new or empty directory, or on a drive other than C.

The full list of dockerfile commands can be found at: https://docs.docker.com/engine/reference/builder

When the docker build command is executed, the output (image below) will include a Step for each instruction contained in the dockerfile (excluding the parser directive). Each step results in a new layer that is saved in the image cache. If the command text (and related files) have not changed since a previous build, the Docker Daemon can reuse the layer from the image cache. When this happens the build step output will include “—> Using cache”. Using a cached layer (instead of creating a new one) reduces the processing required to build a new image and results in shorter build times.

docker build output (some step outputs compressed for brevity)

Once the build completes successfully, the new image is ready for use (from the designated repository).

Leave a Reply

Your email address will not be published. Required fields are marked *