Running a SQL Server Instance in Docker

I've found it very convenient to have a local instance of SQL Server. I use it for:

• quick tests
• performance testing
• demonstrations
• development
• testing
• work around limitations of a cloud instance

This has become really easy with containers. Even if you have the ability to install SQL Server natively on the host (Windows or Linux), it is much quicker and easier to use containers. 

This means that you can run an instance of SQL Server on Windows, Linux, and Mac. 

The first time I did this, I followed Bob Ward's Take the SQL Server Mac challenge, and had a SQL instance running on my Macbook, while in the audience listening to Bob's presentation. Yep, under 5 minutes.

So here is my pared-down How-To. I've tried to do a minimal setup that should work on Win, Linux, Mac, with very little adjustment.

Here's the overview:

1. Download, install, and run Docker
2. Get to an (elevated?) command prompt
3. Run a long-ish docker command
4. Verify via Docker
5. Connect from Host with client 
6. Shut it down

1. Docker Installation

Head over to Docker and download Docker Desktop. I recommend the Stable version for whichever platform you are on:

• Docker for Windows Stable Version
• Docker for Mac Stable Version
• Docker for various Linux flavors

Follow the installation instructions for your platform. This varies slightly for the platform you are on. If you can install a program on your machine, you can do this.

2. Command Prompt

For Mac, use the Terminal Application to get a command line. Here's how.

For Windows, type cmd in the search area, and choose "Run As Administrator". Here's how. Administrator privileges are not required for everything, but use it this first time to make sure you can get it working.

3. Run Docker Command

This is a long-ish command, but all the pieces are necessary. There are various ways to do this, but i'm using a "quick" method found in Microsoft's documentation. This method both pulls the SQL Server 2019 container image, and runs it with the correct parameters. Here's the command that I used:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=123ABCabc" -p 1401:1433 --name sqlsrvr1 -h -sqlsrvr1 -d mcr.microsoft.com/mssql/server:2019-latest

Here's the code again, broken into multiple lines. Note the Windows continuation character ^ (caret) at the end of each line:

docker run -e "ACCEPT_EULA=Y" ^
  -e "SA_PASSWORD=123ABCabc" ^
  -p 1401:1433 ^
  --name sqlsrvr1 ^
  -h sqlsrvr1 ^
  -d ^
  mcr.microsoft.com/mssql/server:2019-latest

Either of the above should work via cut-and-paste, but I find it easier to read and visually verify the one-parameter-per-line version.

The basic command (without options, which won't work) is:

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

This creates a process that runs the image mcr.microsoft.com/mssql/server:2019-latest. If that image happens to be on the local machine, great. If not, it will download the image from mcr.microsoft.com.

Note the "tag" at the end of the image url: 2019-latest. Microsoft releases many container images from which you can choose. Find them all on DockerHub. With this tag, are basically saying, get the latest stable release of SQL Server 2019. 

That basic command will not create a usable sql server container. You need some additional pieces:

• -e "ACCEPT_EULA=Y"
• passes "Y" to SQL Server, indicating acceptance of the End User License Agreement (EULA)
• -e "SA_PASSWORD=123ABCabc"
• passes the "sa" user password to SQL Server
• you will log in to this server using SQL Authentication, with "sa" as the username, and "123ABCabc" as the password
• I'm using "123ABCabc" because it satisfies the password policy requirements (9 chars, upper and lower, digits"
• "sa" is short for system administrator, and is basically the root user on the server instance that you are running
• -p 1401:1433
• maps the container's port 1433 to your machine's port 1401
• SQL Server "listens" for connections on this port by default
• So if you connect to your host machine's port 1401, you will be connected to the container's port 1433
• in my situation, I am running a SQL instance on the host that is already using the default port 1433.
• --name sqlsrvr1
• this gives the container your desired name, rather than a docker-generated name
• when issuing commands on the host, you can refer to the container by this name
• -h sqlsrvr1
• this sets the HOSTNAME environment variable inside the container, rather than the docker-generated name
• when issuing commands inside the container, you can refer the system inside the container by this name
• -d
• this runs the container "detached"
• this means that the container will run in the background rather than the foreground

4. Verify via Docker 

If you've run the command in the previous section, you should have a running container based on the mcr.microsoft.com/mssql/server:2019-latest image. Run this at the command line to see it:

docker ps

This command gives a list of the running containers. You should see results something like this:

CONTAINER ID   IMAGE                                      COMMAND                CREATED        STATUS        PORTS                     NAMES
d325650811b1   mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 43 minutes ago Up 43 minutes 0.0.0.0:1401->1433/tcp sqlsrvr1

The results indicate that the container is running, with the proper name, and listening on the proper port

Verify via Connection

Now fire up a SQL client to connect to the container, and use it. I'm using Azure Data Studio (ADS), which will run on Windows, MacOS, and Linux. Here's the "new connection" dialog in ADS

Just to be clear:

• ADS is running on the host
• it is connecting to the host IP address (localhost)
• it is connecting to the host Port 1401
• Docker has mapped host port 1401 to the container's port 1433, which is the default port for SQL Server
• So ADS will be connected to the container's port 1433
• Note the Server text box has "url, port" - there's a comma between the url and the port

You should see that your container is shown in the Server Explorer on the left-hand side of the ADS window. 

6. Shut it down

You can, of course, leave this container running in the background and use it whenever you need it. But what if you want to shut it down? Or entirely get rid of the container? Here's how to do it:

docker stop sqlsrvr1

This stops the container. It will no longer be running in the background. The container is still there, and can be restarted like this:

docker start sqlsrvr1

To entirely remove the container from the host machine (it cannot be started, and will no longer take up space on your host's drive), you would issue this command on the stopped container:

docker rm sqlsrvr1

Summary

Docker can seem overwhelming at first, especially if you are not comfortable working at the command line. But if you are looking to run a local instance of SQL Server, running a Docker container is the fastest path.