Monday, April 1, 2019

Running SQL Server Insider a Docker Container

Most of the time when we are working with an application which connects to a Microsoft SQL Server database, we usually have Microsoft SQL Server installed in the local machine and have our test database/s there and doing our testing against that.

With SQL Server containers you can avoid the installation and you can set up a SQL Server in a matter of minutes.

In this post, let’s see how we can setup SQL Server 2019 inside a Docker container. As of today, the latest SQL Server 2019 image is CTP 2.4.

First, make sure docker daemon is running in your machine. I am on Windows and it’s making sure Docker Desktop is running.

Now pull up the image from MCR (Microsoft Container Registry). If you are not aware that Microsoft has started maintaining its own container registry (like Docker Hub) and has started serving container images from there. You can find more information on that from this blog post: Microsoft syndicates container catalog).

I am using PowerShell.
docker pull
Once the command is completed, you can run the following command to make sure the image is present.
docker images
docker images
Next, before starting a container from this image, you need to accept end user license and setup a sa password. You can do all this and start up a container by below command.
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=1qaz!QAZ@' -p 1433:1433 --name sql2019 -d
After that if you check on running containers, you should see our sql2019 container is running.
docker ps -a
Now you have a container which has SQL Server is running. Next thing you might want is to try the connectivity.

In this post I am using Microsoft SQL Server Management Studio (SSMS). I just need to get the local IP address of my machine and use it as the server name, use SQL Server Authentication and use sa credentials.
SSMS - Connected
It should get connected without any issues. Now if you want to restore a database, you can use either SSMS or sqlcmd. For this post, I will use SSMS.

When I try to select a .bak file, of course, it’s empty.
Locate Backup File
I can easily copy the file I want using docker cp command.
docker cp C:\Users\Jaliya\Desktop\WideWorldImporters-Full.bak <containerId>:/var/opt/mssql/data
Once the command completed, if I refresh the Locate Backup File dialog, I can see the copied file there. And from there I can select the file and do the restoration.

A couple of important things,
  • You can stop the container (in this case: docker stop sql2019) and start (docker start sql2019) it later without losing the changes in SQL Server Configuration and your databases.
  • But if you remove the container by doing a docker rm, everything in the container is deleted, including your databases.
That’s pretty neat, isn’t it. Hope this helps.

Happy Coding.


No comments:

Post a Comment