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. Now-a-days, we have so many more options.

There are many options to run SQL Server!

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 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 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 latest 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 comes with it. Whether you have a MacOS or Windows machine, this can be annoying

I have a tenancy to be a clean freak (both virtually and otherwise) so, I’m definitely not a big fan of having clutter on my machine. This is where running SQL Server in a Docker container can really help. As I mentioned, a Docker container is a self-contained virtual environment. This means, we don’t have to install anything! 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 and is supported on both MacOS and Windows. Once Docker is installed, we have to get our SQL Server container running and what better way than to use 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 however, I like to use it in this case as I don’t have to type out all of the command-line parameters out in a single command. 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 just have to browse to the directory where we placed out docker-compose.yml file and execute the following command.

docker-compose up

This 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 in 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! Personally, 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.