Microsoft SQL Server Containers

I’m a Database Administrator, why am I learning about containers?

So, it seems containers are here to stay.  Before the release of SQL Server 2017, I really hadn’t paid attention to this space.  The thinking was I’d never need to know about any other OS except Windows Server.  This all changed when Microsoft decided to release SQL Server on Linux.  I began to panic.  I’ve intentionally focused on the Windows platform, and of course SQL Server, over the past 11 years.

Resistance is futile – Instead of waiting for SQL Server on Linux (or containers) to become widely adopted I decided to begin learning both Linux and containers.  I’ll be creating a series of blog posts as I start down this journey.  Each will be related to SQL Server, docker, and Linux.

What is a container?

Docker describes a container as being “a standard unit of software that packages up code and all its dependencies so the application runs quickly and reliably from one computing environment to another.”  They are lightweight and do not require their own OS.  For a full description, see https://dockr.ly/2PYAbPy.

SQL Server and Containers

Microsoft supports running SQL Server within Linux containers beginning with SQL Server 2017.  There are several use cases for running SQL Server within containers (a major one being CI/CD for dev and QA environments).  When you add on container orchestration platforms, such as Kubernetes, things get real interesting (ability to move containers to new hosts, scale up, scale down etc).  As I progress through this path I’ll be setting an Openshift (OKD) cluster.

Getting started

I’m using Red Hat Enterprise Linux 7.5.  If you choose to use this distro, start with a Red Hat Developer subscription.  For more details, see https://developers.redhat.com/.

Proxmox is the hypervisor used within my lab environment.  Each of the commands listed below are being run while logged in as root for simplification (I know, this isn’t best practice).

  1. Setup a Red Hat Developer subscription.
  2. Download RHEL.
  3. Create a VM, or install on bare metal, using the RHEL iso.
  4. Configure networking and the hostname.
    1. I used the ntmui utility to do this.  See here for details.
  5. Add the server to your subscription.  This allows you to utilize Red Hat repositories, pull updates, etc using yum.  As root, or sudo, run the following:
  6. Once registered, pull down the latest updates using yum. (What is yum?  https://access.redhat.com/solutions/9934)
  7.  Install Docker.
  8. Start the docker daemon, set to run automatically on boot, and check the status of docker.
  9. Check the docker version
    1. Example output from docker version.

 

 

 

 

 

Docker is installed, now what?

With docker installed we can pull an image of SQL Server using “docker pull.”   We’ll grab both a SQL Server 2017 image and SQL Server 2019 (CTP 2.0).

SQL Server 2017 (dev edition)

  1. Check the available docker images:

 

Create a container using the 2017-latest image

  1. Verify the container is running by entering “docker ps” at the prompt and pressing enter.  You should see the following information returned:
    1. CONTAINER_ID – Unique ID
    2. IMAGE – Image used to create the container
    3. COMMAND – running command within the container
    4. CREATED – Time the container was created
    5. STATUS
    6. PORTS – Defined by the -p argument used within the docker run command.  We’ve defined the port as 1433:1433 (host is listening on port 1433 and passing traffic to port 1433 within the container).
    7. NAMES – Container name.
  2. Connect to the SQL Server instance using SSMS, Azure Data Tools, or sqlcmd.  You’ll need to connect to the host IP over port 1433 for this example.

 

 

 

 

 

 

SQL Server 2019 CTP 2.0 (RHEL image)

  1. Check the available docker images:

 

 

Create a container using the vNext-CTP2.0 image

  1. Verify the container is running by entering “docker ps” at the prompt and pressing enter.  You should see the following information returned:
    1. CONTAINER_ID – Unique ID
    2. IMAGE – Image used to create the container
    3. COMMAND – running command within the container
    4. CREATED – Time the container was created
    5. STATUS
    6. PORTS – Defined by the -p argument used within the docker run command.  We’ve defined the port as 5000:1433 (host is listening on port 5000 and passing traffic to port 1433 within the container).
    7. NAMES – Container name.
  2. Connect to the SQL Server instance using SSMS, Azure Data Tools, or sqlcmd.  You’ll need to connect to the host IP over port 5000 for this example.

 

 

 

 

 

Docker Logs

docker logs fetches the logs available within a particular container.  Very useful if you can’t connect via SSMS and you need to review the SQL Server error log.

Cleanup

To cleanup our host we’ll stop each container, remove the container, and then remove the images.  One thing to note, once you remove a container all associated data is also removed.  By default, a container does NOT persist data if it is removed (docker rm).  I’ll revisit this in an upcoming post in which we can ensure any databases created are persisted in the event a container is removed.

Summary

In this post, we’ve installed docker, pulled down images for both SQL Server 2017 and 2019, and created two containers.  You might have noticed the SQL Server agent is not running within these containers.  The images hosted within the mcr.microsoft.com repository do not have the agent enabled.  In the next post, we’ll build our own image, using docker build and a Dockerfile, which includes the SQL Server Agent, full text indexing, and client tools.

 

References

Docker command line reference – https://docs.docker.com/engine/reference/commandline/docker/

Official Microsoft repository for SQL Server in Docker resources – https://github.com/Microsoft/mssql-docker

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.