Why you may ask? SQL Server runs just fine on Windows 10, but there are a few advantages to running SQL Server in a container rather than installing it on your machine. The biggest advantage is that you can throw it away at any time, for any reason (like a new version has shipped) and leave your machine pristine and fully functional. If you have ever tried to uninstall SQL Server from your machine you’ll definitely appreciate that. Also it is faster to get up and running than a full install of SQL Server (Assuming you already have Docker Desktop and Docker Compose installed, which I do) .
In the modern world of microservice development I find that over time I end up with all sorts of dependencies installed on my machine for various projects. One project may be using SQL Server, the next MongoDB and the next PostgreSQL. And then there is Redis, RabbitMQ, the list goes on and on… Running these dependencies in containers just makes it quick and easy to switch between projects and not have all of these dependencies cluttering up my machine.
As I mentioned this approach does assume you have Docker Desktop installed, and I prefer to also use docker compose as well just to simplify starting things up and shutting them down when I need to. If you don’t already have these tools installed you can get them at Docker Hub, or by using Chocolatey (The Windows installer for Docker Desktop will install both for you.)
choco install docker-desktop
Getting Started
It’s pretty simple to get an instance of SQL Server running in a container, you’ll find all the basic information to get started on the DockerHub Microsoft SQL Server listing. To start up the latest version of SQL Server 2017 use the following command from your command shell.
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Password#1" -p 4133:1433 -d mcr.microsoft.com/mssql/server:2017-latest
Note: I’m running the commands in PowerShell which requires double quotes. If you run them using the command prompt use single quotes.
The -e arguments set environment variables inside the container that are picked up by SQL Server when it runs.
ACCEPT_EULA=Y accepts the Microsoft SQL Server EULA
SA_PASSWORD set the sa account password (You might want to choose a better password!)
-p maps the ports your-machine:container. If you want to map 1433 (the standard SQL Server port) to itself on your machine use -p 1433:1433, in my examples I’ll be mapping to 4133 on my machine as above.
-d runs the container detached, returning the container id and releasing your shell prompt for you to use. If you omit this standard out will be dumped to your shell as long as the container is running.
mcr.microsoft.com/mssql/server:2017-latest specifies the image to run (and pull if you don’t already have it) The :2017-latest is the tag and means to pull the latest tagged version of the image. You can specify a specific version if you so choose.
So if we run the command above (and we haven’t previously run it) Docker will go out and pull the image and start it up. It will likely take 30 seconds to a few minutes to download the image, but once it is completed you should see something like the following in your shell.
❯ docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Password#1" -p 4133:1433 -d mcr.microsoft.com/mssql/server:2017-latest Unable to find image 'mcr.microsoft.com/mssql/server:2017-latest' locally 2017-latest: Pulling from mssql/server 59ab41dd721a: Pull complete 57da90bec92c: Pull complete 06fe57530625: Pull complete 5a6315cba1ff: Pull complete 739f58768b3f: Pull complete 3a58fde0fc61: Pull complete 89b44069090d: Pull complete 93c7ccf94626: Pull complete 0ef1127ca8c9: Pull complete Digest: sha256:f53d3a54923280133eb73d3b5964527a60348013d12f07b490c99937fde3a536 Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2017-latest bcb2d2585339b3f7fd1a2fdeafff202359ce563213801949a4c55f954e5beb11 ❯
At this point you should have a shiny new instance of SQL Server 2017 up and running. You can see the running container by executing
docker ps
This will list out all of the running containers on your machine.
Note the Container ID and Name, you can use these to reference the container with subsequent Docker commands. At this point you can connect to your database server from your application or SQL Server Management Studio. With the command above the connection string to connect would be: “Server=localhost,4133;Database=master;User Id=sa; Password=Password#1”.
To stop the instance:
docker stop bcb
Above I used a shortened/abbreviated version of the container id, you can do this if it uniquely identifies the container. If I had 2 containers that started with this string I would need to use the full id (or at least more of it) or the name.
I can start it up again using:
docker run bcb
And I can permanently delete the instance using:
docker stop bcb docker rm bcb
If you need to see the containers you have that are not currently running (ie. you stopped, but did not remove them) use:
docker ps -a
Making Things a Bit More Usable
All this is awesome, but you’ll soon run into a couple of issues:
- You’ll grow tired of typing in all the long command, remembering all the correct switches etc, and listing out the containers to get the ids to manage them.
- Once you delete your containers you’ll lose your databases! That’s right, the database files are stored in the container, so once you delete the container it’s gone.
Let’s start by solving the second problem first, which will make the first problem worse :(, then we’ll circle back to solve the first problem.
Mapping Your Data Files to Your Local Machine
Step one: You’ll need to share a drive in Docker. To do this:
- Right click on the Docker Desktop Icon in your system tray and select “Settings”.
- Select the “Resources” item and then “File Sharing”.
- Select a drive to share and click “Apply & Share”
Step two: Create a folder in your shared drive to map into your container. In my case I’ve shared my x: drive so I’ve created a folder X:\DockerVolumes\SqlData\Sample
Step three: Now we are ready to modify our run command to map the shared (empty) folder into our container’s data directory. (I would avoid spaces in the path to your shared volumes directory, as I recall it make things “fun”.)
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Password#1" -p 4133:1433 -v X:\DockerVolumes\SqlData\Sample:/var/opt/mssql/data -d mcr.microsoft.com/mssql/server:2017-latest
Assuming everything works as expected, you should now have all of your system databases in your shared directory. Now they will persist even if you destroy the container and spin up a new one.
Directory: X:\DockerVolumes\SqlData\Sample Mode LastWriteTime Length Name ---- ------------- ------ ---- -a---- 2020-01-29 10:07 PM 4194304 master.mdf -a---- 2020-01-29 10:07 PM 2097152 mastlog.ldf -a---- 2020-01-29 10:07 PM 8388608 model.mdf -a---- 2020-01-29 10:07 PM 8388608 modellog.ldf -a---- 2020-01-29 10:07 PM 14024704 msdbdata.mdf -a---- 2020-01-29 10:07 PM 524288 msdblog.ldf -a---- 2020-01-29 10:07 PM 8388608 tempdb.mdf -a---- 2020-01-29 10:07 PM 8388608 templog.ldf
If they do not show up, try stopping the container and restarting it without the -d switch and read through the output in your terminal, it will usually give you a clue as to your problem.
Cleaning It All Up with Docker Compose
All that is great but, typing out – docker run -e “ACCEPT_EULA=Y” -e “SA_PASSWORD=Password#1” -p 4133:1433 -v X:\DockerVolumes\SqlData\Sample:/var/opt/mssql/data -d mcr.microsoft.com/mssql/server:2017-latest – every time you want to start SQL Server is a bit annoying and error prone. To solve this we’ll put all these arguments into a docker-compose file and make things much easier.
To organize things I create a folder on my drive to contain my docker-compose files, each file in it’s own sub folder. ex: C:\Docker\Sample would contain 1 docker-compose.yml file that defines my configuration for SQL Server 2017. Here is an example file for the docker run we ran above:
version: "3"
services:
default-sql:
image: "mcr.microsoft.com/mssql/server:2017-latest"
ports:
- 4133:1433
environment:
SA_PASSWORD: "Password#1"
ACCEPT_EULA: "Y"
volumes:
- X:\DockerVolumes\SqlData\Sample:/var/opt/mssql/data
Most of this should look pretty familiar, it’s just a YAML representation of the arguments we’ve been specifying above.
If we navigate to the folder containing our docker-compose file, in my case C:\Docker\Sample\ we can simply run:
docker-compose up -d
Once again the -d switch is to run the container detached. You can omit it an see what is happening inside your container. After a few seconds our server will be up and running. When we are done with our container we can run:
docker-compose down
Now everything should be spun down. If you’re really lazy like me you can create an alias for docker-compose in your PowerShell profile so you can just use:
dc up -d dc down
Final Thoughts
You’ll want to keep an eye on the containers you have that are sitting around in a stopped state by using “docker ps -a” and cleaning up the old containers by using “docker rm CONTAINERID” to remove them. You’ll also want to keep an eye on the images you have cached and periodically clean them up as well. You can list them with “docker images” and remove them with “docker rmi IMAGEID“. (rmi=remove image) These images can be pretty good size (the current SQL 2017 image is 1.4GB).