Runnin' Microsoft SQL Server on XXXXX Linux container in Docker

Summary

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

Background

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

As I mentioned in my gettin' started article, XXXXX setup like this could sensibly be used by XXXXX software developer who wanted XXXXX local SQL Server instance without all XXXXX hassle (and probable resource usage, even when not in use) of XXXXX full local installation. Since I'm usin' my laptop PC, I'm usin' Docker for Windows, but this really is cross-platform so, if you're usin' XXXXX Mac or XXXXX 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 doin' so on XXXXX Windows machine as there can be XXXXX couple of issues under certain circumstances.

Once Docker is running, you need to pull XXXXX 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 XXXXX image; you only need to go there if you want more information.

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

First we're goin' to pull XXXXX 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 XXXXX image separately before tryin' to run it. The docker run command will pull XXXXX image first if it needs to. We'll demonstrate this in XXXXX minute.

Runnin' XXXXX container

Next you need to run it. To do this you need to accept XXXXX license agreement and specify XXXXX strong password for XXXXX administrator (sa) account. You should also specify XXXXX PID if you want somethin' other than XXXXX 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 XXXXX 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 connectin' to MS SQL on Linux, you have to connect with XXXXX username and password; you can't use Windows Authentication.

A note about port mapping

Note that XXXXX port mappin' is specified via -p host port:container port.

Thus -p 1401:1433 means that port 1401 on XXXXX host (my laptop) is mapped to port 1433 in XXXXX 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 XXXXX container (i.e. all of them) will need to include port 1401.

Queryin' through PowerShell

Now that our container is running, let's run XXXXX simple query to satisfy ourselves that everythin' is workin' properly. In your PowerShell window, run XXXXX followin' command which will run XXXXX slqcmd tool (from within your image) interactively.

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

After enterin' XXXXX (ahem) Str0ngPassword! we chose earlier you will be greeted with XXXXX simple 1> prompt.

Run XXXXX followin' two commands:

1> SELECT @@VERSION
2> GO

The screenshot shows runnin' XXXXX image and then runnin' XXXXX query (and its output).

Let's leave XXXXX image runnin' and try queryin' it via XXXXX couple of other methods.

Queryin' usin' 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 XXXXX port and that this is done by usin' XXXXX comma, e.g. localhost, 1401.

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

Queryin' usin' LINQPad

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

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

Then choose Language: SQL and run XXXXX query.

And that's it. Pretty easy, eh?

An aside about image tagging/versioning

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

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

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

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

Conclusion

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

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