This is a super simple way to retain the data and settings that you create and change on your SQL Server Docker instance. There’s a lot of talk about attaching databases after you recreate your Docker container, but I found that you just have to mount the volumes that SQL Server uses to hold the system and user databases to a folder on your local drive, and if you remove the container you can recreate it and mount the same local drives without losing any data or settings. It’s great and simple!
This article assumes you have a working knowledge of containerization and SQL Server, but may provide enough detail to get you up and running with very little knowledge on either.
So, you will need to install Docker on your local machine and have it set to use Linux containers. https://docs.docker.com/get-docker/
I don’t remember whether it runs Windows or Linux containers by default, but you can right-click the Docker icon in the system tray, and choose “Switch to Linux containers…” If you have anything running as a Windows container already, then they will continue to run, but you won’t be able to manage them until you switch back to Windows containers. Sounds good to me!
When you right-click the Docker icon in the system tray, if you see “Switch to Windows containers…” then you are already on Linux, and can skip to the section on Let’s set up this SQL Server container! Otherwise, click “Switch to Linux containers…” as shown in the following screenshot.
It will warn you about switching and not being able to manage. You can choose to not show this message again (or not, it’s up to you), and then click Switch as shown in the following screenshot.
You will probably get a message in the lower right corner of your screen saying Docker Desktop is switching…
It doesn’t take very long in my experience, but I guess they are working on the model of under promise and over deliver, or maybe it’s just because I don’t have a lot of containers running, or I don’t know what would made this switch take a long time.
Anyway, you wait until the switch is done. You can right-click the Docker icon in the system tray again, and see that it now shows “Switch to Windows containers…”
Now, you are good to go to setup SQL Server on Docker with a Linux image.
Let’s set up this SQL Server container!
First things first, set up a folder on your local machine that you will map to your container. I just set up a folder structure here: C:\docker\sql2019u because I’m using the latest SQL Server 2019 version on Ubuntu, but you can name your folder whatever you want. Note: I’m using the Linux image because I tried the Windows one and it’s old and no good, and I’ve read that Microsoft isn’t really maintaining Windows SQL Server images for Docker and you should use Linux images.
Then you will run the following code at the command line:
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=thisisastrongpassw0rd!" -p 1436:1433 --name sql2019u -v c:/docker/sql2019u/data:/var/opt/mssql/data -v c:/docker/sql2019u/secrets:/var/opt/mssql/secrets -d mcr.microsoft.com/mssql/server:2019-latestLet’s do a quick breakdown of what we are doing in the docker run script above:
- docker run – standard way to start a Docker container
- -e – is accepting the license of SQL Server (EULA) and setting the SA password. Important note: make sure you set a strong password otherwise the container will appear to be working correctly, but is not accessible and actually stuck on an error saying the password doesn’t meet requirements.
You can follow the logs of the container by using this command:
docker logs -f <containerid> - -p – I’m setting the port on my host to 1436 because 1433 is already in use on it with a SQL Server install, and 1436 will map back to 1433 in the container. When setting these flags, it’s always host then container, so -p hostport:containerport. You can also choose to map the container to the host port 1433, but I already had 1433 in use. If you want to map the container to host 1433, just use 1433:1433 instead.
- –name – names the container. If you leave this off, then it gets a random name.
- -v – maps a volume from the host to the container. In this case, I’m mapping the default container data directory to a folder on my host. I’m also setting the secrets to map to the host so nothing is lost when removing the container. This will ensure any changes you make to the system dbs will be saved and any new dbs you add will also be saved, even if you remove the container, the host folder and it’s data will not go away.
- -d – this builds the container in the background. If you want to see everything it does at the command line, you can leave this off, but it’s a lot of output to the screen telling you every single thing it’s doing as it creates the container.
- mcr.microsoft.com/mssql/server:2019-latest – gets the latest 2019 SQL Server image to use in your container
So, now open your command line, if you haven’t done so already, and get this container setup!
When the code has finished, it will spit out some random GUID:
Note: if this is the first time using this image, it will need to be downloaded so you will see something like the screenshot below with the downloading of the image, but once it’s downloaded once, it won’t have to download it again (unless you purposefully remove the image in the future).
To see the status of your container, run docker ps at the command line. This will display a list of your running containers and their status:
If you look in your host folder, it won’t be empty, and it contains the secrets and data folders that are mapped to your container. The data folder has your system dbs (and will also hold your user dbs once you create some) and the secrets folder contains the machine-key file.
Connecting via SSMS
Once you confirm your Docker container is running, you can connect to it via SSMS. You will use the name localhost, and if you mapped the container to port 1436 on your host, then you will use localhost,1436. If you decided that you wanted to map to 1433 on your host, then you won’t need the port, and instead, just use localhost.
Note: If you get an error like “A connection was successfully established with the server, but then an error occurred during the pre-login handshake.”, it may look like the docker container is ready, but it still may be loading, so check the logs at the command line to make sure it’s ok and then try again:
docker logs sql2019u
Getting SQL Server Agent enabled
When you first get into SSMS, you will see that the SQL Agent is disabled, but pretty much most other things are in good working condition. Let’s get the agent enabled.
It’s a simple fix to get the agent running. You can run the code below in an SSMS script window.
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure 'Agent XPs', '1';
RECONFIGURE; Once this script has been run successfully, you can refresh you connection in SSMS:
Then, SQL Server Agent will be running!
How to get your data and settings back if you remove the container
Since we’ve changed settings in the last section, and this is the kind of thing that won’t be remembered if you remove you container without having volumes mapped from your host to your container, let’s remove the container to see how the setting changes will be reflected because we mapped the volumes with the first creation of the container and we will once again map the volumes. Let’s stop and remove the container:
To stop the container at command line: docker stop sql2019u
To remove the container at command line: docker rm sql2019u
You can double-check the container is gone by running docker ps at the command line, but it’s not necessary.
Next, you can run the same docker run command as when you set it up the first time (as long as you didn’t remove the folder that lives on your host computer and I don’t think you would have done that 🙂 and I know that Docker didn’t do that with the container removal):
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=thisisastrongpassw0rd!" -p 1436:1433 --name sql2019u -v c:/docker/sql2019u/data:/var/opt/mssql/data -v c:/docker/sql2019u/secrets:/var/opt/mssql/secrets -d mcr.microsoft.com/mssql/server:2019-latestOnce it’s done, then connect to SSMS again:
And you will see the agent is running as expected without the additional configuration steps! So nice, no additional work needed and it remembers what you did as long as you map the volumes!
The whole point of this exercise for me was to get auditing working on a SQL server, then use a linked server to another SQL Server, and send the auditing data via a SQL Agent job. More to come on this in a future post. So you know I tried this all on 2017 version first and it doesn’t support linked servers, so had to update these instructions to 2019.