Using SQL Server on Docker (1)

It is yesterday’s news. SQL Server runs on Linux, and on Docker too. There are plenty of blog posts showing how to install, start and connect to it too.

During last Data Relay, I have seen Mark Pryce-Maher‘s talk on SQL Server on Linux (twice). It was a good talk, but it followed the same pattern so not surprisingly a common question from the audience appeared to be ‘why would you want to do it?’.

I mean, the biggest claim to fame appears to be that SQL Server actually works on Linux. It doesn’t bring any new features. In fact, some features are missing. A good part of docker demos appears to be an exercise in futility too. One starts a container with SQL Server, creates a database, inserts some data then the container restarts and as if by magic the data is gone, the database never existed. So, indeed, why would you want to run an SQL Server on Docker?

This post is my attempt to address this question by showing how I use SQL Server on Docker and how I deal with the persistence problem.

First: Get it up and running!

If you want to know how to run and connect to SQL Server on Docker read the Microsoft’s Quickstart Document, or Andrew Pruski’s blog post about running vNext on Docker.

Making the long story short, assuming you are running Windows, have Docker installed, configured to run Linux containers and that you don’t have an SQL Server instance locally installed:

docker pull mcr.microsoft.com/mssql/server:latest
docker run -d -e ACCEPT_EULA=Y -e SA_PASSWORD=Secret1234 -p 1433:1433 --name sql mcr.microsoft.com/mssql/server:latest

That’s all you need to do to have the latest production SQL Server (Developer Edition) instance running on localhost on port 1433.

Creating a database that survives container restarts

If you have the container from the previous example running let’s stop and remove it first.

docker stop sql
docker rm sql

There is a number of ways to persist container data. For my purposes the simples appears to be docker volumes. You can read more about docker persistence on the official documentation site but for now let’s just create a local volume called sqlvolume

docker volume create sqlvolume

and mount that volume on the container

docker run -d -e ACCEPT_EULA=Y -e SA_PASSWORD=Secret1234 -p 1433:1433 --mount source=sqlvolume,target=/data --name sql mcr.microsoft.com/mssql/server:latest

Now, if you create a database with files on the mounted volume like so:

create database [TestDb]
on primary (name = N'TestDb', filename = N'/Data/TestDb.mdf')
log on (name = N'TestDb_log', filename = N'/Data/TestDb.ldf')

and you stop and start the container

docker stop sql
docker start sql

as if by magic, the TestDb database survived the restart.

Why I find it useful

So with some considerable effort, I was able to create a database that survives a restart of a container or that of a host system. Nothing a standard SQL Server instance running on windows couldn’t do! So why do I do it?

I write my blog posts on my laptop and I don’t want to commit to installing SQL Server on it. I want to be able to try out vNext while still having the ability to run demos against 2017 and I’m definitely not installing two instances on my laptop. Luckily I don’t have to. With the above setup all I have to do is run docker start sql and within seconds I have a local instance of SQL Server ready to serve my queries. When I’m done I do docker stop sql and it is as if the SQL Server was never there.