Configure and customize SQL Server Docker containers

  • Article
  • 8 minutes to read

Applies to: yesSQL Server (all supported versions) - Linux

This article explains how y'all tin configure and customize SQL Server Docker containers, such as persisting your data, moving files from and to containers, and changing default settings.

Create a customized container

It is possible to create your ain Dockerfile to create a customized SQL Server container. For more information, come across a demo that combines SQL Server and a Node application. If you do create your own Dockerfile, exist enlightened of the foreground process, considering this process controls the life of the container. If information technology exits, the container will shut down. For example, if yous want to run a script and start SQL Server, brand sure that the SQL Server procedure is the correct-most control. 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 lot reversed the commands in the previous example, the container would close down when the do-my-sql-commands.sh script completes.

Persist your data

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 post-obit section explains how to apply information volumes to persist your database files even if the associated containers are deleted.

Important

For SQL Server, it is critical that you understand data persistence in Docker. In addition to the discussion in this section, come across Docker's documentation on how to manage data in Docker containers.

Mount a host directory as data book

The first pick is to mountain a directory on your host as a data volume in your container. To exercise that, use the docker run command with the -5 <host directory>:/var/opt/mssql flag. This allows the data to be restored between container executions.

Annotation

SQL Server 2019 containers automatically start upwardly as non-root, while SQL Server 2017 containers start as root past default. For more information on running SQL Server containers as non-root, see Configure security.

                  docker run -due east 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 -v <host directory>/data:/var/opt/mssql/data -v <host directory>/log:/var/opt/mssql/log -five <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=<YourStrong!Passw0rd>" -p 1433:1433 -v <host directory>/data:/var/opt/mssql/information -five <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=<YourStrong!Passw0rd>" -p 1433:1433 -v <host directory>/data:/var/opt/mssql/data -v <host directory>/log:/var/opt/mssql/log -5 <host directory>/secrets:/var/opt/mssql/secrets -d mcr.microsoft.com/mssql/server:2017-latest                                  
                  docker run -east 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 -v <host directory>/information:/var/opt/mssql/data -five <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 -due east "ACCEPT_EULA=Y" -due east "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -p 1433:1433 -v <host directory>/information:/var/opt/mssql/information -five <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 -east "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -p 1433:1433 -v <host directory>/data:/var/opt/mssql/data -five <host directory>/log:/var/opt/mssql/log -v <host directory>/secrets:/var/opt/mssql/secrets -d mcr.microsoft.com/mssql/server:2019-latest

This technique as well enables yous to share and view the files on the host outside of Docker.

Use data book containers

The second option is to employ a data volume container. You tin create a data volume container by specifying a book name instead of a host directory with the -v parameter. The following instance creates a shared information book named sqlvolume.

                  docker run -due east 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' -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=<YourStrong!Passw0rd>" -p 1433:1433 -v sqlvolume:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2017-latest                                  
                  docker run -east "ACCEPT_EULA=Y" -east "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -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=<YourStrong!Passw0rd>' -p 1433:1433 -v sqlvolume:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-latest                                  
                  docker run -e "ACCEPT_EULA=Y" -eastward "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -p 1433:1433 -v sqlvolume:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-latest                                  
                  docker run -e "ACCEPT_EULA=Y" -east "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -p 1433:1433 -v sqlvolume:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-latest

Note

This technique for implicitly creating a data volume in the run command does non 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 finish and remove this container, the data volume persists. Yous can view information technology with the docker book ls command.

              docker volume ls

If you then create another container with the aforementioned volume name, the new container uses the same SQL Server information contained in the volume.

To remove a data volume container, use the docker volume rm command.

Alert

If you delete the information book container, any SQL Server data in the container is permanently deleted.

Backup and restore

In improver to these container techniques, you can too use standard SQL Server fill-in and restore techniques. You can use backup files to protect your information or to motion the data to another SQL Server example. For more data, see Backup and restore SQL Server databases on Linux.

Alarm

If you practise create backups, make sure to create or re-create the backup files exterior of the container. Otherwise, if the container is removed, the fill-in files are as well deleted.

Enable VDI backup and restore in containers

Virtual Device Interface (VDI) backup and restore operations are now supported in SQL Server container deployments start with CU15 for SQL Server 2019 and CU28 for SQL Server 2017. Follow the steps beneath to enable VDI-based fill-in or restores for SQL Server containers:

  1. When deploying SQL Server containers, use the --shm-size option. To begin, set the sizing to i GB, as shown in the sample command beneath:
              docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Mystr0ngP@ssw0rd!" --shm-size 1g  -p 1433:1433 --proper 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 retentiveness directory (/dev/shm) inside the container, which is gear up to 64 MB past default. This default size of the shared memory is bereft to support VDI backups. We recommend that you configure this to a minimum of ane GB when you deploy SQL Server containers and want to support VDI backups.

  1. You lot must too enable the new parameter retentivity.enablecontainersharedmemory in mssql.conf inside the container. You can mountain mssql.conf at the deployment of the container using the -v option as described in the Persist your data section, or later on you take deployed the container by manually updating mssql.conf inside the container. Here's a sample mssql.conf file with the retention.enablecontainersharedmemory setting set to truthful.
              [retentiveness] enablecontainersharedmemory = true

Copy files from a container

To re-create a file out of the container, use the post-obit control:

              docker cp <Container ID>:<Container path> <host path>

You can get the Container ID by running the control 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

Copy files into a container

To copy a file into the container, utilise the following control:

              docker cp <Host path> <Container ID>:<Container path>

Instance: 

                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/information

Configure the time zone

To run SQL Server in a Linux container with a specific time zone, configure the TZ environment variable. To find the right time zone value, run the tzselect command from a Linux bash prompt:

              tzselect

Later on selecting the time zone, tzselect displays output like to the following:

              The following data has been given:          United states of america         Pacific  Therefore TZ='America/Los_Angeles' will be used.

You lot can apply this information to set the aforementioned 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 'A_PASSWORD=<YourStrong!Passw0rd>' \ -p 1433:1433 --proper noun sql1 \ -e 'TZ=America/Los_Angeles'\ -d mcr.microsoft.com/mssql/server:2017-latest                                  
                  sudo docker run -e 'ACCEPT_EULA=Y' -e "SA_PASSWORD=<YourStrong!Passw0rd>" ` -p 1433:1433 --name sql1 ` -due east "TZ=America/Los_Angeles" ` -d mcr.microsoft.com/mssql/server:2017-latest                                  
                  sudo docker run -east 'ACCEPT_EULA=Y' -due east "SA_PASSWORD=<YourStrong!Passw0rd>" ` -p 1433:1433 --proper name sql1 ^ -e "TZ=America/Los_Angeles" ^ -d mcr.microsoft.com/mssql/server:2017-latest                                  
                  sudo docker run -east 'ACCEPT_EULA=Y' -eastward 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' \    -p 1433:1433 --name sql1 \    -due east 'TZ=America/Los_Angeles'\    -d mcr.microsoft.com/mssql/server:2019-latest                                  
                  sudo docker run -east 'ACCEPT_EULA=Y' -east "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" `    -p 1433:1433 --proper noun sql1 `    -eastward "TZ=America/Los_Angeles" `    -d mcr.microsoft.com/mssql/server:2019-latest                                  
                  sudo docker run -e 'ACCEPT_EULA=Y' -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" `    -p 1433:1433 --proper noun sql1 `    -e "TZ=America/Los_Angeles" `    -d mcr.microsoft.com/mssql/server:2019-latest

Change the tempdb path

It'southward a skillful exercise to go along your tempdb database separate from your user databases.

  1. Connect to the SQL Server case, so run the following Transact-SQL (T-SQL) script. If there are more files associated with tempdb, you'll need to move them likewise.

                      Change DATABASE tempdb Alter FILE (    Proper noun = tempdev, FILENAME = '/var/opt/mssql/tempdb/tempdb.mdf' ); GO  Change DATABASE tempdb Alter FILE (     Proper noun = templog, FILENAME = '/var/opt/mssql/tempdb/templog.ldf' ); Get

  2. Verify that the tempdb file location has been modified, using the following T-SQL script:

                      SELECT * FROM sys.sysaltfiles WHERE dbid = two;

  3. You must restart the SQL Server container for these changes to take effect.

                        docker stop sql1 docker start sql1                                      
                        docker stop sql1 docker get-go sql1                                      
                        docker stop sql1 docker start sql1

  4. Open an interactive bash session to connect to the container.

                                              docker exec -it sql1 bash                                      
                                              docker exec -it sql1 fustigate                                      
                                              docker exec -it sql1 bash

    In one case continued to the interactive shell, run the following control to check the location of tempdb:

                      ls /var/opt/mssql/tempdb/

    If the move was successful, you'll come across like output:

                      tempdb.mdf templog.ldf

Change the default file location

Add together the MSSQL_DATA_DIR variable to change your data directory in your docker run control, and so mount a book to that location that your container's user has admission to.

                  docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=MyStrongPassword' -due east '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 "SA_PASSWORD=MyStrongPassword" -eastward "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 "SA_PASSWORD=MyStrongPassword" -e "MSSQL_DATA_DIR=/my/file/path" -5 /my/host/path:/my/file/path -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest                                  
                  docker run -e 'ACCEPT_EULA=Y' -east 'SA_PASSWORD=MyStrongPassword' -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 "SA_PASSWORD=MyStrongPassword" -e "MSSQL_DATA_DIR=/my/file/path" -5 /my/host/path:/my/file/path -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest                                  
                  docker run -eastward "ACCEPT_EULA=Y" -e "SA_PASSWORD=MyStrongPassword" -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

Examples of custom Docker containers

For examples of custom Docker containers, come across 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 case for RHEL 8 and SQL Server 2017
  • Dockerfile example for Ubuntu 20.04 and SQL Server 2019 with Total-Text Search, Polybase, and Tools

For information on how to build and run Docker containers using Dockerfiles, run into https://github.com/microsoft/mssql-docker/tree/master/linux/preview/examples/mssql-mlservices.

Next steps

  • Go started with SQL Server 2017 container images on Docker by going through the quickstart
  • Get started with SQL Server 2019 container images on Docker by going through the quickstart

  • Deploy and connect to SQL Server Docker containers

  • Troubleshooting SQL Server Docker containers

  • Secure SQL Server Docker containers