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:

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:

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

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 :)

MSSQL Linux and Docker

SQL Saturday Guatemala Speakers

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