/ SQL Server

SQL Server 2017 CTP 2.0 in Docker on Linux (Because you can!)

Hey there buddos!

Have you been seeing the cool new things that SQL Server 2017 is going to support? I have and I'm keen to start playing with them. Especially graph databases, they look really interesting!

So what's the best way to give these new features a try without setting up a new VM and faffing around with installs? Welp, another new feature in 2017 is support for Linux, and with that support for stable, easy to manage Docker containers.

Now I'm going to assume that you're using a Windows desktop for this. Some of the steps apply to Mac and Linux too, so you should be able to figure it out. You're smart. I believe in you!

Step 1 - Install Docker

Download Docker for Windows and do yourself an install. When it's done you'll have a cute little whale icon in your notification area.

Go ahead and right click that whale and hit Settings... then Advanced.

Docker Advanced Settings page

Here you can set how many CPUs your containers can access, and where your images (which containers are built from) are stored. Essentially though, you need to set the memory available to docker to at least 4GB for SQL Server to start properly. I missed this and got upset when all my SQL Server containers shut down immediately after startup.

Step 2 - Get the SQL Server Image

Next we need an image to create our container from. Images are pre-built containers that you can build your own containers off.

Microsoft are kind enough to build container images for their CTP releases. We need to pull an image of CTP 2.0 to our machine so we can create a container from it. To do this we can see what images are available with this command:

docker search mssql

List of mssql docker images

Dang, that's a lot of images! None of them are Microsoft ones either, so let's narrow down the search a bit...

docker search microsoft/mssql

List of Microsoft mssql Docker images

Better! Now we can see microsoft/mssql-server-linux, Microsoft's official SQL Server Linux image (the microsoft/ bit means it comes from their repository on Docker hub). But how do we know which version of SQL Server we're getting?

Docker uses tags to distinguish versions of images. I don't know of a way to browse tags from the docker command line, so we need to look at the image's page on on Docker Hub (the site that hosts all docker images). Whilst you're there set yourself up a login for Docker Hub.

mssql-server-linux repository

Here we can see that CTP2-0 is the latest tag (at the time of writing), so this gives us a choice of commands we can use.

If we want that specific version we can download the image using docker pull microsoft/mssql-server-linux:ctp2-0 but since this is the latest image version we can instead just run docker pull microsoft/mssql-server-linux which will automatically get the newest version of the image. You'll be asked for your docker hub username and password, so fill that in and your image will start downloading:

Pulling a Docker image

When that's done you should get a line like:

Digest: sha256:dbca054ce3266e8a392eb24e51f3d8eebb53b18cc9e907918b3ef0e95e0e183c
Status: Downloaded newer image for microsoft/mssql-server-linux:latest

See that :latest tag at the end? That means we've pulled the latest build. We can run docker images and check that mssql-server-linux appears in the list. Here it is at the top of my list:

list of local Docker images

Step 3 - Create a Container

Now for the good bit! Let's spin up a new container running SQL Server 2017 CTP 2.0.

Now we can tweak a few things to make our container accessible. Here's the basic command that will start a SQL Server container:

docker run -d -p 1433:1433 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssword' microsoft/mssql-server-linux

There's a few switches there that need explaining:

-dTells docker to run this container in the background, so we get our powershell window back after the container is started.
-p 1433:1433Tells docker to map the port 1433 on the local machine to the port 1433 on the container. Change this to -p [yourport]:1433 if you already have an instance running on port 1433.
-e 'ACCEPT_EULA=Y'Good 'ol EULAs. Microsoft requires you to specify this.
-e 'SA_PASSWORD=P@ssword'This sets the password for the SA account. Change it to something you like!
Now you should have a command ready to run in Powershell. Give it a go. I'll wait.

...

...

That didn't take long did it? You should have a unique container ID and a blinking cursor. Great. How do you know anything has even happened? Let's run:

docker ps

Currently running Docker containers

Yeah! We can see our container sitting there waiting to do something useful. You'll notice it lists the container ID and a friendly name it makes up (in the example here: hungry_chandrasekar).

Step 4 - Connect to SQL

Rev up SSMS and connect to localhost (or localhost,[port number] if you had to change the container's port) with SQL Server authentication. Use the username sa and the password you specified when you ran the container.

SSMS Object explorer

Aww yissss! You've now got an instance of SQL Server 2017 CTP 2.0 running in a container on Linux. Don't believe me? Run SELECT @@version:

Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64)   Apr 13 2017 11:44:40   Copyright (C) 2017 Microsoft Corporation. All rights reserved.  Developer Edition (64-bit) on Linux (Ubuntu 16.04.2 LTS)

Now you can play with the instance to your heart's content. You'll notice that SQL Server Agent isn't running. Getting that working takes a bit more work, which I'll cover another time, but apart from that everything available in SQL Server for Linux is there.

Step 5 - Cleaning Up

Done with your container, or just messed it up so bad that you need a new one? No problem.
Run docker ps and find the container id or friendly name of your container:

Our Docker container running MSSQL 2017

Now we can stop the container with docker stop [container id] or docker stop [friendly name]. To stop the container above I'd use docker stop c7 as you only need to specify the first two characters of the container id (as long as they're unique).

Once you've done that you can then delete your container completely with docker rm [container id OR friendly name]. If you've stopped your container but need it back, just run docker start [container id OR friendly name] and your back in business!

Summary

It's really easy to get SQL Server 2017 up and running in a container. Here's all the commands you really need to run to get it going without all my extra blathering:

docker run -d -p 1433:1433 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssword' microsoft/mssql-server-linux 

Huh? I can hear you asking why I made you read all that when you could just run a single command. Even over the internet. Even though my hearing is awful.
Welp, Docker will automatically pull an image for you when you try to run a container, so this command will pull the image and set it running, or just run a new container if you already have the latest image. It's neat like that. You still need to install docker and delete you instance yourself when you're done, but that's all there is too it. Quick and easy, and fast to tidy up so you've not got a ton of old installs of CTPs kicking around your hard disk.

It's worth noting that you can also run SQL Server 2017 in a Windows container. This works in a similar way but needs a few changes to the process above, which I'll cover another time.

Now go have fun with your graph databases!