First look at SQL Server on Docker

Goal

After finishing my previous quick experiment with getting Docker up and running, I immediately wanted to experiment with SQL Server portability using Docker. The overal long term goal is to have standard docker images that all developers on the team could use for development and to assist with the onbording process of new developers to our team. By the end of this article, I will show how I used resources provided by Microsoft to successfully get SQL Server running in a Docker container and be able to connect to the instance from outside the container, and from a different machine. Here we go!

Microsoft container images

Thankfully, Microsoft has provided a big helping hand to get started with SQL Server on Docker. For Linux, the provide a standard SQL Server container image and instructions on how to get started. I followed this quick start guide as closely as possible for my setup.

It is so easy to get started on a new project or technology and skip right past the ‘prerequisites’ sections of a tutorial or documentation. I do it all the time, but I am trying to get in a better habit of taking the time to verify everything is in order before beginning. At the very least I learn a new command or location for where something is stored.

I initiallly used the same previous Amazon Liux AMI that I used when first setting up my first exposure to Docker, and while I know that the version of Docker is compliant with the container image from Microsoft, it never hurts to get in the habit of double-checking yourself.

$ docker version

I also wanted to check my Docker storage driver to make sure it is compatable (overlay2) with the image.

$ docker info

Next I checked for total RAM, which Microsoft states should be at least 2GB.

$free -m

Turns out this was still a t2.micro instance, so I had to change the instance size of my EC2 to get more RAM. I chose a t2.medium instance to get a bit more RAM to work with than just the minimum and pick an additional core.

Better! Back on track.

Also checked my available storage(8GB based on the t2.micro instance I had before).

$ df

I decided to bump that up to 30GB using the instructions from AWS for extending a volume size for Linux.

A quick check on 64-bit

$ uname -m

Pull and run the image

Finally, it was time to pull the container image from Microsoft and run it. It took a bit longer than I wanted to get to this point, but succesfully resized an EC2 instance and extended the size of my EBS volume as well!

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

$ sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD={YourNewStrong!Passw0rd}' \
   -p 1433:1433 --name sql1 \
   -d mcr.microsoft.com/mssql/server:2017-latest

Then verified the container was OK

$ docker ps

All looks good.

Connecting to the database

In the interest of learning to use the interactive shell inside of a Docker container I used the example from Microsoft to run SQL Server commands to make sure everything was running OK inside the container.

$ sudo docker exec -it sql1 "bash"
[email protected]:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '{YourNewStrong!Passw0rd}'

This successfully produced a sqlcmd prompt:
1>

From this sqlcmd prompt I quickly ran through the steps from the Microsoft Quick Start Guide to create a database, table, and 2 records within that table.

Connect outside the container

I had finally arrived at the point I was most excited about. Connecting to the SQL Server running in the container from outside of the container.

sqlcmd -S {your server ip},1433 -U SA -P '{YourNewStrong!Passw0rd}'

In my case I wanted to test connecting using SSMS on my local machine to the EC2 instance on AWS running the container (making sure the security group allowed for inbound connections on the correct port from my machine. Success!

Conclusion

I’m really happy I was able to get this to work. It was amazing how straightforward it ended up being since the container image was provided by Microsoft. Some day I will do a full SQL Server install on Linux to take my journey a little deeper.

My next project with Docker will be backing up an existing database to a Docker container to provide a prepackaged image of a database that all developers can pull and test against.


Featured image by Tobias Fischer @ unsplash.com