Sreekanth Bandarla
Server Manager - Dashboard.

SQL Server with a Docker container on Windows Server 2016

April 15, 2019 by

In this article let’s see how to install a SQL Server Instance using a Docker container in Windows server 2016.

“Containers” is probably one of the hottest buzz words talked about these days in IT world, especially Docker containers. So, what exactly is Docker? Well, Docker is one of the few companies who develops, builds and distributes container technologies. It’s a pioneer in container technologies, similar to VMWare in virtualization technologies. I won’t be going into internals and architecture of how docker container works under the hoods in this article, instead lets just focus on installing docker containers inside windows server 2016 and then setup the latest version of SQL Server Instance on top of it.

One can install dockers in windows 10 (should be enterprise or professional with anniversary update or later and Hyper-V enabled.) or in Windows server 2016, In our case we will be using Windows Server 2016 for this purpose.

Installing Docker services on windows server 2016

In order to install Docker suite, first we have to enable containers feature in windows server 2016 and install Microsoft management package provider for docker. Okay, let’s get into action. To begin with, I created a Windows server 2016 Virtual machine and enabled direct internet access to the VM.

Open server manager and navigate to “Add roles and features” and select “Containers” feature as shown below.

Server Manager - Dashboard

Selecting installation type in Add roles and features wizard

server selection in add roles and features wizard

select containers feature in add roles and features wizard

Select “Containers” feature and click “Next”.

Confirm installation selections in Add roles and features wizard

After a minute or so, the server got rebooted as per the option selected above. Now let’s download and install Microsoft package management provider for docker. For that. Open PowerShell in elevated mode (Run as an administrator) and issue below command.

Install docker microsoft provider

You will be prompted to download and install NuGet provider as well as part of this installation, Type “Y” or just hit enter to proceed further.

Install and import Nuget provider

Now it’s time to install the Docker package. Issue the below provided PowerShell command to install the latest version of Docker. (This will install both Docker engine and the Docker client on your machine)

  • Note: If you have the latest updates installed on your windows server 2016, you should not run into any issues.

In my case, I didn’t have the prerequisite patches installed on my server, so my installation ran into issues as shown in the below screenshot.

failed installing docker because of missing required updates for windows server 2016

Well, let’s apply the latest and greatest updates available for my windows server 2016 invoking “sconfig”.

Installing updates on windows server from server configuration

After few seconds, you should get below screen, from which you can select “Option 6” to download and install latest updates.

select "Download and install updates"  from server configuration

At this point, it prompted me to select either to install “All updates” or “Only recommended updates”. I opted for installing all updates as you can see in the below screenshots.

Select to install all the updates

Windows downloading all the available updates

Restart the machine after installing updates

As you can see, after few minutes I got a popup requesting for system reboot. Select “Yes” and once the server is back online, I tried issuing the same PowerShell command which failed earlier to Install Docker services on my machine.

Okay, Let’s retry again…

Installing docker containers provider

Awesome, this time I had no issues. At this point, you should be able to see Docker being installed as a service under “Services.msc” in stopped state. You can start the service from services console or issue below PowerShell statement to start the service.

Start docker engine service

Great, now that we have Docker installed on our server, it is time to verify few basic things. Issue “Docker Info” command to get information related to docker installation we just finished.

verifying docker container information

Issue “Docker version” to get version details as shown below.

Verify the docker version

If you are curious about all the available commands we can use with Docker, just type “docker” and hit enter. It will provide us with a long list of all available commands as shown below.

listing all the commands available in docker.

Installing/running SQL Server on Windows containers


Now that we have Docker service up and running on our server, we should be able to create SQL containers. First thing first, in order to create a SQL container, we need to have an image. Think of an image as our SQL Server installation media. Similar to how we need media to create a SQL Instance on a machine, we need an image to run a SQL container. These images can be stored and pulled (Analogy – download in our traditional setup) from your own private registry if you have one setup or from Docker hub. Docker hub is a registry/repository provided by Docker company where anyone can upload and store their images. For this article, I will be using an image published by Microsoft in docker hub to create my SQL containers.

To begin with, Let’s search all the Microsoft SQL images available in docker hub by issuing below command.

search SQL Server images available in docker hub by micrrosoft.

Just for fun, let’s see if I have any images already available in my local repository. I can issue the command “docker images” for that purpose. As you can see, I have none at this point.

verify available images in local registry.

Now, let’s pull (Again…think of it like downloading the media) “mssql-server-windows-developer” to my local registry from docker hub by issuing the below command

pulling an image from docker hub

Depending on your download speeds, it might take a while. Once it’s done pulling the image from hub, I am issuing the “docker images” command again to check if things appear any different.

Verify docker images in registry

As you can see now, I have an image downloaded to my local repository which is ~15 GB in size. You can also notice that this image has been published/created by Microsoft in docker hub 14 months ago.

Now comes the fun part, we can issue below command to create and run the SQL container. Think of running a container is similar to installing a SQL Instance.

  • -e: Set the ACCEPT_EULA variable to “Y” to accept end user license agreement and use SA_PASSWORD to Specify your own strong password that meets SQL Server complex password requirements
  • -p: 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
  • –name: Specify a custom unique name for the container rather than a randomly generated one. If you run more than one container, you cannot reuse this same name
  • d: Run the container in detached mode. Since SQL runs as a service, it is recommended to use this option. If not, container will take over your cmd/shell when it starts

create docker container using docker-pull.

Great, Let’s verify the status of the newly created Docker windows container to make sure it’s running. For that you can issue either “Docker ps” or “Docker container ls” commands.

Tip: Docker ps” command will only list containers which are running currently, if you want to list all the containers, issue “Docker ps -a” command.

verify the status of docker container from powershell.

Now, Let’s create few more Windows based SQL containers based on the same image. As you can see from the below screenshot, I was able to setup/install three SQL Server Instances using Docker windows container technology in matter of few seconds as opposed to several minutes or even hours per our traditional methods for setting up SQL Instances.

Create multiple containers  based on the same SQL image.

Now let’s get the IP addresses of these Docker windows SQL containers using the below shown command, I need this information to connect to my SQL container from the host machine or remotely using SSMS or any other client tools.

Inspect IP address of the containers.

For whatever reason, if you see any issues with starting containers, you can review the logs by issuing the command “Docker container logs –name of the container”. FYI, below are the logs of my first container mssqltrek-con1.

Review Docker container logs.

Now that we have confirmed containers are up and running, let’s connect from SSMS.

Connect to SQL Instances created in containers from remote SSMS.

  • Note: By default, Docker containers for windows will install a default Instance of SQL Server. You can use localhost (if you are connecting from the same host) or the IP address or the container name along with the port numbers in order to connect to respective SQL container.

Cleaning up Docker container environment:

Now, let’s cleanup our environment by removing all the Docker SQL containers which we did created so far. To remove/delete a container, issue the command “docker rm containername”.

  • Note: Containers should be in stopped state in order to remove/delete them gracefully.

Removing the containers gracefully.

As you can see, I ran into errors when I attempt to remove my Docker windows container when it is in running state.

I stopped my Docker windows containers using command “docker stop containername” and then I was able to remove them without any issues as you can see below.

Stop and remove SQL containers in windows.

Conclusion:

In this article, we have seen how to install containers on Windows Server 2016 from scratch and Install the latest stable version of SQL Server leveraging Docker containers technology. Container technology is here to stay and It’s not the future, it’s present. Several companies are already leveraging Docker containers technology in their shops or at least evaluating it. Getting familiar with deploying and provisioning containers could almost become a mandatory skill in near future especially for DBAs and DevOps. Hope this article will be useful in setting up Docker windows containers in a lab environment to get familiar with and explore the technology.

Sreekanth Bandarla

Sreekanth Bandarla

Sreekanth Bandarla is a Database Administrator having about 9 years of experience supporting SQL Servers in Banking, Trading, Industrial, Automotive and Health care domains. He is aMicrosoft certified professional holding active MCITP and MCSA certifications, currently exploring cloud technologies in database stack. He keeps his own blog and in his leisure time he loves playing open world games in Play station.

View all posts by Sreekanth Bandarla
Sreekanth Bandarla
Docker

About Sreekanth Bandarla

Sreekanth Bandarla is a Database Administrator having about 9 years of experience supporting SQL Servers in Banking, Trading, Industrial, Automotive and Health care domains. He is a Microsoft certified professional holding active MCITP and MCSA certifications, currently exploring cloud technologies in database stack. He keeps his own blog and in his leisure time he loves playing open world games in Play station.View all posts by Sreekanth Bandarla

21,930 Views