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:

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

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

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:

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

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:

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:

• Learn How to Deploy SQL Server 2019 in Containers in Just Minutes