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-CTP2.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 used the SQL Server 2019 CTP2.2 image. 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.

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-CTP2.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

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, Microsoft Certified Solutions Expert, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, architect and developer, focusing on Data Warehouse and Business Intelligence projects. 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. Thanks!

Click to Show / Hide Comments