Skip to content

Creating a SQL Server 2019 Demo Environment in a Docker Container

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 :)

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-CTP3.2-ubuntu

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:

Docker Shared Drives

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-CTP3.2-ubuntu

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>:

Connect to Container from SSMS

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:

Default SSMS Restore Database 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:

SSMS Restore Database Path: Forward Slash Path Error

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

SSMS Restore Database Path: Backslash Path Success

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…

SSMS Restore Database

…aaaaand SUCCESS! :D

SSMS Restore Database Success

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

Restored Database in SSMS

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

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

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, Microsoft Certified Solutions Expert, international speaker, author, blogger, and chronic volunteer who loves teaching and sharing knowledge. She works as a Senior Business Intelligence Consultant at Inmeta, focusing on Azure Data and the Microsoft Data Platform. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Hi Catherine! The “slash” issue is interesting. If you have your backups just in the top “/sql” directory, as opposed to a directory below (such as /sql/backups, like you), then SSMS will throw an error with not being able to find the backup, and the backup media path will be mixed. In my example, it was “\sql/wwi.bak”. Using “restore headeronly from disk=’/sql\wwi.bak'” in SSMS works, however.
Interestingly enough, the “Backup File Location” stays at “\sql”, until you browse to another lower directory, then it flips to “/sql/”
Tested this with SSMS SQL Server Management Studio 15.0.18068.0. My guess is bug, but not sure.

If you try entering “/sql” then hit the refresh button/icon/arrow/whateveryouwanttocallit it’ll work. Typing “/sql” then pressing enter throws the error – as to why, I’ve never got back to fully investigating so have no idea other than a potential bug ;)

Cathrine, thanks very much for this post. Other than the fact I need to remember that reading is fundamental, it worked like a charm. One problem I ran into was my password didn’t meet the strength requirements, so my container wouldn’t start, though docker start didn’t throw an error. I had to run docker logs to figure that one out.

Nice post. Would also add that docker can give lot of weird behavior if you have installed it on a Corp laptop with Trend AV.
Had to do some exceptions in the AV for certain folders to get it running.

You may find this documentation very useful in the future, as did I. Persistence of data over the life of multiple containers.

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-docker?view=sql-server-2017#persist

SSMS didn’t really want to play so i ended up restoring the databases from script. Otherwise excellent.

I am trying to find out how to enable full context, so I can create adventureworks, running on a docker image on mac. configuration assistant is not available any ideas? Thanks

Hi Cathrine. Thanks for this post. I’ve tried to do this a few times, but just got errors I didn’t understand. Following your example got me there in minutes!

Awesome!!! Thank you so much.

Hi! This is Cathrine (again). Just a reminder. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Leave a Reply to Chris Taylor Cancel reply