Use SQL Server on Docker for Windows, the easy way

I wanted to install and test SQL Server 2019 on a new machine, without the hassle to create a VM from scratch, since I had none of my resources from this new place, that’s when I remembered about Docker and how Microsoft is slowing reaching feature parity for SQL on LInux , moreover, I didn’t need any of the unsupported stuff, so.

I didn’t find a straightforward guide that said “do this and that’s it” and everybody lost themselves in useless words for which I didn’t have the time to read, so, here I am writing it myself.

Prerequisites

Docker for Windows, duh, install it
If you haven’t, you may have to enable Hyper-V on your windows Machine (if you’re the kind of person to read this blog I expect you to have it already enabled)

SQL Server Download and Installation

It’s as easy as opening PowerShell and typing:

docker pull mcr.microsoft.com/mssql/server:2019-latest

Where 2019-GA-ubuntu-16.04 is the version of SQL Server that I want to install, in my case SQL Server 2019 RTM, that’s called a “Tag” , you can use other tags to install a different version of SQL Server, the full list of Tags is on the Microsoft SQL Server Docker Hub

The pull process will download and compose the image for you, ready for deploy, to actually create your docker instance, another command is required:

docker run `
--name sql2019 `
-p 1433:1433 `
-e "ACCEPT_EULA=Y" `
-e "SA_PASSWORD=This is a password,ok?" ` 
-v D:\SQL:/sql `
-d mcr.microsoft.com/mssql/server:2019-latest

Let’s analyze the meaning of each parameter:

• –name sql2019
  • The name of the Docker container (different than the name of the SQL instance), useful to quickly reference to the container in docker
• -p 1433:1433
  • The range of ports to expose, in this case the default SQL Server port
  • Map a TCP port on the host environment (first value) with a TCP port in the container (second value). In this example, SQL Server is listening on TCP 1433 in the container and this is exposed to the port, 1433, on the host.
• -e “ACCEPT_EULA=Y”
  • It’s mandatory to accept the EULA to execute any instance of SQL Server Anywhere
• -e “SA_PASSWORD=This is a password,ok?”
  • The SA password configured in the instance
  • Important , be sure to follow the Microsoft password policy (8 characters, UpperCase+Lowercase, etc..) when setting this one otherwise the container will shut itself down after a few seconds and you won’t understand why
• -v D:\SQL:/sql `
  • UPDATE: You may want to mount a volume for the internal mssql folder too, for easier upgrading, check the next article here.
  • This command is used to mount a windows folder inside the container, in this case the folder “D:\SQL” will be mounted to the path “\sql” in the container
  • In this way you can share files (backups, scripts, …) from the host machine to the container
  • If it’s the first time that you share a drive in Docker you’ll get a notification asking if you want to share the drive, of course if you want to proceed you’ll have to confirm
    Your antivirus could interfere with this step, if you have any issue please google “nameofyourantivirus docker volume share”
• -d mcr.microsoft.com/mssql/server:2019-latest
  • Finally, this specifies the image, of course I’m using the one I pulled before

That’s it! Done.

Useful Notes

Docker commands

Some useful docker commands:

docker start sql2019 #Starts your container
docker stop sql2019  #Stops your container
docker logs sql2019  #Shows the log for the container
docker ps            #shows the list of running containers, if you want to check if it's still running
docker ps -a         #shows the not running containers too

SQL Server Connection and Usage

To connect to the instance use localhost as host, SQL Authentication, sa as username and the password set above as the password, that’s it!

Finally, keep in mind that the shared path that I’ve set up is \sql , so, to restore a backup you would simply copy it in the shared windows folder (in this case D:\SQL) and tell SSMS/TSQL to read from there:

Fun Fact: In the SSMS GUI you have to use the backslash (\sql) in the path as in windows (see screenshot above), while the actual path in the container, which is Linux, us with forward slashes (/sql) :