Monthly Archives: April 2020

Set up a SQL Server in a docker container

4 Replies

You might wonder, why would I do need to create a docker image with SQL Server? If I were to set up the infrastructure for test or production environment, I would set up a SQL Server in Azure. To automate this process I would follow infrastructure as a code a pattern, creating terraform script and deployment pipeline for it. That is a different case.

Scenarios where that can be useful:

  • you spend 3 days setting up a database to work with your app and you would like to have a back-up of this state
  • you are a front-end Mac user, that doesn’t really like to configure something with a Microsoft label 😉
  • you want to share a particular MS SQL database state with all developers, with a different setup
  • you need that DB just for a quick task and pulling that docker image will take only a few minutes

What do you need

Please mind the fact that I’m a w Windows user.

  • a docker implementation – I’m using Docker Desktop
  • Azure Data Studio, or any other tool, to connect to MS SQL Server
  • Docker Hub account, or any other docker repository

Creating a docker container

First of all, let’s check if Docker is installed on your machine. Just type `docker –version` and you should see something similar to this.

Now let’s pull a SQL Server docker image from Microsoft, this one is Server 2017 developer edition:

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

Now let’s check what docker images I have on my machine.

I already had a SQL Server docker image, so I didn’t have to download it for the second time.

Now let’s create and run a docker container with the command:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=myPass123" -p 1433:1433 --name primeHotelDb -d mcr.microsoft.com/mssql/server:2017-latest

A few comments on what is happening here:

  • we are creating a docker container with the name primeHotelDb
  • we are using a mcr.microsoft.com/mssql/server:2017-latest docker image
  • we are passing ACCEPT_EULA and SA_PASSWORD parameters
  • this will set up a password myPass123 for the SQL Server for user sa
  • SQL Server will be available on localhost on port 1433, which is a first of the two passed in

Now, let’s check that we can connect to this SQL Server, I’m using an Azure Data Studio, which is a handy tool and I must admit – much quicker then SQL Server Management Studio.

The connection was successful, let’s now make some changes and save it, so that we would know later on, that the state of this container was persisted.

As you can see, I created an empty HotelDB database.

Preserving the container state

The easiest way to preserve the state of a container is to send a docker repository. I choose Docker Hub cause it comes with Docker Desktop installation and it’s free for community use. 

But first, we need to:

  • commit changes made in our container to a new docker image
  • we need to name our image michalbialecki/prime-hotel-db

This is because my Docker Hub account is michalbialecki and it will accept repositories only in that format. This can be done with a single command

docker commit primeHotelDb michalbialecki/prime-hotel-db

Now let’s check how my docker images look like.

Apart from Microsoft’s original image, there is my new image named michalbialecki/prime-hotel-db.

Let’s send this image to Docker Hub. To do that we need to log in.

docker login --username=michalbialecki

And after providing a valid password you should see something like this:

Next thing we need to do it to push the image to Docker Hub.

docker push michalbialecki/prime-hotel-db

And our docker image will be pushed to the Docker Hub repository.

After the process finish, I can go to the browser and check my Docker Hub account.

Yay! There is my image! In Docker Hub I can add collaborators, so they will be able to push images to my repository as well.

Now let’s check that it really works. Let’s remove my hotel container and repository from my machine.

docker stop primeHotelDb
docker rm primeHotelDb

Now if you list your container, there should be no primeHotelDb (docker ps -a).

The next command will remove the docker image.

docker rmi michalbialecki/prime-hotel-db

You can check, that this image was removed (docker images).

The next thing to do is to pull a docker image from Docker Hub.

docker pull michalbialecki/prime-hotel-db

Now let’s create a new image and run the container under the name primeHotelDbV2.

docker run -p 1433:1433 --name primeHotelDbV2 michalbialecki/prime-hotel-db

Then if I connect to the container with Azure Data Studio, I will see, that my HotelDb exists. It means, that changes done to my container were persisted!

Summary

In this article, we learned how to:

  • set up a docker image with SQL Server inside
  • connect to an instance of SQL Server in a container
  • save you local changes to a new docker image
  • push and share docker image with Docker Hub  

As you could see, docker is nothing to be afraid of. It is a great way to host apps regardless of the operating system. It is also a great way to work with SQL Server without the need to install and configure everything yourself.

Hope you found that useful, cheers!