Deploying a SQL Server Docker Container

Total: 16 Average: 3.4

Introduction

Containerization has become the dominant topic after the Server Virtualization and Cloud Computing Eras. While many thought leaders assert that current computing paradigms are not particularly new, containerization, as we have it today, can be viewed as having been born after cloud computing. Using containers on-premise allows us to perform migration to the cloud while building a containerized environment mimics the features of solutions deployed in the cloud.

Progression of Computing

Fig. 1 Progression of Computing

Docker is the most popular container offer in the world and it became popular around 2013 largely because it was an open-source project and because the company provided a set of additional tools to go with the product. Docker containers are based on Linux containers which came on the scene earlier in 2008. Microsoft first embraced containers in June 2015. In October, 2016, Microsoft announced the availability of SQL Server 2016 in Docker containers. During the course of this article, we will deploy a SQL Server 2017 Docker image in an AWS EC2 running Amazon Linux.

Deploying an Amazon EC2

We start our exercise by launching an Amazon EC2 instance on our AWS dashboard. We have to choose an instance size that will be able to run SQL Server. For the purposes of this article I have chosen the t2.medium. The t2.medium comes with 2 Virtual CPUs and 4GB of RAM. In order to identify this EC2 instance, I assigned it with the name SQLServerAmazonLinux. You might have guessed by now that my EC2 is running Amazon Linux.

Deploying an EC2

Fig. 2 Deploying an EC2

Assessing the Environment

In the background, I have configured my AWS Security Groups to allow me to access the EC2 using both SSH and eventually SQL Server Management Studio. This means that I have opened ports 22 and 1433 respectively. Once I’m logged in, I can update the operating system using Linux commands:

sudo su –
yum update

Accessing the EC2 via SSH

Fig. 3 Accessing the EC2 via SSH

I then proceed with installing Docker and starting the service. I would do a few basic checks using OS and Docker commands. A full list of Docker commands can be viewed on the system by simply typing the command docker.

yum install docker
systemctl start docker
docker version
docker info
docker images
docker ps 
docker ps -a

Installing Docker Using YUM

Fig. 4 Installing Docker Using YUM

The docker version command displays basic version information while the docker info command displays more details about the entire system. Such information along with the OS version information is useful because SQL Server has minimum requirements in terms of both the host OS and the Docker engine. This is available in Microsoft Documentation.

Checking the Docker Version

Fig. 5 Checking the Docker Version

Displaying Detailed Docker Information

Fig. 6 Displaying Detailed Docker Information

The docker images command lists all available images on the system while the docker ps command lists containers you have created. Adding the –a switch to the ps command will show all containers whether they are running or not.

Displaying Image and Container Information

Fig. 7 Displaying Image and Container Information

Downloading the Deploying Docker Images

The docker search command is used to list images publicly available on Docker Hub. docker pull is used to retrieve images from a registry on the Internet (Docker Hub or elsewhere).

Displaying Images in Online Repositories

Fig. 8 Displaying Images in Online Repositories

Using the following command, we can retrieve a SQL Server 2017 image from Microsoft’s repository:

docker pull mcr.microsoft.com/mssql/server:2017-latest

The image is identified with its name as well as a TAG which is specified after the colon. After pulling the image, we can see that our list of images has been updated (see Fig. 10)

Downloading a SQL Server 2017 Image

Fig. 9 Downloading a SQL Server 2017 Image

Updated Docker Image List

Fig. 10 Updated Docker Image List

Now that we have the image downloaded, we can invoke the docker run command to create a container and run a command in it. The following code will create a docker container using the specified image. In the same code, we will provide the SA password (using the –e switch) and ask Docker to run this command in the background (the –d switch).

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Passw0rd#1" -p 1433:1433 - name sql1 -d mcr.microsoft.com/mssql/server:2017-latest

After doing this, we can see that our output for docker ps will change, now listing a running container. Recall that the ps command on its own only displays running containers. If your container crashed for any reason, you will need to use the –a switch to see it on the list. Notice that the output of this command gives us information about the container such as its ID (which translates to the hostname/instance name as we will see later), the command we are running within the container (invoking sqlserver.exe), the time when the container was created, and how long it has been up.

Updated Running Container List

Fig. 11 Updated Running Container List

Accessing the SQL Server Instance

Using the docker exec command, we can log in to the container now hosting our SQL Server instance and interact with SQL Server using SQLCMD. When running the docker exec command, we specify that we want to run the container interactively (-it) as well as the container name and Linux shell we prefer. Notice the subtle change in the hostname when we run this command (Fig. 11).

sudo docker exec -it sql1 "bash"

Inside the Container

Fig. 12 Inside the Container

The following line is a call to SQLCMD, passing the credentials we need to log in to the instance. In terms of security, it is recommended to change the SA password as soon as the deployment is done because this password can be retrieved from the Linux environment hosting the container.

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "Passw0rd#1”

Inside the SQL Server Instance

Fig. 13 Inside the SQL Server Instance

Note that we can also access the SQL Server instance from outside the host using tools like SQL Server Management Studio. In such a scenario, we have to supply the hostname or IP address of the server hosting the container.

Logging on Using SSMS

Fig. 14a Logging on Using SSMS

Logging on Using SSMS

Fig. 14b Logging on Using SSMS

Conclusion

We have seen in this article a simple way to set up a SQL Server instance using Docker Containers. Containerization is changing the world of computing in a similar way that technologies such as virtualization and cloud computing did. The focus of a typical DBA is changing and will change even more as we advance into the future. DBAs must now learn new skills such as Cloud Computing, Containers, and Container Orchestration to remain relevant in IT.

References

  1. SQL Server Express Edition in Windows Containers

  2. SQL Server Quickstart on Docker

  3. SQL on Docker Issues

  4. SQL Server Containers
Kenneth Igiri

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud. Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.