Recently, with the help of a colleague at work, I’ve started to dabble a little with containers. I had a customer that requested some specific code to be tested, and I realized that I didn’t have my own local instance of SQL running (always good to have a local one). I decided to try to make this process easier instead of going the traditional route of creating a Virtual Machine and also to help me learn a new technology. In these series of posts, I’m going to document my process of creating a Mini Data Lab for SQL Server on my desktop using Docker. It is intended to be for beginners and in no way is an article for best practices or production deployments.
First step is to actually get docker installed and configured. I’m on a Windows machine, so it wasn’t too difficult following the instructions on these two links:
After a successful installation your docker application should look something like mine (except with no Containers/Apps or Images available).
So now we need to start getting our container images downloaded so we can use them. This is pretty easy, you can use the command prompt or powershell for this next step. First we want to pull the latest image of the version of SQL Server that we want; for this tutorial I will be using SQL Server 2019 running on Ubuntu Linux. The command is simply the following:
docker pull mcr.microsoft.com/mssql/server:2019-latest
You can find many more images here: https://hub.docker.com/_/registry
The next step would be to create a docker container using the newly downloaded container image. The command for that would look like this:
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YOURSTRONGPASSWORDHERE" -p 1401:1433 --name sqldocker1 -h sqldocker1 -d mcr.microsoft.com/mssql/server:2019-latest
For this command we are doing a few simple things to get the container up and running.
-e “ACCEPT_EULA=Y” accepts the user license agreement
-e “SA_PASSWORD=” sets the sys admin password for the instance
-p HOST:CONTAINER allows you to map the ports between the container and the host. Since SQL Server Runs on 1433 you will need to map the the Host’s port to the Container’s 1433 Port. Let me explain a bit more. For HOST you will pick any port (it can be 1433 but you can only use that port once). The Container port will always be 1433 as that is the port that SQL Server will listen on be default on the container. So you are basically mapping a “road” from the “external port” (Host) to the “internal port” (Container). If you look at the first image in this post you will see that I have mapped port 1433, 2433, 3433, 4433, and 5433 to five different containers (sqldocker 1 through 5). Each port mapping to the “internal port” of the container (1433) that SQL Server uses to listen for connections on. This works very differently then how Virtual Machines do and it took me a bit of time to wrap my head around; this can be changed but we will discuss it in a future post.
–name is the name of the container in your local repository
-h represents the host name or “internal name” used by the container
-d maps the docker image name to the container you want to create
You can run the command docker run –help for even more details on the flags and other options.
A few seconds later after running that command, you can log into your new SQL Server Instance. Yes. You read that correctly, a few seconds!! With one line of code? With one line of code. Go ahead and try it out you can use the IP address of your host machine or just use localhost and the port number you defined.
That is all you need to get your first SQL Server Container running on docker on your own personal computer. Simple and fast. The thing that takes the longest is downloading the container image from the container registry.
In the next few posts we will expand on using Docker Containers to build up our Mini Data Lab.
March 15, 2021 at 8:46 AM
I’ve been banging my head trying to get docker to set up a permanent IP with my container to let me connect to a SQL Server instance, your method looks a lot simpler and less painful. Thank you.
LikeLiked by 1 person
March 15, 2021 at 9:36 AM
I’ve banged my head on that too. You can still do it (I’m still writing that part up) but if you need something quick and dirty this is the way to go. Containers are complex, but I really just wanted to run SQL Server and test some stuff on it so this basic configuration worked for me.
No more head banging 🙂