SQL Server 2017 is the biggest release and most important releases in the Microsoft product history simply because of the freedom that it gives to install the product anywhere. The advent of the Docker containers and growing emphasis on open source solutions opened the door to greater technological innovation and advancement.
This article discusses the following topics:
- Introduction to Docker container
- Pre-requisite to build Docker container
- A walkthrough of the setup process to build a SQL Server instance running on the Linux CentOS and in a Docker container
- How to connect to the database engine
- How to connect to the database engine externally
- And more…
So let’s get started exploring the new-found Docker container.
Introduction
Docker provides an elegant platform for API packaging for container management. It eases out the complex design process of application packaging by incorporating the dependent components into the container image.
The support for Linux and Docker containers came with the release of SQL Server 2017, which opens up the various options for installing and working with SQL Server 2017 on those platforms. SQL Server 2017 is no longer a platform dependent of the database offering. Its footprint is widespread across other flavors of the operating system as well. This new capability allows developers and open source users to test the full-fledged SQL Server instance at a lower cost.
Prerequisites
- Docker Engine 1.8+ on any supported Linux distros or have an up-to-date version of Docker for Mac or Windows
- Min of 2 GB of dedicated disk space
- Min of 2 GB of RAM dedicated to Docker
- System requirements for SQL Server on Linux
In this quickstart, we will learn how to install the Docker on Linux distros and how to incorporate the SQL Server 2017 container image. In this case, Docker provides a platform to bundle only the required resources for SQL Server 2017 into a fully self-contained unit. In the case, the container will include only the pieces of the operating system that it required, including any drivers, system libraries, or other resources needed to make SQL Server instance fully functioning. This option keeps the size down since only the bare minimum components are included in the container.
This capability makes containers highly portable since they don’t have any external dependencies. It is possible to create a Docker image on one machine, then move or copy it to another and ensure that it’ll still work in the same way.
Microsoft offers a number of different container images that we can pull. They will be identified by tags during the installation process. In most cases, we’d specify a 2017-latest image to get the most current version of SQL Server 2017, but there is an option to pull an image with an earlier cumulative update as well. On this site, we can see the different tags that are available for the Docker images.
For the entire demo, I will be using the Docker Centos CE(Community Edition) container. For the production use, the documentation at this URL will help us to obtain the required licenses and images, if this applies to your situation.
Get started
To setup Docker on CentOS, download the latest release of the .rpm package and install it manually using the YUM command. You may need to download the latest file each time to upgrade the Docker.
- Browse the URL
https://download.docker.com/linux/centos/7/x86_64/stable/Packages/ to download the latest Docker version, – docker-ce-18.03.0.ce-1.el7.centos.x86_64.rpm.
- Login the console or user terminal with SU (Super User Mode).
- Prepare a directory /tmp/docker to download the rpm package.
- Download the rpm package using the wget utility.
# wget –directory-prefix=/tmp/docker/ https://download.docker.com/linux/centos/7/x86_64/stable/Packages/docker-ce-18.03.0.ce-1.el7.centos.x86_64.rpm
- Install Docker CE with changing the path below to the one that leads to the downloaded Docker package.
#cd /tmp/docker/ #yum install docker-ce-18.03.0.ce-1.el7.centos.x86_64.rpm
- You can see that Docker is installed.
- Now, Docker is installed but the service is not started. Use the systemctl command to start Docker, verify and enable the docker service.
# systemctl start docker #systemctl status docker #systemctl enable docker
- Verify that docker is installed correctly by running the welcome image.
#docker run welcome
So far we’ve set up with Docker container on CentOS.
Pull the SQL Server 2017 container image
Let’s pull the SQL server container image from Docker Hub https://hub.docker.com/r/microsoft/mssql-server-linux/.
On the right, copy the docker pull command to extract the SQL Server 2017 image. We can specify a specific image from the list of tags in the public repository on the docker hub. Append the colon and then 2017-latest in command only gives you the most current image for the SQL Server 2017 release. You can also just simply put latest without 2017 and the hyphen to download the latest available release.
#docker pull Microsoft/mssql-server-linux:2017-latest
Once the pull is complete, we can create a container from this image. We’ll do that with the sudo docker run.
The first parameter is accepting the end user licensing agreement. Use the -e parameter to configure and set the appropriate values, inside the single quotes, type ‘ACCEPT_EULA =Y’. Next parameter is setting the strong password for the SQL server instance. Again, use the -e parameter, inside the single quotes ‘MSSQL_SA_PASSWORD=<StrongPassword>’.
Then map a TCP port using the –p parameter. In this case, port 1401 (Host computer) to 1433(SQL Server) is a port that SQL server will listen to within the container.
dockAssign a unique name for the SQL Server container using the — name parameter. In this case, we’ve assigned an SQLServerDemo1 as a unique SQL Server container. Finally, reference the image using the -d parameter i.e. is Microsoft/mssql-server-linux:2017-latest.
Now, we can see that the container is created. We can see the status of all Docker containers with the command docker ps -a
#docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=thanVitha@2015' -p 1401:1433 –name SQLServerDemo1 -d microsoft/mssql-server-linux:2017-latest #docker ps -a
The parameters are discussed in the below table.
[table id=49 /]
At this point, your SQL Server Docker container should be up and running.
Now, connect and query the Docker container. Let’s log in and interact with SQL server inside of the container. We can connect and interact with SQL Server in a container in two ways one is from within the container and other is from outside the container.
Use the command docker exec –it to specify the name of the container to connect. In this case, it’s SQLServerDemo1. Then indicate the interactive shell that we want to use, which is bash, and this is simply the command language that we’re going to use for working with Linux.
#docker exec –it SQLServerDemo1 bash
Now, we can notice a prompt change. This indicates that we are inside of the Linux container. Since SQL Server 2017 is already installed, run the sqlcmd tool by specifying its path, type /opt/mssql-tools/bin/sqlcmd to pass the parameters to get connected to the SQL instance.
In the following example, the sqlcmd is used to connect to SQL Server. In this case, we did not specify the server in the connecting string that indicates that the connection is made to the container internally.
With SQL Server running in a Docker container, we can connect to the database engine from any computer that has the SQL connectivity support. Now, instead of logging directly into the container, we can connect to it from another computer. First, list the port that we mapped to 1433. In this case, it’s 1401. We can see the port details from the output of
#docker ps -a
The second piece of information is fetching the IP address of the docker container host. In the following example, the host machine IP address is selected using the ifconfig command and port 1401 is added to the connection string to connect the docker container explicitly.
In the following example, the SQL Server is connected using sqlcmd from the Linux machine to Docker container.
#sqlcmd 10.2.6.70,1401 -U sa -P thanVitha@2015
Connecting to SQL Server 2017 Linux Docker container from Windows machine
Run sqlcmd with the specification of the IP address and the port mapped to port 1433 of the container. In this example, that is port 1401 on the host machine port.
We can also connect the container from the SSMS tool and query the database and its related objects.
Wrapping Up
The speed and portability of containers should make Docker containers are more affordable, practical, and more easily tested. The entire setup is done in very few minutes. The steps are no different for Windows and Mac OS. Browse, https://www.docker.com/community-edition to download the respective libraries and start using it. It gives a platform an independent offering to develop and test their applications. In this article, we saw how the Docker container can be built on CentOS Linux machine. It truly gives freedom and platform independent offering for developers and administrators to deploy and connect any platform they wish.
Stay tuned for more updates…
References
- https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker
- https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup#system
- Introduction to Docker and Docker Toolbox
