SQL Server and Docker is easy as 1, 2, 3

Introduction

In this short article I’m going to show you how to run and connect to a SQL Server instance running on docker.

If you’ve never used Docker before then I highly recommend you give it a try for your next development project. I find docker easy to set up and work with. It removes the need to install SQL on your laptop or add another development server somewhere. Instead you can run a container like its another server / service but its local on your machine.

Install docker desktop

Just to note, I’m doing this from a Windows 10 pro machine. So firstly head over to docker.com and get yourself signed up. This is important because you’ll need an account in order to pull container images. Once you’ve done that you can go through the process of downloading docker desktop here or find the link on their site.

More useful documentation can be found here to help you.

When going through the process you’ll be given the option to run Linux or Windows containers (which you can change later) but for this I’m choosing Windows.

Run your docker container

You can interact with docker in either PowerShell or cmd line so your choice. When we run the container docker will use an existing image or download a new one. Details can be found here, but we’ll be running developer edition of SQL Server.

docker run -d -p 1433:1433 -e sa_password=<Str0ngPassw0rd> -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

A couple of things to note before you run this command, firstly if you’re already using port 1433 for a local SQL Instance then you’re need to change the port number (the left of the colon).

Second, you will need to create a password that complies with the password policy, found here.

So once done then run!

Connect to SQL

So to connect to my SQL Server docker instance I’m going to use SSMS (SQL Server Management Studio). All I need to do now is enter the server name as localhost with the port number I assigned and connect using the sa account with the strong password I created.

and voila!

Wait… what about the database?

So I have my SQL Instance but obviously I need a database to work with.

What I’m going to do is recreate the container but this time input a volume so the SQL Instance can see a bak file. I’m going to use the Microsoft sample database WideWorldImporters.

If you’ve already done the above then stop and remove your container before continuing.

docker stop <containerid> #stop the container running

docker rm <containerid> #remove the container we've stopped

Adjusting my docker run command below I’ve added the container path c:\sqldata pointing to my local c:\temp

docker run -d -p 1433:1433 --volume c:\temp:c:\sqldata -e sa_password=<Str0ngPassw0rd> -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

Now when I’ve run my container I can see the bak file and restore the database with the following SQL.

USE [master]
RESTORE DATABASE [WideWorldImporters] 
FROM  DISK = N'C:\sqldata\WideWorldImporters-Full.bak' 
	WITH  FILE = 1,  
	MOVE N'WWI_Primary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\WideWorldImporters.mdf',  
	MOVE N'WWI_UserData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\WideWorldImporters_UserData.ndf',  
	MOVE N'WWI_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\WideWorldImporters.ldf',  
	MOVE N'WWI_InMemory_Data_1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\WideWorldImporters_InMemory_Data_1',  
	NOUNLOAD,  
	STATS = 5
GO

And there you go…