Docker environment variables

We can configure SQL Server in a Docker container by using environment variables.

We've already seen this when running SQL in a container in the previous wikis as there are two environment variables that must be set each time we run SQL Server in a container: -

MSSQL_SA_PASSWORD or SA_PASSWORD
ACCEPT_EULA

MSSQL_SA_PASSWORD sets the SA password so that we can use the SA account to connect to SQL Server within the container. ACCEPT_EULA accepts the end user licence agreement which must be set to Y otherwise SQL won't run (it's a legal thing, apparently)

However there are a whole bunch of other environment variables that we can use to configure SQL Server in a container: -

MSSQL_PID - Sets the SQL Server edition or product key
MSSQL_LCID - Sets the language ID to use for SQL Server. For example 1036 is French.
MSSQL_COLLATION - Sets the default collation for SQL Server. This overrides the default mapping of language id (LCID) to collation.
MSSQL_MEMORY_LIMIT_MB - Sets the maximum amount of memory (in MB) that SQL Server can use. By default it is 80% of the total physical memory.
MSSQL_TCP_PORT - Configure the TCP port that SQL Server listens on (default 1433).
MSSQL_IP_ADDRESS - Set the IP address. Currently, the IP address must be IPv4 style (0.0.0.0).
MSSQL_BACKUP_DIR- Set the Default backup directory location.
MSSQL_DATA_DIR - Change the directory where the new SQL Server database data files (.mdf) are created.
MSSQL_LOG_DIR - Change the directory where the new SQL Server database log (.ldf) files are created.
MSSQL_DUMP_DIR - Change the directory where SQL Server will deposit the memory dumps and other troubleshooting files by default.
MSSQL_ENABLE_HADR - Enable Availability Group. For example, '1' is enabled, and '0' is disabled
MSSQL_AGENT_ENABLED - Enable SQL Server Agent. For example, 'true' is enabled and 'false' is disabled. By default, agent is disabled.
MSSQL_MASTER_DATA_FILE - Sets the location of the master database data file. Must be named master.mdf until first run of SQL Server.
MSSQL_MASTER_LOG_FILE - Sets the location of the master database log file. Must be named mastlog.ldf until first run of SQL Server.
MSSQL_ERROR_LOG_FILE - Sets the location of the errorlog files.

N.B. - This information is taken from the official Microsoft documentation here

What's really cool is that we can specify these environment variables in a dockerfile so that we can built a custom image with custom values already set so that we don't have to set them again when running a container.

For more information, see Building a Custom Image

Let's run a container with a bunch of these environment variables specified: -

docker container run -d `
--publish 15789:15789 `
--env MSSQL_SA_PASSWORD=Testing1122 `
--env ACCEPT_EULA=Y `
--env MSSQL_PID="Developer" `
--env MSSQL_TCP_PORT=15789 `
--env MSSQL_BACKUP_DIR=/var/opt/mssql/data `
--env MSSQL_DATA_DIR=/var/opt/mssql/data `
--env MSSQL_LOG_DIR=/var/opt/mssql/data `
--env MSSQL_AGENT_ENABLED="True" `
--name sqlcontainer1 `
mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

If we want to see what has been specified in a running container, we can run: -

docker exec sqlcontainer1 printenv

And there's all the environment variables we specified in our docker container run statement! Including the SA password! So be careful who has access to your Docker host, as they can get sensitive information like that.

We can also use environment variable files instead of typing out all the --env flags. Firstly, create a file with the variable in it (I called mine C:\temp\sql.env): -

MSSQL_PID=Developer
ACCEPT_EULA=Y
MSSQL_AGENT_ENABLED=True
MSSQL_DATA_DIR=/var/opt/sqlserver/sqldata
MSSQL_LOG_DIR=/var/opt/sqlserver/sqllog
MSSQL_BACKUP_DIR=/var/opt/sqlserver/sqlbackups

And now we can reference that file with the --env-file flag: -

docker container run -d `
--publish 15789:1433 `
--env MSSQL_SA_PASSWORD=Testing1122 `
--env-file C:\temp\var.env `
--name sqlcontainer1 `
mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

Much easier!