By: Edwin Sarmiento | Comments (4) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Containers
Problem
To continue this series on Introduction to Containers for the SQL Server DBA, we will look at container naming conventions, the internals of container images, configuring persistent storage for SQL Server containers and running multiple SQL Server containers on a single host machine.
Solution
In Part 1, you learned how to install Docker on Windows Server 2016. In Part 2, you installed Docker on a Linux CentOS server. Part 3 walked you thru exploring the different docker commands that will help you get started on working with containers. This tip will introduce you to the container naming conventions and internals of container images, configuring persistent storage for your SQL Server containers and running multiple SQL Server containers on a single host machine.
Container Naming Convention
By default, the Docker client will connect to the Docker Hub to search, pull or push container images. However, you can tell it to use other public container registries other than Docker Hub. In order to do so, you need to understand the naming conventions used when working with container images.
Container images are referenced using a standard naming convention. An image name is made up of slash-separated name components, optionally prefixed by a registry hostname. The registry hostname must comply with standard DNS rules. In the previous tip, a registry hostname was not provided when a container image was pulled using the docker pull command. When no registry hostname is provided, a container image will be pulled from the Docker hub and the fully qualified hostname for Docker hub is docker.io. You can see this when you run the docker info command.
However, there are other public repositories available such as the ones from Microsoft, Amazon AWS, Google Cloud Platform and the likes. You need to know the corresponding hostnames of the public repository that you would like to use to pull image from.
After the registry hostname comes the repository (or repo, for short) name. The repo structure varies depending on how the images are stored on the container registry. In the case of Docker hub, since there are so many software vendors that use it as a public container registry, the first-level repo name is the name of the software company. For example, docker.io/microsoft is Microsoft's official repo on Docker hub whereas docker.io/ibmcom is IBM's.
After the repo name comes the image name. The image name is what identifies the contents of the container image.
Finally, there's the tag. The tag further identifies the content of the container images. Some might consider images and tags as one and the same. However, it helps to understand what they are used for, so you can leverage them when you build your own container images. SQL Server has multiple supported versions running on either Linux or Windows containers – SQL Server 2016, SQL Server 2017, SQL Server 2017 with additional updates installed, SQL Server 2019, etc. Similar to when you want to use a specific SQL Server version, you have to be explicit as to which one you need. This is where the tag comes in. By default, if the tag is not specified when pulling a container image, it will use the latest tag.
Refer to the docker pull command used in the previous tip to understand the container naming convention.
docker pull microsoft/mssql-server-linux
Using the container naming convention, this could have been written as,
docker pull docker.io/microsoft/mssql-server-linux:latest
NOTE: In May 2018, Microsoft introduced the Microsoft Container Registry (MCR) as the official container image registry for everything Microsoft-related. And because Docker hub is the go-to public registry for container images, it will still be the primary discovery point for all Microsoft containers - Docker hub for image metadata, MCR for images. Refer to this blog post for additional information.
You could also use the MCR's registry hostname as follows:
docker pull mcr.microsoft.com/mssql/server
However, you need to be explicit about using tags to pull the version that you need. Should you need a specific version, append the appropriate tag to the image name. The command below will pull the SQL Server 2017 on Ubuntu Linux with CU7 from the MCR.
docker pull mcr.microsoft.com/mssql/server:2017-CU7-ubuntu
Also, be sure to check the version of SQL Server running on the container images that you download when you do not specify a tag. The latest SQL Server on Linux image from Docker hub may not be the same as the latest SQL Server on Linux image from MCR. In the example below, the SQL Server on Linux container named sql-linuxcon01 was pulled from Docker hub while sql-linuxcon02 was pulled from MCR. However, sql-linuxcon01 has a higher version number than sql-linuxcon02.
Understanding proper container naming convention is important especially when you start creating your own container images and store them on either public or private container repositories.
Images versus Containers
A container image is a read-only template for creating application containers. It consists of a set of files structured in a file system layer that contains application files, operating system-dependent files like libraries that your application needs and metadata that describes what it is. Container images can be shared with other images and containers. If you look at the output of the docker pull command used in the previous tip to pull an image of the SQL Server on Linux container, you would see multiple lines, each one with the Pull complete message.
Each of the lines with a Pull complete message represents a file system layer of the image stacked on top of each other to create the full image. The image consists of multiple layers, leveraging the union file system (UnionFS) to combine these layers and make it look like a single image. You can explore the layers that make up the image (and other low-level information about the container) by using the docker inspect command. This will display a JSON array that describes the metadata of the image. Scroll down to the "Layers" section to see how many layers an image has. Each line represents an image layer.
docker inspect microsoft/mssql-server-linux
The number of Pull complete messages is the same as the number of layers in the image. You can use the docker history command to show the different layers inside the image and their corresponding sizes. It will also show you the different commands and actions that took place to generate each layer that created the image.
docker history microsoft/mssql-server-linux
All non-zero-sized commands together with the FROM, COPY, RUN and CMD commands generate a layer. Details of these commands will be covered in a future tip.
If you count the number of non-zero-sized commands plus the first CMD command that runs the SQL Server process (the second CMD command simply runs the bash shell), you have a total of nine (9) layers which is consistent with the number of Pull complete messages. You will get to see the layers that are created when you build your own container image.
A container is a runtime instance of an image, an execution environment for an application. Using baking cake as an analogy, you can think of an image as the recipe while a container is the cake. You can bake as many cakes (containers) as you want by following a given recipe (image).
Given the read-only nature of an image, it is a little bit confusing since containers run applications that modify data. How is this possible?
When you create a new container, a thin writable layer is added on top of the underlying stack of layers present in the read-only container image. Since the image is read-only, all changes made to the running container - such as creating new files, modifying existing files or deleting files - are written to this thin writable container layer. This is done using the copy-on-write process. When a file inside the image needs to be modified, it locates the file in the image, copy it on the writeable layer and modifies it there.
When a container is deleted, the corresponding writable layer is also deleted. Meanwhile, the base image remains unchanged. This concept is similar to Microsoft Hyper-V's differencing disks or VMWare's Linked Clones. You can create multiple virtual machines (containers) using the virtual hard drive of the parent virtual machine (image). Modifying or deleting the virtual machines does not affect the virtual hard drive of the parent virtual machine.
Containers are designed to be short-lived and the images immutable. This means that the images and running containers never change. If you want to update the application inside the container – for example, installing the latest SQL Server service pack – the best practice is to create a new image that has an updated version of the application, create a new container based on the new image and delete the old one. However, deleting the old container also deletes the writeable layer that contains all the changes made to the container. As SQL Server DBAs, data protection is a top priority. When you create a new database inside a SQL Server instance running on a container, the database files (MDF/NDF/LDF) get created on the thin writeable layer. Deleting the container also deletes the thin writeable layer together with the SQL Server database files – including the data. How do you deal with this?
Persisting Data with Containers
The preferred way to persist data that Docker containers generate and use is thru volumes. Volumes are Docker objects and are created and managed by Docker. They are directories (or files) that are outside of the container's union file system and exist as normal directories and files on the Docker host's filesystem – they live outside the container. You can think of them as volume mountpoints in Windows – it looks like a folder inside the operating system (container) but is external to it. With SQL Server containers, you can create a Docker volume, mount it on the container and use it to store the database files. If the container is deleted, the database files are kept in the volume.
By default, when you create a volume, Docker creates a folder on the host and is located in /var/lib/docker/volumes in Linux and C:\ProgramData\docker\volumes on Windows.
You can create a volume by using the docker volume create command. Be sure to specify a meaningful name or Docker will generate a random name for you.
docker volume create sqldbdata
You can inspect the volume metadata by using the docker volume inspect command. Notice the Mountpoint key pointing to the location of the folder in the Docker host.
docker volume inspect sqldbdata
Now that you have a Docker volume, you can mount it to a folder inside the container. This assumes that you already have a folder inside the image. Run the docker run command below to create a SQL Server on Linux container and mount the volume you just created to the /var/opt/mssql folder inside the container. Use the --mount parameter with the corresponding source and target to attach a filesystem mount to the container.
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=y0urSecUr3PAssw0rd' -p 1133:1433 -h linuxsql05 --mount source=sqldbdata,target=/var/opt/mssql --name sql-linuxcon05 -d microsoft/mssql-server-linux
NOTE: Previous versions of Docker use the -v parameter. You will see a lot of examples in old documentation that uses the -v parameter. The --mount parameter is mostly used for Docker Swarm services. Starting with Docker 17.06, the --mount parameter can now be used in standalone containers. The advantage of using the --mount parameter comes from being explicit with the use of key=value pairs, making the syntax easy to understand. Compare the two commands using the different options to mount a volume to a container.
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=y0urSecUr3PAssw0rd' -p 1133:1433 -h linuxsql02 -v sqldbdata:/var/opt/mssql --name sql-linuxcon02 -d microsoft/mssql-server-linux
The /var/opt/mssql folder is the default folder for SQL Server databases on Linux. Created SQL Server user databases will be stored in this folder.
Since this folder was mounted as a Docker volume, you can explore it from the Docker host as well using the ls command. The data folder is the standard DATA folder in SQL Server.
ls -l /var/lib/docker/volumes/sqldbdata/_data/data
Even if you remove the container, the data in the volume remains intact since the volume exists outside of the container. Remove the container using the docker rm command below. Use the -f parameter to force the removal of a running container.
docker rm sql-linuxcon02 -f
This makes it easy to update or upgrade SQL Server since the system and user databases are outside of the container. Simply delete the old container and create a new one with an updated version of SQL Server. Of course, this is not a substitute for proper backup processes when performing an upgrade.
Running Multiple SQL Server Instances on Linux?
One of the limitations when running SQL Server on Linux is its current support for running only a single (default, not named) instance per server. This becomes a challenge when you want to move from a multi-instance SQL Server on Windows installation on a single host to SQL Server on Linux. Since multiple SQL Server instances on a single Windows Server machine is simply a matter of assigning different port numbers using a single IP address of the host machine, it is possible to run multiple SQL Server on Linux containers on a single Docker host. This is done by mapping the Docker host's port to the container's port using the -p parameter of the docker run command. The two docker run commands below creates two SQL Server on Linux containers on the same Docker host but using different port numbers. The port mappings and container names are shown below.
| Container Name | Server Name | Host Port Number | Container Port Number |
|---|---|---|---|
| sql-linuxcon10 | linuxsql10 | 1410 | 1433 |
| sql-linuxcon11 | linuxsql11 | 1411 | 1433 |
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=y0urSecUr3PAssw0rd' -p 1410:1433 --name sql-linuxcon10 -d -h linuxsql10 microsoft/mssql-server-linux
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=y0urSecUr3PAssw0rd' -p 1411:1433 --name sql-linuxcon11 -d -h linuxsql11 microsoft/mssql-server-linux
In the next tip in this series, you will continue exploring container images by understanding the Dockerfile and how you can create your own SQL Server containers.
Next Steps
- Review the following documentation on Docker
- Review the previous tips on running SQL Server on Docker
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
