Infrastructure at your Service

Steven Naudet

Getting started with SQL Server on Linux containers with Docker Desktop and WSL2

In this blog post, I will show you the steps I went through to run SQL Server inside a Linux Docker container on my Windows 10 laptop.
The aim here is to deploy quickly and easily SQL Server instances in my lab environment.

Introduction

Since May 2020 Windows ships with WSL 2. Windows System for Linux is a feature that creates a lightweight environment that allows you to run Linux distributions (Debian, Ubuntu, etc.) without having to set up a VM or dual-booting. WSL has been around for a while but with WSL 2 the architecture has been reconfigured to run a Linux kernel, adding full system call compatibility and increasing file system performance. You can now run Docker in your Linux distro on Windows with WSL 2.

In this blog post, I decided to install the Docker Engine on Windows with Docker Desktop but you could also install Docker directly on a WSL2 Linux distro.

We will see the following topics

– WSL 2 installation
– Docker Desktop installation
– Run a SQL Server container
– Connect to the SQL Server instance with SSMS
– Enable the SQL Server Agent
– Create a custom image
– Transfert a database backup file

WSL 2 installation

The requirement to use WSL 2 is to have Windows 10 version 1903 or higher with Build 18362 or higher.

So the first thing is to check the windows version and build number. It can be done with PowerShell with Get-ComputerInfo.

Let’s check if WSL is already enabled:

You can enable it using the command below (in an administrative PowerShell). You will have to restart your computer after that.

Once enabled WSL 2 needs to be updated.
To do so you need to download and run the latest WSL 2 update package.

Docker Desktop installation

Download the latest Docker Desktop for Windows installer from Docker Hub.
The installation is pretty straightforward.

Now that Docker Desktop is installed you can go into the Docker Settings and verify that the “Use the WSL 2 based engine” option is checked.

Now that Docker Desktop is installed you can use the following command to list distributions installed on your computer. Docker Desktop comes with 2 distributions and we can verify what version WSL we have.

Run an SQL Server container

We are now all set up.
To start working with a container we first need to choose a container image to run from.
The list of official SQL Server from Microsoft is available on Docker Hub. I will choose the 2019 CU8 on Ubuntu 16.04 one.

To get the image from the online registry just run the “docker pull” command. Docker commands can be used in PowerShell.

We can list the available images.

To start a new container from the image the command is “docker run”. I specify a name for my container and the SA account’s password.

My container is now Up and running. I can check the status with “docker ps”.

Connect to the instance with SSMS

I mapped the SQL Server default port 1433 to the instance port inside the container (also 1433) so I can just connect with SSMS using “localhost” as Server name:

That’s it. I’m now connected to my instance.

If I need a new instance again I can run the same command just changing the container name and the port.

I can confirm the instance is running querying the DMV sys.dm_os_host_info.

Enable the SQL Server Agent

On the screenshot above we can see the SQL Server Agent is disabled by default. It can be enabled in T-SQL from the instance:

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Agent XPs',1
reconfigure

The SQL Server Agent could be enabled at the run with an SQL Server environment variable ;  MSSQL_AGENT_ENABLED. Here is an example;

Create a custom image

Now in T-SQL I will create a database “TestDB” with a table and insert rows inside.

create database testDB
go
use testDB
go
create table testTable (
	id int identity primary key
	, colA char(4)
)
go
insert into testTable(colA) values('test')
go 2020

 

To create an image from my “sql2019lab” container I first need to stop it.

Then I need to run docker commit.

The image is ready. The image has no repository or tag. I could upload my custom image to Docker Hub and run it from anywhere but I’ll keep everything locally for now. I can run a container from my new image.

When I connect to the instance on my new “sql2019lab3” container the SQL Server Agent is already running and my database is there.

Transfert a database backup file

It’s quite easy to transfer a file from Windows to a running container with the command “docker cp”.
Here I copy a database backup into the default directory /var/opt/mssql/data

The backup file can be restored from SQL Server in T-SQL or using SSMS.

Conclusion

In conclusion, we’ve seen how easy it is to set up Docker on Windows and run containers.
It’s not more complicated than installing SQL Server on a VM with Hyper-V or VirtualBox.
Once the image has been downloaded a new SQL Server instance can be deployed in a few seconds.
I hope you enjoyed this short introduction to SQL Server in containers.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Steven Naudet
Steven Naudet

Consultant