olly - Fotolia


2 ways to attach SQL Server database files to Linux containers

SQL Server files can be stored outside of Docker containers in host directories or volumes. Here's how to set up SQL Server on Linux databases and attach them to containers.

Dealing with data in a Docker container is more difficult than it is in a virtual machine or physical server. Containers are inherently stateless, while database management systems like SQL Server need to persist data for stateful business applications. When you run SQL Server in a container, you typically store the databases externally in either the host machine's file system or a Docker-managed volume.

The SQL Server container images for Windows that Microsoft has made available on the Docker Hub can accept the attach_dbs environment variable as part of a Docker run command, which allows users to automatically attach SQL Server database files to a container when it's started. To make that happen, the Windows images run a PowerShell script called start.ps1, which database administrators (DBAs) can also use to attach databases to custom containers.

However, that script isn't included in Microsoft's container image for SQL Server on Linux. This means that, by default, Docker containers with the Linux-based version of the database software can't automatically attach external databases to the SQL Server instances running in them. Instead, databases can be attached manually or via a script after a container is running.

Let's look at two different ways to connect external data repositories to a Linux-based container and use them to store SQL Server database files that can then be attached to the container.

Mount a host directory in a container

The simplest way to access external data from a SQL Server on Linux container is to use a Docker run command to mount a file or directory on the host machine in the container -- a bind mount, in Docker parlance. You can see an example of how to do that below:

docker run -d -p 1433:1433 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=SQLpwd2017' -e 'MSSQL_PID=Express' -v /home/administrator/MSSQLDBs:/var/opt/mssql microsoft/mssql-server-linux

In this example, the -v -- or volume -- parameter is used to map a /home/administrator/MSSQLDBs directory on the Docker host to the /var/opt/mssql directory in the container. The latter is the default directory where the data and log files are stored for the SQL Server on Linux instance.

After the container starts, the Docker Engine duplicates the contents of the container-based directory into the external directory, as shown in Figure 1.

A SQL Server database directory on a Docker host system
Figure 1: The contents of the MSSQLDBs host directory after mounting it in a container

Mounting the host directory enables SQL Server databases to be stored in a persistent manner and updated as changes are made to the data they contain. If you drill into the mounted directory's data folder, you can see the data (.mdf) and log (.ldf) files from the SQL Server instance running in the container, as shown in Figure 2.

A SQL Server database directory's data folder on a Docker host system
Figure 2: The SQL Server database files stored in the MSSQLDBs directory's data folder

To add new databases, you can copy the data and log files to the data folder and then use T-SQL commands to attach the databases to SQL Server. Alternatively, you can copy a SQL Server database backup file to the mounted directory and do a restore to make the database available to the container.

For this example, I logged into the Docker host and created a backup subfolder in the MSSQLDBs directory. I then copied the .bak file from the Microsoft WideWorldImporters-Standard sample database into the new folder and used the ifconfig | grep inet command to retrieve the IP address of the container.

Next, I used Microsoft's SQL Operations Studio tool to restore the database to the SQL Server on Linux container, as shown in Figure 3.

A restore database process in SQL Operations Studio
Figure 3: Attaching a SQL Server database to a container by restoring a backup file

The newly attached database will be persisted in the Docker host directory even after the container is stopped and deleted. When a new container is started, the host directory can be mounted to it so the database, and any others in the directory, can continue to operate.

Use Docker volumes to store database files

The second way to attach SQL Server database files to a Linux-based instance running in a Docker container is to use a data volume. According to Docker's online documentation, volumes are the preferred method for persisting data outside of containers.

Bind mounts, like the one shown in the previous example, are dependent on the directory structure of the Docker host, which can limit their portability. In contrast, data volumes are managed by Docker itself in the host machine's file system.

Volumes can also be used by both Linux and Windows containers, and it's safer to share them between multiple containers. They're also easier to manage, back up and migrate than bind mounts are.

If you delete a SQL Server container with an attached volume, the volume and the data in it will continue to exist. The data in a volume is only deleted if you remove the volume itself -- a feature that makes containerizing SQL Server more feasible than it used to be.

You can use the following Docker command to create a volume, which I've named MSSQLDBvol in this case:

docker volume create MSSQLDBvol

The volume can then be mounted to a SQL Server on Linux container by using a Docker run command that's similar to the one listed at the start of the bind mount section below:

docker run -d -p 1433:1433 -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=SQLpwd20017' -v MSSQLDBvol:/var/opt/mssql microsoft/mssql-server-linux

The -v parameter supplies the name of the volume and the container directory that will be bound to it. In this example, the latter is again the /var/opt/mssql directory, the default location for data and log files in SQL Server on Linux.

Attach SQL Server database files from a volume

To attach a new database, you can copy SQL Server data and log files or a backup file in the container, then either attach the files to the SQL Server on Linux instance or restore the backup. Here's an example of how to use the Docker cp command to copy the data file of the AdventureWorks 2014 sample database into a SQL Server on Linux container that has the ID d6e:

docker cp /home/administrator/downloads/AdventureWorks2014_Data.mdf d6e:/var/opt/mssql/data

After the database has been copied to the /var/opt/mssql/data directory, its contents will also be copied to the external MSSQLDBvol volume and updated there as the data changes.

You can also use SQL Server Management Studio, SQL Operations Studio or SQLCMD scripts to attach SQL Server database files stored in a volume to a container. The results of attaching the AdventureWorks 2014 database to the SQL Server on Linux container with SQL Operations Studio are shown in Figure 4 below.

Example of attaching a database to a container with SQL Operations Studio
Figure 4: Attaching a database from a Docker volume to a container in SQL Operations Studio

To view the contents of the MSSQLDBvol volume to confirm what files are stored there, you can use an ls command like the following:

ls /var/lib/docker/volumes/MSSQLDBvol/_data/data

That will list the files that are contained in the volume, as shown in Figure 5.

SQL Server database files stored in a Docker volume
Figure 5: The contents of the MSSQLDBvol volume after adding the AdventureWorks 2014 database

The methods of attaching external database files to a SQL Server on Linux container do require some manual intervention. But there's no doubt that containers are a rapidly growing technology, and running SQL Server in a container can provide a huge boost for database deployment and management, particularly in test and development environments.

Knowing how to attach SQL Server database files to containers, on both Windows and Linux platforms, is a skill that DBAs are likely to be called on to demonstrate -- and utilize -- more and more in the years ahead.

Dig Deeper on Database management

Business Analytics
Content Management