Docker Commands with Examples for SQL Server DBAs - Part 3

By:   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Containers


In our previous tips we look at how to setup Containers on Windows and Linux.  In this tip we will introduce several docker commands that you can use to manage and work with Containers both on Windows and Linux.


To continue this series on Introduction to Containers for the SQL Server DBA, we will look at exploring the docker command while diving deeper into the container ecosystem. 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. This tip will introduce you to working with the docker command to perform the most common tasks when working with containers.

Most Common Docker Commands

Working with containers and Docker means knowing the most common tasks you will be performing. This also means knowing the corresponding docker commands to accomplish these tasks. The docker command is the base command for the Docker command line interface (CLI). Every task you will perform will start with the docker command followed by subcommand, depending on the task, together with the corresponding parameters.

Below are the most common tasks with their corresponding docker commands and their usage. To illustrate the interoperability of the docker command, screenshots will be either from Windows Server 2016 or from Linux CentOS command shell.

NOTE: The sample commands are kept as simple as possible, without the inclusion of sudo (for the Linux platform) to keep it as platform agnostic as possible. That doesn’t mean you’ll ignore the security practices of running commands on both the Windows Server and Linux server operating systems.

Also, when running the docker commands on Windows Server, use the PowerShell command shell and not PowerShell Integrated Scripting Environment (ISE). PowerShell ISE does not provide an interactive shell.

Exploring the different Docker commands

Being new to the environment, you need all the help you can get to make the most out of your learning experience. You can begin by simply typing docker on the command line. This will display all the available subcommands that you can use with the docker command.

Exploring the different Docker commands

Alternatively, you can also use the help subcommand.

docker help
docker help command

You can use this with any subcommands that you use by adding the double minus symbol, for example, docker subcommand --help.

Identifying the Docker version and system information

Much like when working with SQL Server, the first thing I do when reviewing a new SQL Server instance is identifying the version (and sometimes edition) of the SQL Server instance I’m working on. This is no different with Docker. In the first two tips in this series, you’ve seen how you can use the command below to identify the version of both the Docker engine and the Docker client installed on a machine. By default, both the client and the engine/server are installed on the machine during the installation process.

docker version 
Docker Version Command

You’ve also seen how to display system-wide information regarding the docker installation on the server using the command below.

docker info
Docker Info PowerShell command

Running a Docker container

To get started with running containers, run the command below.

docker run hello-world

The docker run command runs a new container in an isolated process, meaning it has its own file system, its own networking, and its own isolated process tree separate from the host.

The hello-world container image gives you a high-level overview of what is happening when you run a Docker container. The screenshots below are for both Linux and Windows Server operating system, respectively.

Docker Hello-World
Running Hello World in Docker

How Docker runs containers

Let’s look at the numbered steps displayed in the output of the hello-world container to understand how Docker runs containers.

  1. A machine running the Docker client and engine is ready to run tasks.
  2. You run the docker run hello-world command from the Docker CLI client.
  3. The Docker client makes API calls to the Docker daemon, passing the command to run a new container using the hello-world container image as the template (#1 in the output).
  4. The Docker daemon checks to see if it already has a copy of the hello-world container image in its local file system (also called the local container registry). In this case, because it is a brand-new installation, it doesn’t have a local copy of the container image.
  5. The Docker daemon searched and pulled the hello-world container image from the Docker Hub – a public repository of container images (#2 in the output). Pull is just a term used to mean download. The Docker daemon needs a local copy of the image to start a container.
  6. After downloading the hello-world container image, the Docker daemon created and ran a new container based on the image (#3 in the output).
  7. After performing the tasks configured in the hello-world container image, the container exited or entered in a Stopped state (#4 in the output). This leaves a local copy of the hello-world container in the local container registry

Introducing Docker Hub and Container Registries

Docker Hub – a container registry - is a cloud-based, public repository for creating, testing, storing and distributing container images. You can pull container images from software vendors such as Microsoft, Oracle, RedHat, IBM, WordPress, etc. depending on the ones that you need.

Docker  Hub

By default, the Docker client will connect to the Docker Hub to search, pull or push (a term used to mean upload) container images. You can configure it to use other public container registries other than Docker Hub.

Since your focus is on SQL Server running on either Windows or Linux containers, you want to limit the search to only those types of containers. Alongside with pulling and running SQL Server container images, you need to be aware of the associated licensing provisions. Refer to the SQL Server 2017 licensing guide for more details.

While you can use the website to search for the name of the image, running the docker search command below to search the Docker Hub for container images from Microsoft is more efficient.

docker search microsoft
docker search microsoft

The result is a bit overwhelming since it includes all the Microsoft-related container images available on Docker Hub. You only want the SQL Server-specific container images.

Run the command below – on Linux, with the grep command - to search the Docker Hub for SQL Server-specific container images from Microsoft.

docker search microsoft | grep sql
SQL Server Dockers

Run the command below – on Windows, with the Select-String PowerShell cmdlet - to search the Docker Hub for SQL Server-specific container images from Microsoft.

docker search microsoft | Select-String sql 
docker search microsoft Select-String sql

NOTE: You should be knowledgeable with native Linux or Windows/PowerShell commands, so you can leverage them while working with the different Docker commands.

Running the SQL Server on Linux and Windows containers

Run the docker pull command below on Linux to pull the official latest SQL Server 2017 on Linux container image. This will not create and run a new SQL Server on Linux container, it will just download the image to the local container registry.

docker pull microsoft/mssql-server-linux
docker pull microsoft/mssql-server-linux

Similarly, run the docker pull command below on Windows to pull the official latest SQL Server 2017 Developer Edition on Windows container.

docker pull microsoft/mssql-server-windows-developer
docker pull microsoft/mssql-server-windows-developer in PowerShell

NOTE: The SQL Server on Linux container image (1.35GB) is much smaller than the SQL Server on Windows container image (15.1GB) and, hence, will be faster to download.

Size of the docker container image
docker images in PowerShell

Alternatively, you can use the docker run command to pull and run container images at the same time since the Docker engine will download the image anyway if it doesn’t yet exist in its local container registry. However, the docker run command necessary to run the SQL Server container images will include additional parameters.

Use the docker run command below on Linux to create and run a SQL Server on Linux container.

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=y0urSecUr3PAssw0rd' -p 1433:1433 --name sql-linuxcon01 -d -h linuxsql01 microsoft/mssql-server-linux 

Use the docker run command below on Linux to create and run a SQL Server on Windows container.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=y0urSecUr3PAssw0rd" -p 1433:1433 --name sql-wincon01 -d -h winsql01 microsoft/mssql-server-windows-developer 

Both commands use the following parameters:

  • -e: ENVIRONMENT VARIABLES. These are specific to SQL Server. The ACCEPT_EULA and SA_PASSWORD parameters are self-explanatory.
  • -p: Publish a container's TCP port (or a range of ports) to the host using this format ip:hostPort:containerPort. This will allow you to connect to the SQL Server instance in the container remotely, mapping port 1433 on the host to port 1433 on the container. Using this format, accessing the container remotely means using the host’s IP address.
  • --name: A unique custom name to help identify the container instead of a system-generated one. This helps you easily identify the container to perform additional tasks
  • -d: Run the container in detached mode. This means that after running the docker run command, the container still runs in the background. Use this parameter since SQL Server runs as a service.
  • -h: Server hostname that you want to assign to the container

Once the SQL Server container is up and running, you can connect to it remotely using SQL Server Management Studio. Use the IP address of the Docker daemon host in the Server Name field.

Looking at the output of the query,

  • SQL Server on Linux container defaults to Developer Edition. It also runs Ubuntu Linux
  • SQL Server Developer Edition on Windows container runs Windows Server 2016 Core Datacenter Edition
  • SQL Server 2017 on Linux container (14.0.3048.40) has a much later version than the SQL Server on Windows container (14.0.3015.40)
  • The SQL Server container images are configured with a default instance
Default SQL Server Instance

You can use the docker ps command to list the container that you just created. By default, this command only shows running containers.

docker ps
docker ps command
docker ps command in PowerShell

Use the output of the docker ps command to explore the different properties of the containers.

Running commands inside running containers

It’s really not advisable to connect remotely into a running container and manage it like your typical server operating system. Containers are conceptually designed to run a single process and performs a single task, particularly the Linux containers. Even so with SQL Server. You want to deploy the container using best practices configuration for the server operating system layer and just manage it via SQL Server Management Studio or any SQL Server client tool. However, should you need to explore and interact with the container, you can do so. Run the docker exec command below with the -it parameter to run a command inside a running container.

NOTE: The command that you want to run should be appropriate to the underlying operating system kernel. Don’t expect to run an interactive PowerShell command shell inside a Linux container unless you’ve explicitly created it with PowerShell Core installed.

On Windows:

docker exec -it sql-wincon01 powershell

The docker exec command will start an interactive PowerShell command on the sql-wincon01 container and attach it to PowerShell session that started the docker exec command. From there on, you can run the native PowerShell cmdlets to explore the container. Below, you’ll see that the sql-wincon01 container created from the microsoft/mssql-server-windows-developer image runs Windows Server 2016 Core Datacenter Edition.

Windows Edition

On Linux:

docker exec -it sql-linuxcon01 bash 

Below, you’ll see bash session that ran started the docker exec command change from the current Linux host to the Linux container.

docker exec command change from the current Linux host to the Linux container

You can run the exit command – applicable to both Windows and Linux – to exit the current session running on the container and return to the original calling session.

In the next tip in this series, you will continue to learn more about the internals of container images and how to create your own SQL Server containers – be it on the Windows Server or Linux operating systems.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Edwin Sarmiento 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

Comments For This Article

get free sql tips
agree to terms