Containerized SQL Server on Docker

With Docker containers, SQL Server becomes simpler and easier to manage

I am not a fan of Microsoft’s SQL Server installation program. This historically complex and unforgiving installation wizard, which has dozens of steps and has tons of potential pitfalls, is punishingly difficult and finicky — especially when compared to the ease of installation of Postgres.

That said, nowadays I don’t even install Postgres without creating a virtual instance using a container application like Docker. What if we did the same thing with SQL Server?

Let’s walk through how we can set up SQL Server quickly and easily using containers.

Containers make database servers significantly easier to set up (Flickr)

Installing SQL Server on Docker

The first step is to get Docker on your computer.

• If you’re on a Mac, you can install Docker Desktop or use homebrew to install just the docker service.
• On Windows, you can download Docker Desktop for Windows — it will require you to reboot, but it will only ask you one question: whether you want to use Windows Subsystem for Linux 2.0. Say yes and follow these configuration instructions to make sure you are on WSL 2.0.

Microsoft helpfully provides instructions for installing SQL Server on Docker — and they’re far easier than installing SQL Server directly. In fact, it only requires two commands.

A few important things to know before you start:

• SQL Server is very finicky about passwords. Please be sure there are no characters with encoding conflicts in your password; if you need to use a symbol use something safe like a hyphen or exclamation mark.
• On a Mac, you must prefix docker commands with sudo. On Windows, you need to make sure Docker Desktop is running prior to executing these commands.

Here you go:

# This command fetches the latest image from Microsoft
$ docker pull mcr.microsoft.com/mssql/server:2019-latest

# This command launches SQL Server
$ docker run -e "ACCEPT_EULA=Y"
   -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>"
   -p 1433:1433 --name sql1 --hostname sql1
   -d mcr.microsoft.com/mssql/server:2019-latest

Wow! This is way simpler than a twelve-step process running a complex installation wizard. Since the majority of us will use cloud managed databases from Amazon or Azure for our production environments, we don’t need our local servers to be perfectly tuned.

Once your instance is running, you can use Docker to execute commands directly:

# Check to see if Docker is running - lists all active containers
$ docker ps

# This command will execute the SQL statement in double quotes
# after first prompting you for your password
$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd 
    -S localhost -U sa -Q "SELECT @@SERVERNAME"

There’s a small quirk to connection strings using Docker. When you connect via SQL Server management studio, you’ll need to specify the containername in addition to localhost and the port number. Instead of localhost:1433, you’ll specify localhost\sql1,1433. Here’s what it looks like in practice:

In this example, I set up a container named “sql1” that uses port 1434

Once you’ve launched your SQL Server — it will take a few minutes to fully launch — you can manage it the same way as any other SQL server. Here’s a screenshot of SQL Server Management Studio connecting to my local Docker instance:

Once you’ve launched SQL Server on Docker, it works like any other SQL Server

How to handle computers that can’t run Docker Desktop

At Bellevue College I occasionally find students whose computers won’t run Docker Desktop. If you find that you can’t run Docker Desktop locally, you can still use containerized database servers with a virtual machine running Ubuntu. Here’s how to set it up.

• Install VirtualBox and download the Ubuntu installation disk image.
• Create a new virtual machine in VirtualBox, with at least 4GB of memory and 40GB of disk space.
• Add the Ubuntu installation disk image to the optical drive for your virtual machine.
• Power it on and follow the instruction steps, including choosing your keyboard and language. You’ll need to choose a username and password for the root account on your VM; write this down. It will be different from your database server username and password.

When the Ubuntu virtual machine installation is finished, you’ll configure virtual machine additions to make the VM easier to manage:

• Remove the installation ISO image from the optical drive of your virtual machine.
• Reboot the virtual machine.
• Log onto the virtual machine.
• Choose “Install Guest VM Additions” from the menu and follow the instructions.
• Set Clipboard mode to bidirectional.
• It will ask you to reboot a second time.

Next you’ll install Docker:

• Log into your virtual machine.
• Open the “Terminal” program and copy and paste the instructions from the Install Docker on Ubuntu page.

Once this is finished, you’ll need to execute the commands at the top of this page to install and run the SQL Server container.

Ted Spence heads engineering at ProjectManager.com and teaches at Bellevue College. If you’re interested in software engineering and business analysis, I’d love to hear from you on Mastodon or LinkedIn.