Dockerizing an MSSQL Server: Unlocking Flexibility

Dockerizing an MSSQL Server: Unlocking Flexibility

Docker is a pretty magical tool that streamlines server and environment setup while helping to eliminate the operating system and software version variables. It’s one of the best ways to mitigate the classic ‘Works on my machine!’ obstacle many developers run into when sharing or promoting their code.

When a lot of people think of Docker, the last thing they think of is Microsoft or Windows. It took many years for Docker to even officially support Windows. As a .NET developer, I always wanted the stability that Docker offers, and today, I can have it. In the post below, I’ll dive into how to dockerize an MSSQL Server.

Why Dockerize?

Before we dive in, let’s talk briefly about the why. There are many advantages to running a Dockerized version of MSSQL Server, including…

• It’s cross-platform. Docker works on various platforms including Linux and Windows. If you want to do your work on Windows and deploy to Linux, you can.
• There is no manual installation required. You just open up a terminal, run a few commands, and your SQL Server is set up and ready to go.
• It’s scalable and can easily be deployed to various cloud platforms.

Set Up

Now that we’ve discussed why you may want to dockerize, let’s dive into the setup. Setting up Docker is a breeze! For the sake of this tutorial, we’re going to use the Docker Desktop application so we can have a nice GUI to get used to the process.

First head on over to the download page and download the installer for the operating system you have.

At this point, if you’re using a Linux environment, you’re good to go on to the next section. However, if you’re in Windows, there is another step required to get things running properly.

Windows Installation

In order to run Docker on Windows properly, you’ll need to either run it on a virtual machine running a Linux distribution or run it through WSL2 (Windows Subsystem for Linux). Neither of these are enabled or running by default, but both are pretty easy to set up.

For this tutorial, we’re going to go with the WSL2 approach since it doesn’t require a Windows Professional installation, and it’s simply the easiest of the two.

To set up WSL2, the instructions couldn’t be easier. Open up Powershell, and run this command:

wsl --install

Seriously. That’s it.

This will by default install Ubuntu to run under Windows that enable Linux-based applications to run without the need to dual-boot or run it in a virtual machine. You can check the same article mentioned above for instructions on how to install a different distribution if you’d prefer, but Ubuntu will serve our purpose just fine!

There’s one final step before getting started with Docker on Windows while using WSL2. Hit the settings cogwheel in the top right corner of the application, go to General settings, and make sure to check the ‘Use the WSL2 based engine’ checkbox. (My current installation is Windows 10 Home, so this is my only option.)

Linux Installation

There are many different distributions for Linux, and Docker has support for a lot of the more popular ones. There is a lovely installation document provided by Docker themselves that will answer most installation methods and questions, here.

Initializing the SQL Server

Now that Docker is installed, our next step is to spin up a Dockerized MSSQL server. This is pretty simple and can be accomplished with a few terminal/Powershell commands. For the sake of this tutorial, let’s go over how to do it in Windows using Powershell first.

Open up your Powershell and run this command:

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

You can replace the 2022-latest with different versions such as 2017 or 2019 if you’re trying to match another already existing database, but running this will essentially pull in the latest version of SQL server to install into the Docker container.

After that, you can run this command:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" `
  -p 1433:1433 --name sql1 --hostname sql1 `
  -d `
   mcr.microsoft.com/mssql/server:2022-latest

There are a few things to note on this.

• ACCEPT_EULA will automatically accept the End User License Agreement for using the particular Docker image.
• The name will be the name of your Docker container that will appear in the containers list on the Docker Desktop Client you installed earlier:
  
• hostname is how you will connect to your Docker container if you need to perform any sort of action on it from a terminal.
• The MSSQL_SA_PASSWORD will be your password, so be sure to remember this and make it secure! Replace <YourStrong@Passw0rd> with your new password, this includes deleting the angle brackets (<>). Your password must at least be 8 letters long.
• -p will determine your port number. If you need to set it to something specific, feel free to, but leaving it default is fine too!
• -d is pointing to the Docker image you pulled down a moment ago. Be sure to change it appropriately if you chose the 2019 or 2017 image previously.

If you are running a Linux distribution, you should be able to run these commands in place of the previously posted one:

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

sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" \
   -p 1433:1433 --name sql1 --hostname sql1 \
   -d \
   mcr.microsoft.com/mssql/server:2022-latest

After running these commands, your new SQL Server instance has been created! You’re welcome to use the master DB that exists by default, but you can also create another database.

Let’s use this as an excuse to log into our database through the terminal and run some commands…

Logging into SA to Create a New Database

Let’s log in to your Docker container to continue the process of dockerizing our MSSQL Server! Run this command:

Docker exec -it sql1 "bash"

This will open up a bash shell in your container. Remember, the inside of your container is a Linux system, so now you are playing by Linux rules. Next you’ll want to utilize sqlcmd to log into to the SA account that was created when you created the container:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourNewStrong@Passw0rd>"

Be sure to replace the "<YourNewStrong@Passw0rd>" with your previously chosen password.

Assuming everything is correct so far you’ll get this prompt:

1>

This allows you to run SQL queries. If you want to do everything here on out through your terminal, you are free to do so, but I’ll show you how to log in via SQL Server Management Studio in the next section.

Let’s create a TEST database now:

CREATE DATABASE TEST;
GO

Viola, your new Database Test now exists. As stated before, you can enter whatever query you want in this fashion!

Logging In Via SQL Server Management Studio

This step should hopefully be familiar to you if you’ve worked with SQL in the past. For this tutorial, I’ve stuck with the Microsoft theme and have installed SSMS (SQL Server Management Studio). It’s a free bit of software that is pretty feature-rich, but feel free to use whatever SQL management software you like most.

There are a few things to note while logging in to your dockerized container. The server name requires the most attention; you’ll notice above we’re connecting via TCP protocol, and we’re connecting to port 1433. This will be the port number you used when you created the Docker container earlier. Login with SA, and use the password you entered earlier for it.

That’s it! Use your SQL management software or the terminal to set up your database!

Conclusion:

I can’t stress enough how wonderful of a tool Docker is. With a few commands, we set up a database and were able to log into it. This process can be automated via a script as well that can be run on whatever server you want to deploy it to!

Docker is also good at setting up countless environments and software. I urge you to check out Docker Hub for other types of containers and preconfigured environments. You may be surprised at what you find!

Thanks for reading! Head to the Keyhole Dev Blog for more content.