Exploring SQL Server 2019 with Docker

I was showing SQL Server 2019 to some aspiring .NET developers recently. Aside from discussing relational database concepts, we also dug into various SQL statements and queries. For those of you who are already familiar with SQL Server, you will probably agree that the best way to learn SQL is to get your hands dirty. Typically, I would encourage downloading SQL Server Developer Edition; however, half of the individuals I was working with had Mac Books! Same situation just a couple years ago, it would have been tough luck. Nowadays, we have so many more options.

SQL Server on Linux

Just a few years ago, I remember stumbling across a tweet that claimed SQL Server could run on Linux. As a .NET developer, I was in shock! In fact, I almost couldn’t believe my eyes. But “this is not your father’s Microsoft,” so rather than sit in disbelieve, I decided to take the plunge and install SQL Server on a Linux machine I had handy. Within just a couple of minutes, I had everything up and running. Today, Microsoft offers tons of tutorials, quickstarts, and documentation, which reinforces their embrace of this new SQL Server configuration.

SQL Server on Docker

Now back to the story at hand, I mentioned that many of these newbie developers had Mac Books, not Linux machines. Fortunately, the story doesn’t end there. We can also run SQL Server in a Docker container. Docker allows us to run and manage applications in a self-contained virtual environment. These virtual environments are most commonly run with a Linux operating system. This provides an avenue to run SQL Server on macOS! If you don’t have a MacBook, don’t feel left out. Running SQL Server as a Docker container can be very beneficial to those running Windows as well.

SQL Server has been very successful in the enterprise over the years. Every couple of years, a new version is released, boasting the latest features and improvements. As a developer, we love to install the latest version as they are released, and why not? Who doesn’t love using the newest version of a product? Unfortunately, this leads to having multiple versions of the database engine installed on your machine and all of the tools that come with it. Whether you have a macOS or a Windows machine, this is not always very pleasant.

I have a tenancy to be a clean freak (both virtually and otherwise) so, I’m not a big fan of having clutter on my machine. Running SQL Server in a Docker container can help keep our computer clean. As I mentioned, a Docker container is a self-contained virtual environment, which means we don’t need to install anything on our machine! Just spin up a container, and away you go!

Getting Started

If you have used Docker in the past then, you probably already have Docker Desktop installed. If not, installation is a breeze on both macOS and Windows. Once Docker is installed, we have to get a SQL Server container running and what better way than using Microsoft’s official SQL Server Docker Image. We could get things started directly from the command-line; however, I like to use Docker Compose. Typically Docker Compose is used to run multiple containers concurrently but, it is also beneficial in single container situations. Parameters can be provided in a YAML configuration file rather than command-line parameters, which can be challenging to remember. Here is an example of the docker-compose.yml file I use.

version: "3.7"
services:
    db:
        container_name: mssql2019
        image: mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-16.04
        user: root
        environment:
            SA_PASSWORD: "Password."
            ACCEPT_EULA: "Y"
        ports:
            - 1433:1433
        volumes: 
            - mssql-volume:/var/opt/mssql
volumes:
    mssql-volume:

Now, we have to browse to the directory where we placed our docker-compose.yml file and execute the following command.

docker-compose up

This command will create our container and volume if they do not already exist, then start the container. The volume allows us to persist our data outside of the container. Meaning if we stop/start or even rebuild the container, our data, including the databases, will still be there.

Restoring the AdventureWorks Database

Lastly, to truly explore SQL Server 2019, some sample data is needed. Luckily, Microsoft has provided the AdventureWorks database exactly for this reason. But how can we get our backup in a location that it can be restored from within the container? Using the docker cp command, we can copy the backup from our local file system to the contain. See the following command as an example.

docker cp AdventureWorks2019.bak mssql2019:/var/opt/mssql/data/AdventureWorks2019.bak

With the container copied, we can now restore our database. SQL Server Management Studio (SSMS) is up to the task; however, it can only be installed on Windows. Using Azure Data Studio is a great way to stay platform agnostic. With Azure Data Studio, restoring the database can be accomplished in only a few steps; however, it lacks a couple of the more advanced administrative features that SSMS provides.

Closing Thoughts

I hope you found this article helpful! I found it encouraging that using a Mac is no longer a reason to stop you from testing the water with some traditionally Microsoft only technologies like .NET Core and SQL Server.