Mini Data Lab – SQL Server 2019 on Docker Part 2

So here we are back for Part 2 (I highly recommend you read Part 1 if you haven’t yet). In this post we are going to start configuring our containers and our SQL Instances to make them a little more functional and useful. In the first post, we really just created the containers with all the default settings. That means that all the storage is going to be inside the container. If it is deleted, the storage goes with it. So instead, to make the container more robust and upgradable, we are going to map some local storage on my host machine.

Let’s first take a look at the way I have my disk/folder structure laid out. Again, this is on my personal computer so it’s not a best practice for production and more suitable for development environments.

For each container, I’m creating a separate folder with the MSSQL paths that I need to put my databases, transaction log, and backup files on. Additionally, under the DockerMount folder I have a folder called sqldockershared (which I will put some shared content in later).

Instead of using the container I created in my first post, I will create a new one. Below is the code that I ran to create the new container called sqldocker1.

docker run -e "ACCEPT_EULA=Y" ^
-e "SA_PASSWORD=YOURSTRONGPASSWORDHERE" ^
-e "MSSQL_DATA_DIR=/SQLMount/mssql/data" ^
-e "MSSQL_LOG_DIR=/SQLMount/mssql/logs" ^
-e "MSSQL_BACKUP_DIR=/SQLMount/mssql/backups" ^
-p 1433:1433 ^
-v S:\DockerMount\sqldocker1\mssql\system:/var/opt/mssql/data ^
-v S:\DockerMount\sqldocker1\mssql\data:/SQLMount/mssql/data ^
-v S:\DockerMount\sqldocker1\mssql\logs:/SQLMount/mssql/logs ^
-v S:\DockerMount\sqldocker1\mssql\backups:/SQLMount/mssql/backups ^
-v S:\DockerMount\sqldockershared:/SQLShare ^
--name sqldocker1 -h sqldocker1 -d mcr.microsoft.com/mssql/server:2019-latest 

Let me explain the extra commands used in this script. First, at the end of each line there is a ^ which is sort of like a carriage return allowing you to put multiple lines as opposed to having to write everything in one line. For PowerShell you would use ` (the single quote next to the 1 on the keyboard) and for BASH you would use \.

-e “ACCEPT_EULA=Y” -e “SA_PASSWORD=” these two flags were explained in Part 1

-e “MSSQL_DATA_DIR=/SQLMount/mssql/data” ^
-e “MSSQL_LOG_DIR=/SQLMount/mssql/logs” ^
-e “MSSQL_BACKUP_DIR=/SQLMount/mssql/backups” ^
These three flags configure the default directory for Data, Log, and Backup files once your instance is up and running. This is important so that future creations of Databases and Backups have a location that has a binding to your Host’s storage and won’t be lost if the container is lost.

-p HOST:CONTAINER – explained in Part 1

-v S:\DockerMount\sqldocker1\mssql\system:/var/opt/mssql/data ^
-v S:\DockerMount\sqldocker1\mssql\data:/SQLMount/mssql/data ^
-v S:\DockerMount\sqldocker1\mssql\logs:/SQLMount/mssql/logs ^
-v S:\DockerMount\sqldocker1\mssql\backups:/SQLMount/mssql/backups ^
-v S:\DockerMount\sqldockershared:/SQLShare ^
So here is one of the major changes from the last container that we created in Part 1. This is one of the basic ways to mount volumes from your container to your host’s storage. -v HOST-FILE-PATH:CONATINER-FILE-PATH is the basic syntax. In my case, I’m using the S Drive to mount and map my container’s storage.
The first configuration line of the group is mapping the /var/opt/mssql/data file path in my container to S:\DockerMount\sqldocker1\mssql\system. The reason for this is so that my system databases are placed on that folder in my host (See screenshot below with the files in the correct file path). Since this container is running SQL Server on Linux, the system databases will be under this path in the container (/var/opt/mssql/data) by default. I’m simply telling the container to map that default path to the host’s storage path as stated above.
The next three configuration settings map the user Database, Log, and Backup files to the location S:\DockerMount\sqldocker1\mssql\(data or logs or backups) to their respective paths between the container’s internal file path and the host storage path.
The last configuration setting simply mounts an extra path for some shared files we will be using later. Here is an image that might help solidify your understanding of this concept.

–name -h -d these three flags were explained in Part 1

Image to help explain mapping between container paths and physical folder structure on my Windows Host machine.

And now simply paste the command to the command prompt and press enter. It should take only a few seconds to create the container, start it, and have it ready to connect to.

You can use the command docker ps to see running containers on your system with some basic status information.

You may get this warning since you are mapping a container to your Windows file/folder path.

SQL Server’s system databases now stored and mapped to my Host’s storage in a way the I can find them.

Default locations changed at the Instance level.

Now you may be asking what the benefit of all this is, and it’s quite simple. You want to be able to have a robust way of keeping your database files secure and highly available. Of course, in a real production environment those mount paths would be on SANs or other robustly scalable storage. Imagine that your container is broken somehow, the only thing you need to do is recreate it with the scripts above and everything goes back to “normal.” This is because your system and user databases are in a storage location outside the container and will be persisted. That also means you can do things like upgrades almost instantly; just replacing the old container with a new one and pointing to the existing directories.

It will also help you perform normal tasks easier as an administrator, for example restoring backups! Let’s try this right now. First step, simply put a backup in the backup folder and then restore it from SSMS.

Because of some permissions issues between Linux and Windows (which I fully don’t understand yet), I have to create the database and then do a Restore with REPLACE to get it to work.

Now let’s try something a little more fun. We are going to stop the container, create a new one with the same paths (different name and ports) and start it up to see what it looks like. You can stop the container from the Docker desktop app or just run docker stop sqldocker1 from the command prompt. Now let’s run this similar code in the command prompt (the only thing I have changed is the container’s external port number and the name).

docker run -e "ACCEPT_EULA=Y" ^
-e "SA_PASSWORD=YOURSTRONGPASSWORDHERE" ^
-e "MSSQL_DATA_DIR=/SQLMount/mssql/data" ^
-e "MSSQL_LOG_DIR=/SQLMount/mssql/logs" ^
-e "MSSQL_BACKUP_DIR=/SQLMount/mssql/backups" ^
-p 1403:1433 ^
-v S:\DockerMount\sqldocker1\mssql\system:/var/opt/mssql/data ^
-v S:\DockerMount\sqldocker1\mssql\data:/SQLMount/mssql/data ^
-v S:\DockerMount\sqldocker1\mssql\logs:/SQLMount/mssql/logs ^
-v S:\DockerMount\sqldocker1\mssql\backups:/SQLMount/mssql/backups ^
-v S:\DockerMount\sqldockershared:/SQLShare ^
--name sqldockertest -h sqldocker1 -d mcr.microsoft.com/mssql/server:2019-latest 

New instance, with new port connected and the previously restored database is online and available.

SQL Server Instance name is the same since it was configured with the initial creation of the sqldocker1 container.

There you have it, a new instance up and running with all the old values. This is a great way to run upgrades in the future or even to have a stand-by incase your container gets corrupted you have an easy way to launch a new instance. Keep in mind, there are ways of changing the containers running values without having to recreate them. If you know how, feel free to post your solutions in the comments as I’m still learning myself 🙂