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

Let's take a look at how you can set up 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 you should run SQL Server in Docker, well, here are some of the advantages that Docker offers:

  • Cost-effective and lightweight: You do not need to set up a dedicated server or virtual machine
  • Docker is relatively easy to set up and configure
  • You can easily automate the deployment and setup process with scripts
  • Docker allows you to easily create uniform environments, 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 hugely transform how you deploy or provision your software systems.

Step 1: Getting the SQL Server Docker Image

You'll need Docker version 1.8 or above to be able to set up SQL Server on Docker. If you are using Ubuntu Linux, here's how to install Docker. Check out the official Docker site on how to install Docker on other Linux distros.

Pull the SQL Server Docker image from the official Microsoft Docker repository using the command below. Docker will first look for the image on your PC, and if it does not find it locally it will search for the image on remote repositories via 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 this writing.

You can drop sudo from the command above if you've configured your Docker to run with a non-root user.

Step 2: Running 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:

docker_images_list

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

Docker Command Parameters Description

  • -e "ACCEPT_EULA=Y": Used for accepting End-User License Agreement terms
  • -e "SA_PASSWORD=Adminxyz22#": Used for setting 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 eight characters long.
  • -p 1433:1433: By default, SQL Server runs on port 1433. This parameter simply says: use port 1433 on the host machine to connect to port 1433 on the Docker image.
  • --name: Use this option to specify a name for your docker image, otherwise, Docker will generate a random name for you.
  • --hostname: Use this option for assigning a hostname to your SQL Server. Docker will generate a random hostname if you don't assign one.

It is important that you assign a meaningful name and hostname to your Docker image because 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: Connecting to the SQL Server From Docker Container

You can connect to the SQL Server instance on Docker using SQL Server clients such as 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 the SQL Server running on Docker. First, run the following command to access the terminal of the docker container:

        sudo docker exec -it sql1 "bash"
    

When you've accessed the interactive terminal on the Docker image, run the following command to connect to the SQL Server:

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

The default username for the SQL Server image on Docker is SA. Also, remember to use the correct password that you've assigned to your SQL Server instance.

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

        SELECT Name FROM sys.Databases
    

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

sql server docker image queries

Running Microsoft SQL Server on a Docker Container

We've looked at how to run SQL Server 2019 inside a Linux container on Docker. Docker is widely used by many software engineers for deploying applications and setting up complex environments with ease.