How to run MsSQL Server 2019 with Docker and Docker-Compose

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications which may run either on the same computer or on another computer across a network.

In this guide we are going to explore how to run MS SQL Server 2019 Locally with docker and docker compose. This can be helpful if you want to run MSSQL locally without installing it in your laptop or if you want to run multiple versions of MsSQL seamlessly.

Also check these:

Prerequisites

To follow along, ensure you have the following:

Docker installed locally and permissions to use it to launch containers
Docker compose installed locally
Basic knowledge of the command line

Running mssql using docker run command

Let’s run mssql server using the docker run like in this command. We are using the mcr.microsoft.com/mssql/server:2019-latest image in this example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
$ docker run -d \
    --name my-mssql-server \
    -p 1433:1433 \
    -v ~/apps/mssql:/var/lib/mssqlql/data \
    -e ACCEPT_EULA=Y \
    -e SA_PASSWORD=mssql1Ipw \
    mcr.microsoft.com/mssql/server:2019-latest

Unable to find image 'mcr.microsoft.com/mssql/server:2019-latest' locally
2019-latest: Pulling from mssql/server
35807b77a593: Pull complete
be2aa0ec326c: Pull complete
912596dfeaeb: Pull complete
84a6a587b3fb: Pull complete
a0ceb3206273: Pull complete
Digest: sha256:925bb075b5715b20c2acfbc244db857c92afbdfbaefbe27257a4b97817906bef
Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2019-latest
4834bb05d93f9615e06aa28e9500b68d677565c43a04cac1853915d3bdc7a0f0

In the above command:

The -d instructs docker container to run as a detached process. It run container in background and print container ID
-p is for port mapping. We are instructing the container to expose the container port externally. Container port 1433 is mapped to host port 1433. That means the service can be accessed through localhost:1433
The -v directive is used to mount volumes. In our case we are mounting the container volume /var/lib/mssqlql/datato host path ~/apps/mssql. Containers are ephemeral devices that will contain its data for the time it is running. Once a container is stopped, its data is lost. Mounting volumes ensures that the data is added to a host path that can be reused when the container is restarted.
The -e argument is for the environment variables. The supplied environment variables will be used to set up a accept mssql licence terms, and set up a password for the mssql sa user.

To check that our container is running as expected, use the docker ps command:

1
2
3
$ docker ps
CONTAINER ID   IMAGE                                        COMMAND                  CREATED          STATUS          PORTS                                       NAMES
4834bb05d93f   mcr.microsoft.com/mssql/server:2019-latest   "/opt/mssql/bin/perm..."   26 seconds ago   Up 24 seconds   0.0.0.0:1433-> 1433/tcp, :::1433-> 1433/tcp   my-mssql-server

In my case the container is running as my-mssql-server the name we gave it.

Let’s login to the container using the docker exec command while executing /bin/bash interactively. Here we are also logging in to the mssql server with the credentials we specified above and checking the version.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
$ docker exec -it my-mssql-server /bin/bash
mssql@4834bb05d93f:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA
Password:
1>  SELECT @@VERSION
2>  GO

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1 (X64)
	Sep 23 2021 16:47:49
	Copyright (C) 2019 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS) <X64>

(1 rows affected)
1>

If you need to clean up the container when not in use, you can stop and remove the container using this command:

1
2
3
4
5
$ docker stop my-mssql-server
my-mssql-server

$ docker rm my-mssql-server
my-mssql-server

Using the docker-compose tool

We can achieve the same functionality with docker-compose. Docker Compose is a tool for defining and running multi-container Docker applications. With Docker Compose, you use a YAML file to configure your application’s services.

Docker Compose allows you to define the service (MsSQL in our case) with properties like the image to use, ports to expose, volumes to mount and environment variables.

Here is how we would use docker-compose to achieve the functionality above. Save this as docker-compose.yaml:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
version: '3.9'

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - 1433:1433
    volumes:
      - ~/apps/mssql/data:/var/lib/mssqlql/data
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=mssql1Ipw

Let’s bring up the container:

1
2
3
$ docker-compose up -d
Creating network "mssql_default" with the default driver
Creating mssql_mssql_1 ... done

The commands:

up brings up the container
-d in a detached mode

Verify the container processes using the ps command:

1
2
3
4
$ docker-compose ps
    Name                   Command               State                    Ports
-------------------------------------------------------------------------------------------------
mssql_mssql_1   /opt/mssql/bin/permissions ...   Up      0.0.0.0:1433-> 1433/tcp,:::1433-> 1433/tcp

To login to the container and login to mssql, use this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
$ docker-compose exec mssql /bin/bash
mssql@bd5e569e87f2:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA
Password:
1>  SELECT @@VERSION
2>  GO

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1 (X64)
	Sep 23 2021 16:47:49
	Copyright (C) 2019 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS) <X64>

(1 rows affected)
1>

Conclusion

In this guide, we managed to run MsSql 2019 as a container in our system, We explored using the docker run command while passing the required arguments an alternative approach of simplifying the process with docker-compose.