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.
- Docker For Windows Access Denied Docker Users
- Install Docker Windows Server 2019 Vm
- Install Docker Enterprise Windows Server 2019
- Docker Windows Server Core Image
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.
If this is the first installation of Windows Server 2019 on the server, select (Custom: Install Windows only). Select a partition to install Windows Server, you can optionally create new one from available or use total available size by clicking “ Next “.
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)
Run the below 2 commands to install Docker using powershell. Type “y” and press enter to every questions asked. PS Install-Module -Name DockerMsftProvider -Repository PSGallery -Force. PS Install-Package -Name docker -ProviderName DockerMsftProvider. You have now Docker installed on your 2019 server core. Nowadays it might be confusing if people want to run choco install docker and 'just' get the Docker CLI without any Docker Engine. We're in discussion with the Chocolatey team how to softly fix this and transfer the Docker CLI into a new package name called docker-cli to make it more clear. Instructions on how to run Linux Docker Container on Windows Server 2019 Topics Docker Installation Docker is available in three tiers: Docker Engine – Community Docker Engine – Enterprise Docker Enterprise Docker Engine – Community is ideal for individual developers and small teams looking to get started with Docker and experimenting with container-based apps.
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 :) #Dockerpic.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:
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:
Docker For Windows Access Denied Docker Users
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:
I name my container SQL19 and specify that I want to map my local folder C:DockerSQL 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:
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:
You can now connect from SQL Server Management Studio (SSMS) to your container as SA using your <SuperStrongPassword>:
Install Docker Windows Server 2019 Vm
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:DockerSQLBackup 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:DockerSQL 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:
Summary of SQL Server 2019 in Docker
Install Docker Enterprise Windows Server 2019
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:
Docker Windows Server Core Image
- 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)
About the Author
Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, international speaker, author, blogger, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, chocolate, coffee, and cats 🤓