How to Set Up a Microsoft SQL Server Database in Docker on Linux

Microsoft SQL Server is a powerful and widely used database management system (DBMS). Traditionally, SQL Server databases were set up on dedicated servers or virtual machines, but Docker has changed all that.

Let's see how you can setup a SQL Server instance on a Linux container with Docker.

Advantages of running SQL Server in Docker

If you are a software engineer considering whether to run SQL Server in Docker, here are some of the advantages Docker offers:

1. Cost-effective and compact: You don't need to set up a dedicated server or virtual machine
2. Docker is relatively easy to set up and configure
3. You can easily automate deployment and setup with scripts
4. Docker allows you to easily create a unified environment and you can use the same docker image on any operating system including macOS, Windows or Linux

Docker is a powerful tool, and it can dramatically change the way you deploy or provision your software system.

How to Set Up a Microsoft SQL Server Database in Docker on Linux

Step 1: Download the SQL Server Docker image

You will need download Docker version 1.8 or later here to be able to set up SQL Server on Docker. If you're using Linux Ubuntu, here's how to install Docker. Check out the official website on how to install Docker on other Linux distributions.

Pull the SQL Server Docker image from the official Microsoft Docker repository using the command below. Docker will first search for an image on your PC, and if it can't find a local image, it will search for an image on remote repositories over the Internet.

sudo docker pull mcr.microsoft.com/mssql/server:2019-latest

SQL Server 2019 is the latest supported version of SQL Server on Docker at the time of writing.

Note : You can remove sudo from the above command if you have configured your Docker to run as a non-root user.

Step 2: Run the Docker image

Once the docker image is finished downloading, you can list or view all Docker images on your PC by running the following command:

sudo docker images

Output:

How to Set Up a Microsoft SQL Server Database in Docker on Linux Picture 1

If your SQL Server image is listed, you are ready to run it. But before we do, here are some Docker command parameters that you should know.

Description of Docker command parameters

1. -e "ACCEPT_EULA=Y" : Used to accept the terms of the end user license agreement
2. -e "SA_PASSWORD=Adminxyz22#" : Used to set the SA password of the Docker image. In this case, the password is set to Adminxyz22#. Make sure you use a strong password that is at least 8 characters long.
3. -p 1433:1433 : By default, SQL Server runs on port 1433. This parameter simply says to use port 1433 on the server to connect to port 1433 on the Docker image.
4. --name : Use this option to specify a name for the docker image, otherwise Docker will generate a random name for you.
5. --hostname : Use this option to assign a hostname to your SQL Server. Docker will generate a random hostname if you don't specify one.

It is important that you assign a meaningful name and hostname to your Docker image as this is what you will use in your connection strings to connect to your database.

sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Adminxyz22#" -p 1433:1433 --name sql1 --hostname sql1 -d mcr.microsoft.com/mssql/server:2019-latest

Step 3: Connect to SQL Server from Docker Container

You can connect to SQL Server instances on Docker using SQL Server clients like the command line, Microsoft SQL Server Management Studio, Azure Data Studio, etc… Azure Data Studio is lightweight and available on macOS, Windows, and Linux. Here's how to install Azure Data Studio on Ubuntu.

Let's use the Ubuntu terminal to connect to SQL Server running on Docker. First, run the following command to access the docker container terminal:

sudo docker exec -it sql1 "bash"

Once you have access to the interactive terminal on the Docker image, run the following command to connect to SQL Server:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "Adminxyz22#"

Note : The default username for the SQL Server image on Docker is SA. Also, be sure to use the correct password that you assigned to your SQL Server instance.

Once connected, you can list the available databases with the command:

SELECT Name FROM sys.Databases

Then type GO in the next prompt and press Enter to execute the SQL query:

How to Set Up a Microsoft SQL Server Database in Docker on Linux Picture 2

 The article looked at how to run SQL Server 2019 inside a Linux container on Docker. Docker is widely used by many software engineers to easily deploy applications and set up complex environments.

Isabella Humphrey

Update 09 May 2022