In the first level of this series, we learned to start a container with a number of command line arguments. In the second level, we added persisted storage for our files. In this level, let's take this to the next level by putting most of our code into a docker-compose file that captures all everything in a structured format.

A Quick Start

To start this level, let's look at how I can start a container with the Docker compose system. Here's what I run:

In the image above, I had no containers running. Then I started a container using the docker-compose command and the "up" parameter, which means start the system. The d parameter means detached from this process and return control to this window. The second docker ps shows me container running.

To stop the container, I run the docker-compose command with down.

As an FYI, if I don't use the -d argument, I start to see the container logs.

To stop the container, I could use Docker Desktop, or hit CTRL+C. You can see this below.

Now let's look at how this works.

The Docker Compose YAML File

The key to this is that I use a file that contains all my options. The docker-compose executable takes the YAML configuration and sends it to the docker executable to setup and run the container. By default, we use a file called docker-compose.yml. We can use another name, which I'll show below.

For now, the structure of the file in modern versions of Docker is shown below:

services:
  mssql:
    container_name: sql2022-default
    hostname: sql2022
    image: mcr.microsoft.com/mssql/server:2022-CU14-ubuntu-22.04
    environment:
      ACCEPT_EULA: 'Y'
      MSSQL_SA_PASSWORD: 'Demo12#4'
      MSSQL_DATA_DIR: /var/opt/mssql/data
      MSSQL_BACKUP_DIR: /var/opt/mssql/backup
      MSSQL_PID: 'Developer' 
      MSSQL_TCP_PORT: 1433 
    ports: 
      - "1433:1433"
    volumes:
      - ./data:/var/opt/mssql/data
      - ./log:/var/opt/mssql/log
      - ./secrets:/var/opt/mssql/secrets
      - ./backup:/var/opt/mssql/backup

This is a YAML file, which means we work with indents to specify a hierarchy, and colons to separate keys from values.

Let's decode this file as we might use it for SQL Server and give you the various options that you may decide to change. I'll start with the root element, which is "services". Often we use a compose file to specify multiple containers to start together. In the Microsoft world, we might have a web server in one container and a database in another. For the purposes of this Stairway, we only have one service, the SQL Server container. However, we still specify the services element.

Service Name

The mssql is the service name, which is the only service we have. We could name this anything, and often in example files, I see this named "db". I've chosen mssql since I have compose files for postgresql and mysql I use for other systems. You can put anything here to help you identify this container.

Container Name

This is the name of the container by which we reference this in the Docker process list. Each container has an ID as well, set by the host, but to make things easier in commands like start, stop, etc., we can assign a name. I chose sql2022-default, which lets me know that this is a SQL Server 2022 container on the default port.

In other containers, I've used pgdev for a postgreSQL dev environment, or sql2022-41433 for a container running on port 41433.

Hostname

This is the name of the machine running inside the container. Think of this as the internal VM name assigned in Windows or Linux. When I run @@servername inside a connection to this SQL Server instance, I get this value, sql2022, back.

Image

This is the container image that I need on my machine, or I need to pull from the container registry. In this case, I'm not using the Docker registry, but the Microsoft one. I'm pulling a specific image rather than latest. There are problems (ref 1, ref 2) that can occur with latest, so I try to avoid using that tag. The last thing I need is to get caught in a debugging cycle if something breaks because I wasn't expected a version revision.

My image is the SQL 2022 CU 14 image running on Ubuntu. I will periodically update this when CU 15, 16, etc. come out. Usually after I'm sure the patch hasn't broken anything that I see a lot of you complaining about on Twitter/X, LinkedIn, etc.

Note: I am running WSL2, so I have a Linux subsystem on my Windows machine. SQL Server is not supported on Windows containers anymore.

Environment

Often we use environment variables to configure behavior inside of a container. Lots of software can read environment variables, and SQL Server is no exception. There are a number of variables that SQL Server on Linux can read. You can look at this MS Learn document to learn about them.

I've chosen to add a few that I often change in different containers, which helps me re-use this compose file elsewhere. Here are the ones I've set:

• ACCEPT_EULA - required, so keep this.
• MSSQL_SA_PASSWORD - required since auth is still a problem. Not great to keep this in a file, but this is dev/test. I do often change this once I have container I keep running, so this is less of an issue.
• MSSQL_DATA_DIR - I explicitly set this, since I'll map this in the section below.
• MSSQL_BACKUP_DIR - Same as data, map this to backup.
• MSSQL_PID - set this to dev explicitly to avoid any licensing faux pas
• MSSQL_PORT - this defaults to 1433, but since I might want to muck with this, I include it.

Ports

The ports section controls how I connect to the machine inside the container. The format for most docker things is host:container. In this case, I set the host port first as 1433, and then map this with a colon to 1433 inside the container. If I had changed the MSSQL_PORT variable above, I'd change the second number here to that port.

For other containers, such as my sql2022-41433, I set this as 41433:1433.

Note that a computer networking restriction is that any machine can only have one service listening on a port. This means that each SQL Server instance (or other service) has to have a unique port. If you have port conflicts, the container won't start as it can't bind to the port. If you have a default instance of SQL Server running on your machine, you would need to configure a different port.

Volumes

We want to persist data, right? When the container stops, I still want my data. If I have to remove the container from my system for some reason, I might not want the data to go away. So I map volumes from my host to the volumes inside the container. Essentially creating a file share from a folder on my machine to a folder inside the container. This keeps my data, but also makes it easy for me to drop a file, like a backup file, in a folder on my machine, as opposed to copying it through a network to a place inside the machine.

I have a c:\data folder, under which I create a folder for each container I run. In this case, I've set a SQL2022 folder. As you can see below, I have a few folders for different containers I run. Think of these as the virtual hard drive locations for a VM if I were doing this in Hyper-V or VMWware.

In each SQL Server folder, I have folders for data, logs, backup, and secrets. These are mapper in the compose file to the appropriate folder inside the container. When the container starts, it just uses these folders. If I drop a file into c:\data\sql2022\backup, the SQL Server instance sees this as a file in the default backup location for the instance.

Connecting to the Instance

Once I run the docker-compose command I showed at the top, this is a running container on my system that is listening on port 1433. I can connect to it like this in SSMS:

Notice I used sjones, because I've connected to this container previously, added the sjones login, and that data is in master.mdf, which is stored and persisted in c:\data\SQL2022\data. Even if this container gets destroyed and removed, I can run a new container with this config, and my data is there.

I connect and running a few commands, you can see the hostname and versions listed.

I can work with this like any other SQL Server on Linux instance.

If I were connecting to a container running on another port, I could use .,<port> as in .,41433 for my other instance.

Summary

This level of the Stairway to Database Containers has shown how easy it is to use a configuration file to pre-set a lot of information that quickly becomes cumbersome at the command line. We described various options that can be set in the YAML configuration file, and showed how to start a container using this file.

There are other options you can set, and I encourage you to read the documentation and experiment a bit with using containers, as they make it easy to start up a new piece of software, try something, and get rid of it if necessary, without installing or (attempting to) uninstall software. It's amazing.