Running Microsoft SQL Server on a Linux container in Docker

Summary

Continuing my series of articles about Docker, this one explains how to get up and running with Microsoft SQL Server running in a Linux container (spoiler alert: it's remarkably straightforward). Once we've done that, we'll end up running a simple query against our database from outside our docker container (using each of PowerShell, SQL Server Management Studio (SSMS) and LINQPad).

Background

Recently I got a new laptop which is not quite as powerful as I would usually buy (my emphasis being more on portability and battery life this time round). For this and various other reasons, I decided that now would be a good opportunity to spend a bit more time looking at Docker and not to install quite as much stuff.

As I mentioned in my getting started article, a setup like this could sensibly be used by a software developer who wanted a local SQL Server instance without all the hassle (and probable resource usage, even when not in use) of a full local installation. Since I'm using my laptop PC, I'm using Docker for Windows, but this really is cross-platform so, if you're using a Mac or a Linux machine, you can still follow this guide^[1]. That's quite cool.

Procedure

If you haven't already installed Docker, you need to do that first. I have written a guide for doing so on a Windows machine as there can be a couple of issues under certain circumstances.

Once Docker is running, you need to pull the latest MS SQL Server image from Docker Hub. You can see more about this image at https://hub.docker.com/r/microsoft/mssql-server-linux/, but it's not necessary to visit that link to download the image; you only need to go there if you want more information.

Open a PowerShell window (Win+X, I - it doesn't need to be in admin mode) and run the following commands:

First we're going to pull the image. It's around 1.35GB when decompressed.

docker pull microsoft/mssql-server-linux:2017-latest

Then we can run docker images to confirm that it's there.

NOTE: You don't actually need to pull the image separately before trying to run it. The docker run command will pull the image first if it needs to. We'll demonstrate this in a minute.

Running the container

Next you need to run it. To do this you need to accept the license agreement and specify a strong password for the administrator (sa) account. You should also specify the PID if you want something other than the Developer edition (which I don't since that version is now free for dev use). There are lots of other environment variables which you can set and which may be seen at https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-environment-variables (e.g you can change the internal port not to be 1433 via MSSQL_TCP_PORT=nnnn).

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Str0ngPassword!' -p 1401:1433 -d --name=tomssl_sql microsoft/mssql-server-linux:latest

NOTE: When connecting to MS SQL on Linux, you have to connect with a username and password; you can't use Windows Authentication.

A note about port mapping

Note that the port mapping is specified via -p host port:container port.

Thus -p 1401:1433 means that port 1401 on the host (my laptop) is mapped to port 1433 in the container. If you've ever used MS SQL Server before, you probably know that it listens on port 1433 by default. This means that any connection strings I create from outside the container (i.e. all of them) will need to include port 1401.

Querying through PowerShell

Now that our container is running, let's run a simple query to satisfy ourselves that everything is working properly. In your PowerShell window, run the following command which will run the slqcmd tool (from within your image) interactively.

docker exec -it tomssl_sql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa

After entering the (ahem) Str0ngPassword! we chose earlier you will be greeted with a simple 1> prompt.

Run the following two commands:

1> SELECT @@VERSION
2> GO

The screenshot shows running the image and then running the query (and its output).

Let's leave the image running and try querying it via a couple of other methods.

Querying using SQL Server Management Studio 2017

If you haven't got SSMS 2017, you can download it for free from: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.

First, you need to connect to your server. Don't forget to specify the port and that this is done by using a comma, e.g. localhost, 1401.

Now let's run our extremely simple query using SSMS:

Querying using LINQPad

I don't know about you, but I've been a big fan of LINQPad for about eight years now.

First you need to connect to your container in much the same way as for SSMS (don't forget the comma).

Then choose Language: SQL and run the query.

And that's it. Pretty easy, eh?

An aside about image tagging/versioning

On the Docker Hub page for the MS SQL image I noticed that there's a version tagged as latest and another one tagged 2017-latest. It seemed likely that these were one and the same, but I couldn't see anything confirming this, so I decided to find out for myself.

Notice that I deliberately chose to run the image tagged latest instead of 2017-latest (which I'd already pulled) and notice that they are actually the same image (they have the same image id and nothing was downloaded).

We can think of the image id as being a hash of the image, the point being that each different version of the same image will have a different image id and so, if several differently tagged images have the same image id, then they are, in fact, the same image.

As you can see above, the latest and 2017-latest images have the same image id and therefore mssql-server-linux:latest and mssql-server-linux:2017-latest are the same.

Conclusion

As you can see, it is trivially easy to get up and running with MS SQL Server on Linux using Docker. In this article we pulled and ran the latest version of MS SQL Server on Linux and ran a very simple query using PowerShell, SQL Server Management Studio (SSMS) and LINQPad.

Don't forget to let me know how you get on in the comments section below and follow me on Twitter for more frequent updates. Follow @TomChantler