There are several Microsoft SQL Server container images available for download. These consists of SQL Server 2017 and now SQL Server 2019 CTP2.1. Most of those can be found here. However, what if you need to customize an image by adding additional features? Docker provides a command line utility to build new images using Dockerfiles. A Dockerfile is a text document that contains all the commands a user could call on the command line to assemble an image. For more information see here.
Using docker build, we’ll create an image based on the Red Hat Enterprise Linux OS. The Dockerfile we’ll use for all examples can be found in the https://github.com/sqlservermigrations/containers repository. Using Git, we’ll clone the repo and build the image.
- You will need access to the Red Hat Container Catalog via a Red Hat subscription. A free developer subscription is available.
- You’ll need a Red Hat Enterprise Linux VM (or physical machine) that has been registered to your subscription. The Red Hat Container Developer Kit can be utilized as well (method not shown here).
- If using a RHEL VM or physical machine, Docker must be installed.
- git is needed to clone this repository.
- Prior to building the image, the password within the setup-tools.sh file, line 9, must be set to the same password used when building the image.
Building the image using a RHEL VM
- SSH to the RHEL VM
- Verify docker is running.
Shell1sudo systemctl status docker
- If the status does not show active, start docker.
Shell1sudo systemctl start docker
- Make a directory to clone the repository to and then clone using git. If git isn’t installed, use
sudo yum -y install git to install.
Shell123mkdir ~/sqlservermigrationscd ~/sqlservermigrationsgit clone https://github.com/sqlservermigrations/containers
- Build the image. This will take approximately 10 – 15 minutes (maybe longer) depending on your internet connection. -t is used to name and tag the image (registry/name:tag). For full list of options which can be used with docker build, see here.
Shell12cd ~/sqlservermigrations/containerssudo docker build . -t sqlservermigrations/mssqlserver:2019_CTP2.1
- Verify the image was created successfully.
Shell1sudo docker images
Starting a mssql-server instance
docker run to create a container from the new image. Docker run creates a process, known as a container, is isolated, contains its own file system, has its own networking, and has its own isolated process tree separate from the host.
Shell1sudo docker run -e TZ=America/Chicago -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourStrongP@ssword' --name mssql2019 -p 1433:1433 -d sqlservermigrations/mssqlserver:2019_CTP2.1
- The command finishes and returns the unique container ID. Next, run
sudo docker ps (docker ps)to ensure the container is running.
The docker run command above uses several parameters:
- -e – The image requires at least two environment variables to be provided. Those are provided via the “-e” flag.
- ACCEPT_EULA – Must be equal to Y.
- SA_PASSWORD – Must be set to a strong password.
- Full list of environment variables can be found here.
- –name – Provides a name for the new container. Otherwise all references made to the container in proceeding steps would need to reference the container ID.
- -p – Specifies the port in which SQL Server is listening on internally and the host port to listen on. For example, if you wanted to run multiple containers you’d specify a different host port for each (5002:1433). If connecting remotely be sure any firewalls on the host are configured to allow traffic on the specified host port.
- -d – Starts the container in detached mode.
Viewing the container logs
- Run docker logs and provide the name of container.
Shell1sudo docker logs mssql2019
- This should print out the SQL Server error log.
To filter for a specific text within the log, pipe the results to grep. We’ll see how many CPU cores SQL Server is using.
sudo docker logs mssql2019 | grep "sockets"
The screenshot above shows SQL Server has detected 2 sockets with 8 cores per socket.
Connect to Microsoft SQL Server running in a container
This particular image includes the mssql-tools package. SQLCMD can be used as a quick test for connectivity. We’ll use docker exec to do connect to the container and run a query using sqlcmd.
- Run docker exec in interactive mode (-it).
Shell1sudo docker exec -it mssql2019 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P YourStrongP@ssword -Q 'SELECT @@VERSION'
Azure Data Studio
If the host port is allowed through the firewall then you can connect remotely using other tools. We’ll use Azure Data Studio to connect.
After connecting, you can run queries, view the SQL agent jobs (if the SQL Server Agent extension is installed), and view the objects within the DBA database.
In this post, we created a customized docker image for SQL Server and built a container from it. The more I work in this space the more intrigued I am. Up to this point, all of the containers we’ve built have been ephemeral. No data is retained once the container is removed. I’ll show how to utilize persistent storage in an upcoming post. A quick video walk-through is available below. Thanks!