Creating a SQL Server 2019 Demo Environment in a Docker Container

About a month ago, I learned something new. I learned how to run SQL Server 2019 in Docker and how to set up my demo environment in a container. Cool stuff! I like whales. Whales are cool.

While learning, I started writing this blog post. Then I got distracted and never finished it. This weekend, I had to set up my demo environment again. It was the perfect opportunity to update the content and finally publish this post.

(Why did I have to set up everything again? Oh, it’s a long story that involves disk cleanup and a Cathrine who likes to delete things to keep her computer tidy. Ok, it’s not really a long story. It was more like “oops, I accidentally deleted my container”.)

Anyway! Back to the actual content.

In this post, I share my approach and code snippets for:

1. Installing Docker
2. Getting SQL Server 2019
3. Running SQL Server 2019 in a Docker Container
4. Restoring Demo Databases (AdventureWorks and WideWorldImporters)

Installing Docker

I knew nothing about Docker or containers a month ago. But! I’m lucky to have smart friends :) Andrew Pruski (@dbafromthecold) wrote Running SQL Server 2019 CTP in a Docker container as part of his brilliant blog post series on containers.

I decided to start with his walkthrough and do exactly what he did. It worked pretty well for me! See below :)

Current status: I have absolutely no idea what I'm doing, but I now have a whale swimming on my task bar :) #Docker pic.twitter.com/7JE7RYhZ9b

— Cathrine Wilhelmsen (@cathrinew) October 22, 2018

Getting SQL Server 2019

Once you have Docker installed, pull the SQL Server 2019 image by executing the following code in PowerShell:

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

Pulling an image basically means downloading all the files needed to run a container. You can think of this as downloading the SQL Server installation files.

I originally used the SQL Server 2019 CTP2.1 image, but the code in this post has been updated to a newer version. If you want to run a different version of SQL Server, you can find a list of all the available images on the Microsoft SQL Server page on Docker Hub. You can also get the list of images programmatically.

Running SQL Server 2019 in a Docker Container

When I started writing this post a month ago, I followed Microsoft’s blog post and copied files into my container. Since then, I have learned a few new things. I now run my containers with a bind mount (shared drive). This means that I have access to a local folder on my computer from inside my docker container.

First, make sure to enable Shared Drives in Docker:

Then, run the container by executing the following code in PowerShell:

docker run `
--name SQL19 `
-p 1433:1433 `
-e "ACCEPT_EULA=Y" `
-e "SA_PASSWORD=<SuperStrongPassword>" `
-v C:\Docker\SQL:/sql `
-d mcr.microsoft.com/mssql/server:2019-latest

I name my container SQL19 and specify that I want to map my local folder C:\Docker\SQL to the container path /sql. For more information about the other options, read the Get Started article by Microsoft.

Running a container is similar to installing a local SQL Server instance. Just much faster and easier :)

Finally, verify that the container is running by executing the following code in PowerShell:

docker ps -a

The ps stands for Process Status, and -a will show all processes regardless of status. If the container is not running, you have to (re)start it:

docker start SQL19

You can now connect from SQL Server Management Studio (SSMS) to your container as SA using your <SuperStrongPassword>:

If you specified a different port when running the container, you will have to connect using the port number, for example localhost,1401

Restoring Demo Databases

I had already downloaded the AdventureWorks and WideWorldImporters .bak files to my C:\Docker\SQL\Backup folder.

If you open Restore Database from SQL Server Management Studio (SSMS) and navigate to Locate Backup File, you will see the /var/opt/mssql/data path:

Now… Here’s where I got confused. The current path is using forward slashes. When I ran the container, I also mapped my local folder C:\Docker\SQL to the container path /sql. To me, it was logical to try /sql:

Nope! No can do. You have to use \sql:

I have no idea why, and I haven’t figured out if this is a bug or by design. (Do you know? Please comment!) I can only guess that since SSMS is a Windows application, it is using Windows paths (with backslashes), even though it is showing the Linux paths (with forward slashes).

Anyway! Select your .bak file…

…aaaaand SUCCESS! :D

Yay! The WideWorldImporters demo database is now restored to SQL Server 2019 running in a Docker container:

How cool is that? Just repeat the process for any remaining databases. Mission accomplished :)

Restore Demo Databases Script

If you do this more than a few times a year, definitely automate the restore process using T-SQL or PowerShell! I use the following, standard script:

USE [master];
GO

RESTORE DATABASE [AdventureWorks] 
    FROM DISK = N'/sql/Backups/AdventureWorks2017.bak' 
    WITH FILE = 1, 
    MOVE N'AdventureWorks2017' TO N'/var/opt/mssql/data/AdventureWorks.mdf', 
    MOVE N'AdventureWorks2017_log' TO N'/var/opt/mssql/data/AdventureWorks_log.ldf', 
    NOUNLOAD, STATS = 5
GO

RESTORE DATABASE [AdventureWorksDW] 
    FROM DISK = N'/sql/Backups/AdventureWorksDW2017.bak' 
    WITH FILE = 1, 
    MOVE N'AdventureWorksDW2017' TO N'/var/opt/mssql/data/AdventureWorksDW.mdf', 
    MOVE N'AdventureWorksDW2017_log' TO N'/var/opt/mssql/data/AdventureWorksDW_log.ldf', 
    NOUNLOAD, STATS = 5
GO

RESTORE DATABASE [AdventureWorksLT] 
    FROM DISK = N'/sql/Backups/AdventureWorksLT2017.bak' 
    WITH FILE = 1, 
    MOVE N'AdventureWorksLT2012_Data' TO N'/var/opt/mssql/data/AdventureWorksLT.mdf', 
    MOVE N'AdventureWorksLT2012_Log' TO N'/var/opt/mssql/data/AdventureWorksLT_log.ldf', 
    NOUNLOAD, STATS = 5
GO

RESTORE DATABASE [WideWorldImportersDW] 
    FROM DISK = N'/sql/Backups/WideWorldImportersDW-Full.bak' 
    WITH FILE = 1, 
    MOVE N'WWI_Primary' TO N'/var/opt/mssql/data/WideWorldImportersDW.mdf', 
    MOVE N'WWI_UserData' TO N'/var/opt/mssql/data/WideWorldImportersDW_UserData.ndf', 
    MOVE N'WWI_Log' TO N'/var/opt/mssql/data/WideWorldImportersDW.ldf', 
    MOVE N'WWIDW_InMemory_Data_1' TO N'/var/opt/mssql/data/WideWorldImportersDW_InMemory_Data_1', 
    NOUNLOAD, STATS = 5
GO

RESTORE DATABASE [WideWorldImporters] 
    FROM DISK = N'/sql/Backups/WideWorldImporters-Full.bak' 
    WITH FILE = 1, 
    MOVE N'WWI_Primary' TO N'/var/opt/mssql/data/WideWorldImporters.mdf', 
    MOVE N'WWI_UserData' TO N'/var/opt/mssql/data/WideWorldImporters_UserData.ndf', 
    MOVE N'WWI_Log' TO N'/var/opt/mssql/data/WideWorldImporters.ldf', 
    MOVE N'WWI_InMemory_Data_1' TO N'/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1', 
    NOUNLOAD, STATS = 5
GO

Summary of SQL Server 2019 in Docker

In short, it probably takes longer to read this post than to actually create a demo environment in a Docker container. If you already have Docker installed and a copy of your .bak files, it’s as easy as:

1. Pull
2. Run
3. Restore!

Resources

• Running SQL Server 2019 CTP in a Docker container (by Andrew Pruski)
• Quickstart: Run SQL Server container images with Docker (Microsoft Docs)
• Restore a SQL Server database in a Linux Docker container (Microsoft Docs)
• Use SQL Server Management Studio on Windows to manage SQL Server on Linux (Microsoft Docs)
• Microsoft SQL Server page on Docker Hub