Microsoft SQL Server is a robust and widely used database management system (DBMS). Traditionally, SQL Server databases were set up on dedicated servers or virtual machines, but Docker changed all that.
Let’s see how to set up a SQL Server instance on a Linux container with Docker.
Benefits of running SQL Server in Docker
If you’re a software engineer wondering if you should run SQL Server in Docker, here are some of the benefits Docker offers:
- Economical and lightweight: You don’t need to set up a dedicated server or virtual machine
- Docker is relatively easy to install and configure
- You can easily automate the deployment and configuration process with scripts
- Docker lets you 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 that can dramatically transform the way you deploy or provision your software systems.
Step 1: Obtain the SQL Server Docker image
You will need Docker version 1.8 or higher to be able to configure SQL Server on Docker. If you’re using Ubuntu Linux, here’s how to install Docker. Check Docker official site 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 look for the image on your PC, and if it can’t find it locally, it will look for the 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 this writing.
You can deposit sudo from the above command if you have configured your Docker to run as a non-root user.
Step 2: Run the Docker image
After the Docker image download is complete, you can list or view all Docker images on your PC by running the following command:
sudo docker images
If your SQL Server image is listed, you are ready to run it. But before you do, here are some Docker command parameters you need to know.
Description of Docker command parameters
- -e “ACCEPT_EULA=Y”: Used to accept the terms of the End User License Agreement
- -e “SA_PASSWORD=Adminxyz22#”: Used to set the HER Docker image password. In this case, the password is set to Adminxyz22#. Be sure to use a strong password of at least eight characters.
- -p 1433:1433: By default, SQL Server runs on port 1433. This setting 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 to assign a hostname to your SQL server. Docker will generate a random hostname if you don’t assign one.
It’s important that you give your Docker image a meaningful name and hostname, because that’s what you’ll 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 instance on Docker using SQL Server clients like 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 Docker container terminal:
sudo docker exec -it sql1 "bash"
When you have accessed 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#"
The default username for the SQL Server image on Docker is HER. Also remember to use the correct password you assigned to your SQL Server instance.
Once connected, you can list the available databases using the command:
SELECT Name FROM sys.Databases
Then type TO GO in the next prompt and press Walk in to run your SQL query:
Running Microsoft SQL Server on a Docker container
We looked at how to run SQL Server 2019 in a Linux container on Docker. Docker is widely used by many software engineers to deploy applications and configure complex environments easily.
6 Reasons to Use Docker Virtualization Software
About the Author