Docker – Building a SQL Server image

There are several Microsoft SQL Server container images available for download.  These consists of SQL Server 2017 and now SQL Server 2019 CTP2.1.  Most of those can be found here.  However, what if you need to customize an image by adding additional features?  Docker provides a command line utility to build new images using Dockerfiles.  A Dockerfile is a text document that contains all the commands a user could call on the command line to assemble an image.  For more information see here.

Docker build

Using docker build, we’ll create an image based on the Red Hat Enterprise Linux OS.  The Dockerfile we’ll use for all examples can be found in the https://github.com/sqlservermigrations/containers repository.   Using Git, we’ll clone the repo and build the image.

Prerequisites

    • You will need access to the Red Hat Container Catalog via a Red Hat subscription. A free developer subscription is available.
    • You’ll need a Red Hat Enterprise Linux VM (or physical machine) that has been registered to your subscription. The Red Hat Container Developer Kit can be utilized as well (method not shown here).
    • If using a RHEL VM or physical machine, Docker must be installed.
    • git is needed to clone this repository.
    • Prior to building the image, the password within the setup-tools.sh file, line 9, must be set to the same password used when building the image.

Building the image using a RHEL VM

  1. SSH to the RHEL VM
  2. Verify docker is running.
    1. If the status does not show active, start docker.
  3. Make a directory to clone the repository to and then clone using git.  If git isn’t installed, use  sudo yum -y install git to install.
  4. Build the image.  This will take approximately 10 – 15 minutes (maybe longer) depending on your internet connection.  -t is used to name and tag the image (registry/name:tag).  For full list of options which can be used with docker build, see here.
  5. Verify the image was created successfully.

 

 

Starting a mssql-server instance

  1. Use  docker run to create a container from the new image.  Docker run creates a process, known as a container, is isolated, contains its own file system, has its own networking, and has its own isolated process tree separate from the host.
  2. The command finishes and returns the unique container ID.  Next, run sudo docker ps (docker ps)to ensure the container is running.

 

The docker run command above uses several parameters:

  1. -e – The image requires at least two environment variables to be provided.  Those are provided via the “-e” flag.
    1. ACCEPT_EULA – Must be equal to Y.
    2. SA_PASSWORD – Must be set to a strong password.
    3. Full list of environment variables can be found here.
  2. –name – Provides a name for the new container.  Otherwise all references made to the container in proceeding steps would need to reference the container ID.
  3. -p – Specifies the port in which SQL Server is listening on internally and the host port to listen on.  For example, if you wanted to run multiple containers you’d specify a different host port for each (5002:1433).  If connecting remotely be sure any firewalls on the host are configured to allow traffic on the specified host port.
  4. -d – Starts the container in detached mode.

 

Viewing the container logs

Docker logs provides a way for viewing logs generated within containers.  Results can then be piped to tools such as grep for doing additional filtering.  Let’s take a look.

  1. Run docker logs and provide the name of container.
  2. This should print out the SQL Server error log.

To filter for a specific text within the log, pipe the results to grep.  We’ll see how many CPU cores SQL Server is using.

The screenshot above shows SQL Server has detected 2 sockets with 8 cores per socket.

 

Connect to Microsoft SQL Server running in a container

SQLCMD

This particular image includes the mssql-tools package.  SQLCMD can be used as a quick test for connectivity.  We’ll use docker exec to do connect to the container and run a query using sqlcmd.

  1. Run docker exec in interactive mode (-it).

 

 

 

 

 

Azure Data Studio

If the host port is allowed through the firewall then you can connect remotely using other tools.  We’ll use Azure Data Studio to connect.

    1. Grab the host IP.
    2. Open Azure Data Studio and create a new connection.
    3. Specify the host IP, port, user name, and password.  If port 1433 (default) is used, no need to add it to the connection properties.

 

After connecting, you can run queries, view the SQL agent jobs (if the SQL Server Agent extension is installed), and view the objects within the DBA database.

Summary

In this post, we created a customized docker image for SQL Server and built a container from it.  The more I work in this space the more intrigued I am.  Up to this point, all of the containers we’ve built have been ephemeral.  No data is retained once the container is removed.  I’ll show how to utilize persistent storage in an upcoming post.  A quick video walk-through is available below.  Thanks!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.