Setting Trace Flags and Configuring SQL Server Containers

In this blog post, we will walk through a few examples of how to configure SQL Server in Docker Containers. First, we will configure a container at runtime by overriding the default docker command for the container and setting Database Engine Service Startup Options. Second, we’re going to inject a configuration file into our container to configure SQL Server. Let’s go!

Starting a Container with a Trace Flag

First up, let’s configure a container at runtime using Database Engine Service Startup Options. In Docker you can override the command of a container at the command line so we can start up a container with the correct executable and parameters. I’m configuring a trace flag by setting the correct parameters for the sqlservr executable. So when this container starts up it will start the sqlservr executable with the -T 3226 parameter. Let’s check out the code for this…

docker run \
    --env 'ACCEPT_EULA=Y' \
    --env 'MSSQL_SA_PASSWORD=S0methingS@Str0ng!' \
    --name 'sqldemo1' \
    --volume sqldata1:/var/opt/mssql \
    --publish 31433:1433 \
    --detach mcr.microsoft.com/mssql/server:2019-CU11-ubuntu-18.04 /opt/mssql/bin/sqlservr -T 3226

After starting our container, let’s ask SQL Server about its currently configured trace flags…and in the output below, you can see the trace flag 3226 is configured.

sqlcmd -S localhost,31433 -U sa -Q 'DBCC TRACESTATUS;' -P 'S0methingS@Str0ng!'

TraceFlag Status Global Session
--------- ------ ------ -------
     3226      1      1       0

There is a challenge with this method. This configuration is now part of the container’s definition and is forever tied to the container’s lifecycle. If you want to remove it, you will need to delete the container and recreate it…but that’s ok if you use a Volume to persist your data independent of the container’s lifecycle. You are using Volumes, aren’t you?

Let’s clean up from this demo and move on to the following example. This code will delete your container and your volume.

docker rm -f sqldemo1
docker volume rm sqldata1

Creating and Running a Container with a mssql.conf Configuration File

Let’s do the same thing again, but this time let’s configure SQL Server running in a container with an mssql.conf configuration file. Using this method, you’re decoupling the configuration of the container from its definition. You can quickly change the configuration file and restart the container to load the new configuration using this method. Let’s get started…

First, we need an mssql.conf file, and here is a straightforward example. You can make this as customized as needed. Check out the Docs for more details on what to configure and how to configure it. In our example here, we’re setting a trace flag again.

[traceflag]
traceflag0 = 3226

Now, rather than use docker run to create and start the container. Let’s use docker create to create the container. This will create the container and the volume at the same time. We don’t want to start the container right now because we first want to copy the mssql.conf file in into the container volume and then start the container so SQL Server can use the mssql.conf file for its configuration.

So let’s create that container…

docker container create \
    --env 'ACCEPT_EULA=Y' \
    --env 'MSSQL_SA_PASSWORD=S0methingS@Str0ng!' \
    --name 'sqldemo1' \
    --volume sqldata1:/var/opt/mssql \
    --publish 31433:1433 \
    mcr.microsoft.com/mssql/server:2019-CU11-ubuntu-18.04

And now copy our msssql.conf file into the container at the location SQL Server expects the file to be located at /var/opt/mssql and start up the container with docker start

docker cp mssql.conf sqldemo1:/var/opt/mssql
docker start sqldemo1

With the container up and running, let’s check if our mssql.conf file inside the container is the one we want…and in the output below, you can see that’s the case.

docker exec sqldemo1 cat /var/opt/mssql/mssql.conf

[traceflag]
traceflag0 = 3226

And now let’s ask SQL Server what it thinks its configuration is…and there we have trace flag 3226 configured again.

sqlcmd -S localhost,31433 -U sa -Q 'DBCC TRACESTATUS;' -P 'S0methingS@Str0ng!'

TraceFlag Status Global Session
--------- ------ ------ -------
     3226      1      1       0

This method is excellent if you have a more complex configuration, perhaps TLS certificates, Active Directory configurations, and so on. Be sure to check out the Docs to see what you can configure. Again, if you need to make a change, you’ll edit the file, copy it into the container, and restart it. You can, of course, hop into the container with docker exec and edit the config, or edit it on the base file system.

Let’s clean up from this example.

docker rm -f sqldemo1
docker volume rm sqldata1

That’s a wrap for this post. There’s another way that you can inject configuration into your containers. You can build a custom container image with the settings set inside the container. I’m going to cover that in an upcoming post.