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:
- Installing Docker
- Getting SQL Server 2019
- Running SQL Server 2019 in a Docker Container
- Restoring Demo Databases (AdventureWorks and WideWorldImporters)
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.
Once you are done pulling the image, you can find it in Docker:
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 in Docker:
Or 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:
- 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
About the Author
Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, coffee, chocolate, and cats 🤓