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