RSS

Mini Data Lab – SQL Server 2019 on Docker Part 1

01 Mar
Mini Data Lab – SQL Server 2019 on Docker Part 1

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:
https://docs.docker.com/docker-for-windows/install/
https://docs.docker.com/docker-for-windows/troubleshoot/#virtualization-must-be-enabled

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

You should now be able to see your newly downloaded SQL Server Image in your local repo.

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.

Make sure you run the entire piece of code as one single line.
You can see the container running in the Docker Desktop application.

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.

 

Tags: , , ,

2 responses to “Mini Data Lab – SQL Server 2019 on Docker Part 1

  1. Luther

    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.

    Liked by 1 person

     
    • Ayman El-Ghazali

      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 🙂

      Like

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: