Training
Configure and customize SQL Server Linux containers
Applies to: SQL Server - Linux
This article explains how you can configure and customize SQL Server Linux containers using Docker. You can persist your data, move files from and to containers, and change default settings.
Tip
You can use sqlcmd (Go) to create a new instance of SQL Server in a container for development purposes. For more information, see Create and query a SQL Server container.
You can create your own Dockerfile to build a customized SQL Server container. For more information, see a demo that combines SQL Server and a Node application. If you do create your own Dockerfile, be aware of the foreground process, because this process controls the life of the container. If it exits, the container shuts down. For example, if you want to run a script and start SQL Server, make sure that the SQL Server process is the right-most command. All other commands are run in the background. The following command illustrates this inside a Dockerfile:
/usr/src/app/do-my-sql-commands.sh & /opt/mssql/bin/sqlservr
If you reversed the commands in the previous example, the container would shut down when the do-my-sql-commands.sh script completes.
Your SQL Server configuration changes and database files are persisted in the container even if you restart the container with docker stop
and docker start
. However, if you remove the container with docker rm
, everything in the container is deleted, including SQL Server and your databases. The following section explains how to use data volumes to persist your database files even if the associated containers are deleted.
Important
For SQL Server, it's critical that you understand data persistence in Docker. In addition to the discussion in this section, see Docker's documentation on how to manage data in Docker containers.
The first option is to mount a directory on your host as a data volume in your container. To do that, use the docker run
command with the -v <host directory>:/var/opt/mssql
flag, where <host directory>
is any given path. For instance: C:\SQL
on Windows, or ~/sqlvolumes
on Linux. This allows the data to be restored between container executions.
Note
Containers for SQL Server 2019 (15.x) and later versions automatically start up as non-root, while SQL Server 2017 (14.x) containers start as root by default. For more information on running SQL Server containers as non-root, see Secure SQL Server Linux containers.
Important
The SA_PASSWORD
environment variable is deprecated. Use MSSQL_SA_PASSWORD
instead.
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-p 1433:1433 \
-v <host directory>/data:/var/opt/mssql/data \
-v <host directory>/log:/var/opt/mssql/log \
-v <host directory>/secrets:/var/opt/mssql/secrets \
-d mcr.microsoft.com/mssql/server:2017-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" `
-p 1433:1433 `
-v <host directory>/data:/var/opt/mssql/data `
-v <host directory>/log:/var/opt/mssql/log `
-v <host directory>/secrets:/var/opt/mssql/secrets `
-d mcr.microsoft.com/mssql/server:2017-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" ^
-p 1433:1433 ^
-v <host directory>/data:/var/opt/mssql/data ^
-v <host directory>/log:/var/opt/mssql/log ^
-v <host directory>/secrets:/var/opt/mssql/secrets ^
-d mcr.microsoft.com/mssql/server:2017-latest
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-p 1433:1433 \
-v <host directory>/data:/var/opt/mssql/data \
-v <host directory>/log:/var/opt/mssql/log \
-v <host directory>/secrets:/var/opt/mssql/secrets \
-d mcr.microsoft.com/mssql/server:2019-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" `
-p 1433:1433 `
-v <host directory>/data:/var/opt/mssql/data `
-v <host directory>/log:/var/opt/mssql/log `
-v <host directory>/secrets:/var/opt/mssql/secrets `
-d mcr.microsoft.com/mssql/server:2019-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" ^
-p 1433:1433 ^
-v <host directory>/data:/var/opt/mssql/data ^
-v <host directory>/log:/var/opt/mssql/log ^
-v <host directory>/secrets:/var/opt/mssql/secrets ^
-d mcr.microsoft.com/mssql/server:2019-latest
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-p 1433:1433 \
-v <host directory>/data:/var/opt/mssql/data \
-v <host directory>/log:/var/opt/mssql/log \
-v <host directory>/secrets:/var/opt/mssql/secrets \
-d mcr.microsoft.com/mssql/server:2022-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" `
-p 1433:1433 `
-v <host directory>/data:/var/opt/mssql/data `
-v <host directory>/log:/var/opt/mssql/log `
-v <host directory>/secrets:/var/opt/mssql/secrets `
-d mcr.microsoft.com/mssql/server:2022-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" ^
-p 1433:1433 ^
-v <host directory>/data:/var/opt/mssql/data ^
-v <host directory>/log:/var/opt/mssql/log ^
-v <host directory>/secrets:/var/opt/mssql/secrets ^
-d mcr.microsoft.com/mssql/server:2022-latest
Caution
Your password should follow the SQL Server default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.
This technique also enables you to share and view the files on the host outside of Docker.
The second option is to use a data volume container. You can create a data volume container by specifying a volume name instead of a host directory with the -v
parameter. The following example creates a shared data volume named sqlvolume
.
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-p 1433:1433 \
-v sqlvolume:/var/opt/mssql \
-d mcr.microsoft.com/mssql/server:2017-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" `
-p 1433:1433 `
-v sqlvolume:/var/opt/mssql `
-d mcr.microsoft.com/mssql/server:2017-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" ^
-p 1433:1433 ^
-v sqlvolume:/var/opt/mssql ^
-d mcr.microsoft.com/mssql/server:2017-latest
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-p 1433:1433 \
-v sqlvolume:/var/opt/mssql \
-d mcr.microsoft.com/mssql/server:2019-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" `
-p 1433:1433 `
-v sqlvolume:/var/opt/mssql `
-d mcr.microsoft.com/mssql/server:2019-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" ^
-p 1433:1433 ^
-v sqlvolume:/var/opt/mssql ^
-d mcr.microsoft.com/mssql/server:2019-latest
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-p 1433:1433 \
-v sqlvolume:/var/opt/mssql \
-d mcr.microsoft.com/mssql/server:2022-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" `
-p 1433:1433 `
-v sqlvolume:/var/opt/mssql `
-d mcr.microsoft.com/mssql/server:2022-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" ^
-p 1433:1433 ^
-v sqlvolume:/var/opt/mssql ^
-d mcr.microsoft.com/mssql/server:2022-latest
Caution
Your password should follow the SQL Server default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.
This technique for implicitly creating a data volume in the run command doesn't work with older versions of Docker. In that case, use the explicit steps outlined in the Docker documentation, Creating and mounting a data volume container.
Even if you stop and remove this container, the data volume persists. You can view it with the docker volume ls
command.
docker volume ls
If you then create another container with the same volume name, the new container uses the same SQL Server data contained in the volume.
To remove a data volume container, use the docker volume rm
command.
Warning
If you delete the data volume container, any SQL Server data in the container is permanently deleted.
In addition to these container techniques, you can also use standard SQL Server backup and restore techniques. You can use backup files to protect your data or to move the data to another SQL Server instance. For more information, see Back up and restore SQL Server databases on Linux.
Warning
If you do create backups, make sure to create or copy the backup files outside of the container. Otherwise, if the container is removed, the backup files are also deleted.
Virtual Device Interface (VDI) backup and restore operations are now supported in SQL Server container deployments beginning with CU15 for SQL Server 2019 (15.x) and CU28 for SQL Server 2017 (14.x). Follow these steps to enable VDI-based backup or restores for SQL Server containers:
When deploying SQL Server containers, use the
--shm-size
option. To begin, set the sizing to 1 GB, as shown in the following command:docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Mystr0ngP@ssw0rd!" \ --shm-size 1g \ -p 1433:1433 \ --name sql19 \ --hostname sql19 \ -d mcr.microsoft.com/mssql/server:2019-latest
The option
--shm-size
allows you to configure the size of the shared memory directory (/dev/shm
) inside the container, which is set to 64 MB by default. This default size of the shared memory is insufficient to support VDI backups. We recommend that you configure this to a minimum of 1 GB when you deploy SQL Server containers and want to support VDI backups.You must also enable the new parameter
memory.enablecontainersharedmemory
inmssql.conf
inside the container. You can mountmssql.conf
at the deployment of the container using the-v
option as described in the Persist your data section, or after you deploy the container by manually updatingmssql.conf
inside the container. Here's a samplemssql.conf
file with thememory.enablecontainersharedmemory
setting set totrue
.[memory] enablecontainersharedmemory = true
To copy a file out of the container, use the following command:
docker cp <Container ID>:<Container path> <host path>
You can get the Container ID by running the command docker ps -a
.
Example:
docker cp d6b75213ef80:/var/opt/mssql/log/errorlog /tmp/errorlog
docker cp d6b75213ef80:/var/opt/mssql/log/errorlog C:\Temp\errorlog
docker cp d6b75213ef80:/var/opt/mssql/log/errorlog C:\Temp\errorlog
To copy a file into the container, use the following command:
docker cp <Host path> <Container ID>:<Container path>
Example:
docker cp /tmp/mydb.mdf d6b75213ef80:/var/opt/mssql/data
docker cp C:\Temp\mydb.mdf d6b75213ef80:/var/opt/mssql/data
docker cp C:\Temp\mydb.mdf d6b75213ef80:/var/opt/mssql/data
To run SQL Server in a Linux container with a specific time zone, configure the TZ
environment variable (see Configure the time zone for SQL Server 2022 on Linux for more information). To find the right time zone value, run the tzselect
command from a Linux bash prompt:
tzselect
After you select the time zone, tzselect
displays output similar to the following example:
The following information has been given:
United States
Pacific
Therefore TZ='America/Los_Angeles' will be used.
You can use this information to set the same environment variable in your Linux container. The following example shows how to run SQL Server in a container in the Americas/Los_Angeles
time zone:
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-p 1433:1433 --name sql1 \
-e 'TZ=America/Los_Angeles' \
-d mcr.microsoft.com/mssql/server:2017-latest
sudo docker run -e 'ACCEPT_EULA=Y' -e "MSSQL_SA_PASSWORD=<password>" `
-p 1433:1433 --name sql1 `
-e "TZ=America/Los_Angeles" `
-d mcr.microsoft.com/mssql/server:2017-latest
sudo docker run -e 'ACCEPT_EULA=Y' -e "MSSQL_SA_PASSWORD=<password>" ^
-p 1433:1433 --name sql1 ^
-e "TZ=America/Los_Angeles" ^
-d mcr.microsoft.com/mssql/server:2017-latest
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-p 1433:1433 --name sql1 \
-e 'TZ=America/Los_Angeles' \
-d mcr.microsoft.com/mssql/server:2019-latest
sudo docker run -e 'ACCEPT_EULA=Y' -e "MSSQL_SA_PASSWORD=<password>" `
-p 1433:1433 --name sql1 `
-e "TZ=America/Los_Angeles" `
-d mcr.microsoft.com/mssql/server:2019-latest
sudo docker run -e 'ACCEPT_EULA=Y' -e "MSSQL_SA_PASSWORD=<password>" `
-p 1433:1433 --name sql1 `
-e "TZ=America/Los_Angeles" `
-d mcr.microsoft.com/mssql/server:2019-latest
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-p 1433:1433 --name sql1 \
-e 'TZ=America/Los_Angeles' \
-d mcr.microsoft.com/mssql/server:2022-latest
sudo docker run -e 'ACCEPT_EULA=Y' -e "MSSQL_SA_PASSWORD=<password>" `
-p 1433:1433 --name sql1 `
-e "TZ=America/Los_Angeles" `
-d mcr.microsoft.com/mssql/server:2022-latest
sudo docker run -e 'ACCEPT_EULA=Y' -e "MSSQL_SA_PASSWORD=<password>" ^
-p 1433:1433 --name sql1 ^
-e "TZ=America/Los_Angeles" ^
-d mcr.microsoft.com/mssql/server:2022-latest
Caution
Your password should follow the SQL Server default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.
It's a good practice to keep your tempdb
database separate from your user databases.
Connect to the SQL Server instance, and then run the following Transact-SQL (T-SQL) script. If there are more files associated with
tempdb
, you need to move them as well.ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = '/var/opt/mssql/tempdb/tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = '/var/opt/mssql/tempdb/templog.ldf'); GO
Verify that the
tempdb
file location has been modified, using the following T-SQL script:SELECT * FROM sys.sysaltfiles WHERE dbid = 2;
You must restart the SQL Server container for these changes to take effect.
docker stop sql1 docker start sql1
docker stop sql1 docker start sql1
docker stop sql1 docker start sql1
Open an interactive
bash
session to connect to the container.docker exec -it sql1 bash
docker exec -it sql1 bash
docker exec -it sql1 bash
Once connected to the interactive shell, run the following command to check the location of
tempdb
:ls /var/opt/mssql/tempdb/
If the move was successful, you see similar output:
tempdb.mdf templog.ldf
Add the MSSQL_DATA_DIR
variable to change your data directory in your docker run
command, then mount a volume to that location that your container's user has access to.
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-e 'MSSQL_DATA_DIR=/my/file/path' \
-v /my/host/path:/my/file/path \
-p 1433:1433 \
-d mcr.microsoft.com/mssql/server:2017-latest
docker run -e 'ACCEPT_EULA=Y' -e "MSSQL_SA_PASSWORD=<password>" `
-e "MSSQL_DATA_DIR=/my/file/path" `
-v /my/host/path:/my/file/path `
-p 1433:1433 `
-d mcr.microsoft.com/mssql/server:2017-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" ^
-e "MSSQL_DATA_DIR=/my/file/path" ^
-v /my/host/path:/my/file/path ^
-p 1433:1433 ^
-d mcr.microsoft.com/mssql/server:2017-latest
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-e 'MSSQL_DATA_DIR=/my/file/path' \
-v /my/host/path:/my/file/path \
-p 1433:1433 \
-d mcr.microsoft.com/mssql/server:2019-latest
docker run -e 'ACCEPT_EULA=Y' -e "MSSQL_SA_PASSWORD=<password>" `
-e "MSSQL_DATA_DIR=/my/file/path" `
-v /my/host/path:/my/file/path `
-p 1433:1433 `
-d mcr.microsoft.com/mssql/server:2019-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" ^
-e "MSSQL_DATA_DIR=/my/file/path" ^
-v /my/host/path:/my/file/path ^
-p 1433:1433 ^
-d mcr.microsoft.com/mssql/server:2019-latest
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
-e 'MSSQL_DATA_DIR=/my/file/path' \
-v /my/host/path:/my/file/path \
-p 1433:1433 \
-d mcr.microsoft.com/mssql/server:2022-latest
docker run -e 'ACCEPT_EULA=Y' -e "MSSQL_SA_PASSWORD=<password>" `
-e "MSSQL_DATA_DIR=/my/file/path" `
-v /my/host/path:/my/file/path `
-p 1433:1433 `
-d mcr.microsoft.com/mssql/server:2022-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" ^
-e "MSSQL_DATA_DIR=/my/file/path" ^
-v /my/host/path:/my/file/path ^
-p 1433:1433 ^
-d mcr.microsoft.com/mssql/server:2022-latest
You can use the mssql-conf tool to set parameters in SQL Server containers.
For example, you can set a memory limit for the instance using the following steps:
Connect directly to the container using
docker exec
as the root user. Replacesqlcontainer
with your container name.docker exec -u root -it sqlcontainer "bash"
Use mssql-conf to change a setting. This example changes the
memory.memorylimitmb
setting to 2 GB (2,048 MB)./opt/mssql/bin/mssql-conf set memory.memorylimitmb 2048
For examples of custom Docker containers, see https://github.com/microsoft/mssql-docker/tree/master/linux/preview/examples. The examples include:
- Dockerfile example with Full-Text Search
- Dockerfile example for RHEL 7 and SQL Server 2019
- Dockerfile example for RHEL 8 and SQL Server 2017
- Dockerfile example for Ubuntu 20.04 and SQL Server 2019 with Full-Text Search, PolyBase, and Tools
For information on how to build and run Docker containers using Dockerfiles, see the ML Services samples on GitHub.
- Get started with SQL Server 2017 (14.x) container images on Docker by going through the quickstart
- Get started with SQL Server 2019 (15.x) container images on Docker by going through the quickstart
- Get started with SQL Server 2022 (16.x) container images on Docker by going through the quickstart
Did you know that you can edit SQL content yourself? If you do so, not only do you help improve our documentation, but you also get credited as a contributor to the page.
For more information, see How to contribute to SQL Server documentation