By: Edwin Sarmiento | Updated: 2019-04-02 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Containers
Problem
Our company is looking at evaluating Containers to modernize our existing applications, including our SQL Server databases. I have no prior knowledge of Containers. How do I get started with Containers to help me prepare for modernizing our SQL Server databases?
Solution
To continue this series on Introduction to Containers for the SQL Server DBA, you will look at exploring container images by understanding the Dockerfile and how you can create your own SQL Server containers. In Part 1, you have 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. Part 4 introduced you to the container naming conventions and internals of container images, configuration of persistent storage for your SQL Server containers and running multiple SQL Server containers on a single host machine. This time you will look at how you can create your own SQL Server containers.
Creating Custom Images
Most of the examples highlighted in previous tips used existing container images available on public repositories like Docker Hub. If you have specific configuration requirements for standardizing deployments such as having user databases or tools installed for development environments, the available public container images may not be enough. You have to build custom container images according to your standards.
There are two ways to create your own custom images – by using a Dockerfile or by creating from an existing container.
Working with Dockerfile
A Dockerfile is a text document that contains all the commands a user could run on the command line to create a custom image. Using the same baking cake as an analogy, if the image is the recipe, the container as the cake, Dockerfile is the list of ingredients. You can think of it as a script, composed of various commands/instructions and arguments listed sequentially to automatically perform actions on a base container image to create a new one. Dockerfile defines what goes on in the environment inside your container – from the application that runs inside the container to the networking ports that the application will listen to. In this case, SQL Server is the application. Once the image is built, you can run as many containers as you want, like how you would build a golden virtual machine image for standardizing deployments. It is also a good documentation tool for those who want to understand how the image was built.
But before you can create your own custom container images, you need to understand the contents of the Dockerfile and how you can use them. Use the instructions on the tip Install and Configure SQL Server 2017 Availability Groups on Linux - Part 1 to create your custom SQL Server 2017 on CentOS Linux container.
The file
The file needs to be specifically named Dockerfile – with no file extension and with an uppercase D. You can create the file on Windows using your favorite text editor but make sure you remove the .txt extension when you save it. Once completed, you can copy it to a folder on the machine where you have the Docker client installed.
NOTE: Do not use the root directory to store the Dockerfile. Create a folder to store it into together with your application-specific files.
FROM Instruction
A Docker file must start with a FROM instruction. The FROM instruction defines the base image you would like to start from, typically the base image of your target operating system. It initializes a new build stage and becomes the reference for succeeding instructions in the Dockerfile. Recall that most of the SQL Server on Linux containers available on public repositories run Ubuntu Linux. If you want to build a SQL Server 2017 on CentOS Linux image, the FROM instruction needs to specify that base image, including the necessary tags to reference a specific version.
FROM centos:7.6.1810
If the base image defined in the FROM instruction does not exist locally on the Docker host, it will be downloaded from the Docker Hub before the custom image gets created.
LABEL Instruction
The LABEL instruction - a key-value pair - adds metadata to an image. This is for documentation purposes and helps define the custom image to whoever will inspect and use it. Labels can be used to define what the image is about, who the vendor is, the version of the application, release date, a short description and the likes. You can write a LABEL instruction in multiple lines like the example below.
LABEL name="microsoft/mssql-server-linux" LABEL version="14.0" LABEL operating_system="centos:7.6.1810" LABEL environment="production" LABEL maintainer="[email protected]"
RUN Instruction
The RUN instruction will execute a command in the container and is one of the most common instructions you will see in a Dockerfile. The RUN instruction has two formats:
- RUN <command> (shell form, the command is run in a shell, which by default is /bin/sh -c on Linux or cmd /S /C on Windows). You can also call the PowerShell command shell on Windows.
- RUN ["executable", "param1", "param2"] (exec form – calls the executable directly without shell processing)
The RUN instruction will contain commands depending on what you want to run as part of building your container image. For example, when creating a custom SQL Server 2017 on CentOS Linux container, you need to:
- download the Microsoft SQL Server Red Hat repository configuration file
- download and install the SQL Server installation packages for RHEL
The equivalent RUN instructions for these two are as follows:
RUN curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo RUN yum install -y mssql-server
ENV Instruction
The ENV instruction – also a key-value pair – sets the environment variables for all the subsequent instructions in the build stage. These environment variables will persist when a container is run from the resulting image. The environment variable defined below will set the Linux PATH to tell the shell which directories to search for executable files in response to subsequent commands defined in the Dockerfile, in this case, the default directory for the SQL Server process.
ENV PATH=${PATH}:/opt/mssql/bin
Any command or instruction executed in the Dockerfile after this line will use the /opt/mssql/bin (the SQL Server processes’ default folder) directory. Should you decide to run another process from a different directory, you either define another ENV instruction or simply provide the complete path.
Additional RUN instructions are needed to run SQL Server on a Linux container. For example, the /var/opt/mssql/data directory does not exist at this point since the mssql-conf utility was not used as part of the installation. Therefore, a RUN instruction that creates the directory is provided as follows:
RUN mkdir -p /var/opt/mssql/data
Plus, similar to granting the proper folder permissions to the SQL Server service account on Windows, the SQL Server process account needs to have permissions to access the /var/opt/mssql directory and the /etc/passwd file. The /etc/passwd file is used to keep track of every registered user that has access to a system and contains information such as the user account, password hash, home directory, group ID and the likes. The corresponding RUN instruction for this is provided as follows:
RUN chmod -R g=u /var/opt/mssql /etc/passwd
The parameter g=u means to grant group permissions to be same as the user’s (the SQL Server process account mssql).
EXPOSE Instruction
The EXPOSE instruction informs Docker that the container listens on the specified network ports at runtime. This does not actually publish the port but rather to inform users about the ports that the application is listening on when running on a container. The default port number for SQL Server is 1433.
EXPOSE 1433
CMD Instruction
The CMD instruction allows you to set a default command and default parameters which will be executed only when the container is run. When you don’t provide a command when running a container, this instruction is executed. However, this can be ignored should you choose to run a container with a specific command.
The CMD instruction has three formats:
- CMD ["executable","param1","param2"] (exec form, this is the preferred form)
- CMD ["param1","param2"] (as default parameters to ENTRYPOINT)
- CMD command param1 param2 (shell form)
You can have multiple CMD instructions in a Dockerfile but only the last one will be executed. Hence, it makes sense to properly plan the custom image that you are building. Decide on the command that you want to run by default when a user runs the container.
The way SQL Server runs inside a container is by running the SQLSERVR process from the opt/mssql/bin directory, which is unlike running SQL Server on a full-blown Linux operating system where you start the SQL Server daemon/service. Run the SQL Server process when the custom container is run using the instruction below.
CMD sqlservr
CMD versus RUN
A common confusion when creating Dockerfiles is the availability of different instructions that may seem redundant. Take the CMD and RUN instructions, for example. The form and functionality may look the same. But how are they different?
The RUN instruction executes command(s) in a new layer and creates a new image. When you look at the example provided, the RUN instruction was used to download the Microsoft SQL Server Red Hat repository configuration file and install the SQL Server installation packages for RHEL. These RUN instructions added files to the existing image and created a new image layer.
The CMD instruction, however, sets a default command and/or parameters, that only runs when you run the container.
So, when deciding which one to use, just remember that the RUN instruction is used when building the image while the CMD instruction is used when running the container.
Your final Dockerfile should look something like this.
FROM centos:7.6.1810 LABEL name="microsoft/mssql-server-linux" LABEL version="14.0" LABEL operating_system="centos:7.6.1810" LABEL environment="production" LABEL maintainer="[email protected]" RUN curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo RUN yum install -y mssql-server ENV PATH=${PATH}:/opt/mssql/bin RUN mkdir -p /var/opt/mssql/data RUN chmod -R g=u /var/opt/mssql /etc/passwd EXPOSE 1433 CMD sqlservr
Building Your Custom SQL Server on Linux Container Image using Dockerfile
Once you’ve created the Dockerfile, save it inside a folder where you have the Docker client installed. In this example, a folder named dockerBuild is created.
Use the docker build command to build your custom SQL Server on CentOS Linux container image from the Dockerfile.
docker build -t sql2017centos:1.0 .
The -t parameter describes the name and, optionally, a tag for this image. The 1.0 tag indicates that this is version 1. You can use any value for this. Just make sure it is intuitive enough for yourself and other users. The dot (.) after the name describes the path where the Docker client will reference when building the image, in this case, the current working directory. If you have included other files like database backups as part of the custom image, they must be in the same folder as the Dockerfile for them to be included in the build.
As this command runs, the Docker client will send everything in the current working directory to the Docker daemon and include them as part of the build process. It will read the Dockerfile and follow the instructions sequentially to create your custom image.
Notice that each line in the Dockerfile corresponds to a step when building the custom image. Also, each step seemed like it is creating a temporary container and then deleting it afterwards. What is happening here is that Docker will create an intermediary image by creating a temporary container for every step in the build process. Following the instructions in the Dockerfile, Docker will take the base image (centos:7.6.1810), then executes the RUN instruction to download the Microsoft SQL Server Red Hat repository configuration file. Docker will add the resulting files from that step as another layer on top of the base image. Next, it will download and install the SQL Server installation packages for RHEL and adds the resulting files from that step as another layer on top of the previous image. And so on until the last instruction in the Dockerfile is evaluated. The LABEL instructions were evaluated but did not necessarily create a persistent image layer.
Use the docker images command to review the custom image created.
docker images
Notice that it is slightly smaller (1.23GB) than the microsoft/mssql-server-linux (1.35GB) image from Microsoft that is available on Docker Hub. That’s because the publicly available image also contains the SQL Server command-line tools like sqlcmd and the unixODBC developer package whereas the custom image only contains the SQL Server database engine.
Use the docker inspect command to explore the image layers created with the custom image. Scroll down to the "Layers" section to see how many layers an image has. Each line represents an image layer.
docker inspect sql2017centos:1.0
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 – the instructions in the Dockerfile - and actions that took place to generate each layer that created the image.
docker history sql2017centos:1.0
Recall that all non-zero-sized commands together with the FROM, COPY, RUN and CMD commands generate a layer. If you count the number of non-zero-sized commands plus the first CMD command that runs the SQL Server process, you have a total of five (5) layers which is consistent with the number Layers when you run the docker inspect command.
Test the custom image by running the docker run command below.
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=y0urSecUr3PAssw0rd' -p 1415:1433 --name sql-linuxcon15 -d -h linuxsql15 sql2017centos:1.0
In the next tip in this series, you will continue with building your own custom SQL Server containers using the Dockerfile. But instead of running it on a Linux operating system, you will use a Windows Server operating system instead.
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
Article Last Updated: 2019-04-02
