MS SQL Server in Docker

A 4-minutes guide. Easy and Comprehensive!

Intro

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.

Prerequisites

• 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. Moreover, when it comes to choosing the best-fit SQL version for you, it’s worth being aware that from version SQL Server 2019 and onwards, the minimum TLS version supported is TLS 1.2.

Check if the image has been created in your local Docker repository

docker images

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 in the following table:

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 shell

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

The docker image of the MS SQL Server offers us a version of the sqlcmd command line utility.

mssql@mssqlserver22:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Rpassw!1'

Even this version seems to have some limitations (e.g. “Sqlcmd: Error: ED : Not supported in this version.”), you can use it to perform most of the fundamental operations

Install sqlcmd locally and use it to connect to a remote instance

Alternatively, you can install it locally (e.g. on your Mac). See how you can do it in the official documentation. For your MAC you have to use the Homebrew:

brew install mssql-tools

Note that it might be required also to have a compatible Xcode version (you will be informed about this, e.g. “Error: Your Xcode (11.3.1) is too outdated. Please update to Xcode 13.2.1 (or delete it).”

After you have finished the installation you can check the sqlcmd version installed, as well as obtain some initial help for syntax summary, by using the command:

sqlcmd '-?'

Then you can use it from your local terminal, to access the running instance on a remote host:

sqlcmd -S 192.168.0.17,1443 -U SA -P 'Rpassw!1'

The editing capabilities of the sqlcmd CLI are not that amazing. One of the things I really dislike a lot is that you cannot paste a multi-line statement into the editor’s buffer. However, one can use an external editor to create/edit a script (.sql) file, and then, the script can be read in the sqlcmd buffer via the :r <script_name.sql> command. Any way. You can always find a way to do the job.

When you enter, it displays a new line with a 1> followed by a blinking underscore that is named the sqlcmd prompt. The 1 signifies that this is the first line of a Transact-SQL statement, and the sqlcmd prompt is the point at which the Transact-SQL statement will start when you type it in.

At the sqlcmd prompt, you can type both Transact-SQL statements and sqlcmd commands, such as GO and EXIT. Each Transact-SQL statement is put in a buffer called the statement cache. These statements are sent to SQL Server after you type the GO command and press ENTER. To exit sqlcmd, type EXIT or QUIT at the start of a new line.

[Read more here]

After this short intro, and if you are connected to your MS SQL Server instance, you can use some of the commands of the CLI to obtain a few fundamental info:

👉 Note: Find more details about sqlcmd CLI utility here.

That’s it for now! I hope you enjoyed it 👋 !
Thanks for reading and stay tuned!

Join Medium with my referral link - Panos Zafeiropoulos

PS: You might also find interesting the following post of mine:

Salts and UUIDs for your MS SQL Server database