MSSQL Linux with Docker

On February 18th I had the opportunity to give a talk about SQL Server running inside a Docker container in the SQL Saturday Guatemala event, more details here. I thought no one was going to attend due to at the same time Jason Horner (Microsoft Certified Master of SQL Server) was giving a talk about database performance tricks, but to my surprise some people were interested in the talk (yes, mostly developers, one DBA and one auditor), nice!.

So, I want to share the agenda with some useful reference links and I'm going to extend in the most important slide of the PPT, here it is:

Agenda

The agenda was pretty straight forward and short:

1. Docker
2. MSSQL + Linux
3. Use Cases
4. Demo

1. Docker

I started by giving a quick tour about what Docker is and why it's, the architecture behind and some adoption details in the recent years. Here's the list of reference links:

• What is Docker? https://www.docker.com/what-docker
• Docker Overview https://docs.docker.com/engine/understanding-docker/
• Docker Adoption https://www.datadoghq.com/docker-adoption/

2. MSSQL Linux + Docker

Because there was another talk about MSSQL running on Linux I didn't focus on that and went straight to the instructions and considerations when running the container:

• Run the SQL Server Docker image on Linux, Mac, or Windows https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-docker
• MSSQL for Linux Containers https://hub.docker.com/r/microsoft/mssql-server-linux/
• MSSQL for Windows Containers https://hub.docker.com/r/microsoft/mssql-server-windows/
• MSSQL Express for Windows Containers https://hub.docker.com/r/microsoft/mssql-server-windows-express/

Now, you need to consider the following when running the container (otherwise the container won't run):

• Pull the image from the docker registry (docker pull)
• Your computer needs to have at least 3.25 Gb of RAM (to be honest, I didn't like this but I still need to test if it runs with less memory assigned)
• You need to accept the license agreement with the env var ACCEPT_EULA
• You need to provide a strong password with the env var SA_PASSWORD
• Beware that you're running the full version of MSSQL so you have a trial of 150 days
• You can have persist data by attaching a volume, this is because by default the data is ephemeral and you will lose it once the container is stopped. You need to attach a volume to the "/var/opt/mssql" path

Here's the docker run command:

docker run -e 'ACCEPT_EULA=Y' e 'SA_PASSWORD=YourStrong!Passw0rd' -p 1433:1433 -d microsoft/mssql-server-linux:ctp1 linux:ctp1-2

Here's the docker run command with a volume attached:

docker run -e 'ACCEPT_EULA=Y' e 'SA_PASSWORD=YourStrong!Passw0rd' -p 1433:1433 -v <host directory>:/var/opt/mssql -d microsoft/mssql-server-linux:ctp1 linux:ctp1-2

If you're running on Windows, you can replace with for example C:/mssql

3. Use Cases

I gave some use cases that came to my mind, things that I will use to run MSSQL in a container, so if you have any other use case please share it in the comments, I'll glad to hear from the community:

1. Prototyping, things that you need to validate fast and don't want to have a full server for the DB
2. Local environment for developers, a developer can start working in the code from day 1 without having to worry about the DB version for example
3. Test/Practice environment for DBAs, there are times that we would like to test some scripts that might bring down the server or cause inconsistency
4. Migration script testing, we might want to practice the migration from one version to a newer version
5. Practice backups restore, we can configure a periodic practice to restore backups, we need to make sure that when we need them, they work
6. Separate processing layer from storage layer, you might want to have all data in the cloud or a SAN and not in your computer, this also gives us the ability to copy data from another environment or partner (although we have the risk of corrupted data)
7. Microservice Arquitecture, this is a good fit when you want to have a DB per service (it can also help you to migrate to a more lightweight storage like a no-sql db)

4. Demo

I followed the "Getting Started" page for developers, but I didn't have enough time to do everything, so here are the activities we can do to practice:

1. Configure environment
2. Create an application in C# connecting to a MSSQL
3. Change the app using Columnstore for a 100x faster app
4. mssql extension in Visual Studio Code

Reference link: https://www.microsoft.com/en-us/sql-server/developer-get-started/csharp-ubuntu

SQL Saturday Guatemala

Here are some photos of that day :)

Summary

As you saw, it's really easy to get started with MSSQL for Linux using a Docker container. I present to you some use cases that came to my mind, it might exist more and others would like to discuss some of them and that's why I want to make clear that my idea is not to define a rule, I just wanted to give some examples of how I would use this combination. I'm not sure yet if I would recommend use MSSQL in a Docker container for production environments, I see it more as a fast way to experiment.

Oh yes, here's the PPT of the talk for that day (it's in Spanish): Download here