T-SQL Tuesday #140 : What have you been up to with containers?

T-SQL Tuesday #140 : What have you been up to with containers?For T-SQL Tuesday #140, Anthony Nocentino asks us what we have been up to with containers. As a longtime Mac user, I use them for a variety of things, most importantly to avoid a full-fledged virtualization platform imposing on the precious resources on my laptop.

Anthony highlighted in his invitation post that you can deploy a SQL Server "in just a few minutes!" I disagree. Once you have the repo downloaded:

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

Spinning up a container already running SQL Server takes a short handful of seconds:

docker run -i -p 1433:1433 -e ACCEPT_EULA=Y -e [email protected]! -d mcr.microsoft.com/mssql/server:2019-latest

This will output a long string that serves as the identifier for the container, like this:

dbfc891dcf9de1afb18b506ad3947f38335270198969aaa79a06fedea8b93a1c

In addition to immediately being able to connect to the SQL Server instance on port 1433, I can manage the container using shorthand. For example, if I want to stop and delete the container, I can just say:

docker stop dbfc
docker rm dbfc

(I can name each container explicitly, too, if I want the identifier to be more friendly.)

This makes running multiple tests of slightly different forms, over and over again, extremely easy! I blow away the instance and recreate it in seconds, so I don't have to worry about making all my scripts idempotent. If I want to see all of my running containers, I can use the following to output a list:

docker ps

I used this technique of "stop, drop, repeat" just the other day while testing some behavior around case-sensitive and binary collations at the instance level. In the old days, I would have had to install a second full-on instance of SQL Server in order to test a specific instance-level collation, then repeat for every collation in my set of tests. Yikes! With containers, this is much easier; I just have to add one additional argument to the docker run command:

docker run -i -p 1433:1433 -e ACCEPT_EULA=Y -e [email protected]! -e MSSQL_COLLATION=Hebrew_100_BIN2 -d mcr.microsoft.com/mssql/server:2019-latest

When my tests for that collation are done, I stop and delete the container, and move on to the next. If I need them to co-exist for some time, I can leave them up and running, since they require much less resources to keep running than a bunch of instances. The only change I have to make in that case is to specify a different port for each container (I blogged about another use case for different ports here).

This can also be useful when I am presenting… I can always start with a clean slate, and I don't need to worry about resource issues, connectivity to a remote server, or changing options or otherwise interfering with a shared instance of SQL Server somewhere in my work environment.

You may not be gung ho for containers in production yet, and you may not have much control over this in your environment anyway, but they offer great benefits in your own development environment – even if you are on Windows. If you aren't convinced yet, watch this episode of Data Exposed to get the rundown from Anthony and Anna Hoffman:

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am a father of two, an architect at Wayfair, and my pronouns are he/him.

2 Responses

  1. July 13th, 2021

    […] Aaron Bertrand explains two good uses for using SQL Server containers: […]

  2. July 17th, 2021

    […] Aaron Bertrand […]