MS SQL Server in Docker
MS SQL Server is the heavy database artillery from Microsoft. It figures among the first positions (5th actually) of databases ranking, according to the Stackoverflow Developer Survey 2022.
Below, you will see how easy and fast is to set up a Docker working environment for your MS SQL Server development projects. And, of course, we are going to use the latest official MS SQL Server repos at DockerHub.
- You have to be familiar with Docker, Docker Images, and Containers, and Docker should be installed in your system (preferably a Linux system or a macOS)
Pulling the image
Before pulling the image, it is worth taking a look at the Official page of SQL Server deployment here.
We are going to pull the official MS SQL Server docker image from DockerHub here.
Pull the image
docker pull mcr.microsoft.com/mssql/server
👉 Note: When you pull an image without specifying a tag name, Docker will try to pull the image tagged latest. During the time this post was being written, the latest docker image version of MS SQL Server was the 2022 version RC1 16.0.950.9 Developers Edition.
Check if the image has been created in your local Docker repository
Creating a working MS SQL Server container
We will use the following command:
docker run -d --name mssql2022_1 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Rpassw!1' -p 1443:1433 -h mssqlserver22 -v $HOME/DOCKER/dockerMSSQL/2022/datafiles:/var/opt/mssql/data -v $HOME/DOCKER/dockerMSSQL/2022/logfiles:/var/opt/mssql/log -v $HOME/DOCKER/dockerMSSQL/2022/secrets:/var/opt/mssql/secrets mcr.microsoft.com/mssql/server:latest
The parameters used here are given to the following table:
|–name:||The name of the container. Here we use the container name: “mssql2022_1”|
|-p||The port mapping of the host port to the container port. Here we map the default 1433 MS SQL Server port to the 1443 port of our local host.|
Environment variables. ‘SA_PASSWORD: The MS SQL Server System Administrator (SA) password. Here we use the password “Rpassw!1“. Note that the SA password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.
|-h||Here we also use the -h host parameter to assign a name to the MS SQL running instance. Here we use the “mssqlserver22“|
The data volume(s) mapped for the database data, log, and secret files. Here we mount the following 3 local folders “$HOME/DOCKER/dockerMSSQL/2022/datafiles”, “$HOME/DOCKER/dockerMSSQL/2022/logfiles” and “$HOME/DOCKER/dockerMSSQL/2022/secrets” to the container folders: /var/opt/mssql/data, /var/opt/mssql/log and /var/opt/mssql/secrets respectively. The host folders have to be owned by the Unix user “mssql” or you can set its permissions accessible to other users e.g. “sudo chmod -R o+rw datafiles”, etc.. If omitted the database will not be persisted over container recreation.
Check the container creation and that it is running
docker ps -a
You can stop the container and start it again using standard docker commands, i.e.:
docker stop mssql2022_1 docker start mssql2022_1
Access the container shell and perform some basic operations to gain fundamental info
Access the container shel
Access the container shell by executing:
docker exec -it mssql2022_1 bash
Get some info about the Linux distro being used (it’s a Debian/Ubuntu-based version):
Use the sqlcmd command line utility
Use the sqlcmd command line utility to perform some fundamental actions
mssql@mssqlserver22:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Rpassw!1'
That’s it for now! I hope you enjoyed it!
Thanks for reading and stay tuned!