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.
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.
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
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
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.
Fig. 5 Checking the Docker Version
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.
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).
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)
Fig. 9 Downloading a SQL Server 2017 Image
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.
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"
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”
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.
Fig. 14a 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.
