DB-유

Running SQL Server 2017 in a Docker Container

· mssql, docker, linux

SQL Server 2017 is the first release that runs on Linux, and Microsoft publishes it as an official Docker image. For a developer machine or a throwaway test instance this is a big improvement over the Windows installer: a working instance in under a minute, no Windows licence, and you can throw it away with one command. I've been running it since the release candidates to test migration scripts, and now that the GA image is out I finally wrote my steps down.

I wouldn't run this in production as is. Persistence, resource limits and patching need more thought than a single docker run. For development and CI it's excellent.

My setup is Ubuntu 16.04 with Docker CE 17.06 or newer (Docker for Windows/Mac works too). Give the engine at least 3.25 GB of RAM, because SQL Server 2017 refuses to start with less. On Docker for Mac, raise the VM memory in Preferences, Advanced. Leave about 1.5 GB of disk for the image.

Pull the image

docker pull microsoft/mssql-server-linux:2017-latest

The 2017-latest tag tracks the latest cumulative update for 2017. If you want a fixed version, 2017-GA is there too. I pin a tag for anything shared with a team. Confirm it downloaded:

docker images microsoft/mssql-server-linux

Start a container

docker run -d --name sql2017 \
  -e 'ACCEPT_EULA=Y' \
  -e 'SA_PASSWORD=Dev!Passw0rd2017' \
  -e 'MSSQL_PID=Developer' \
  -p 1433:1433 \
  microsoft/mssql-server-linux:2017-latest

ACCEPT_EULA=Y is mandatory. SA_PASSWORD must satisfy the complexity policy: at least 8 characters with uppercase, lowercase, digits and symbols, otherwise the container exits within a few seconds. MSSQL_PID selects the edition; Developer is the default and free for non-production use, and Express, Standard and Enterprise are accepted if you hold the licence.

Watch the startup log until you see "SQL Server is now ready for client connections":

docker logs -f sql2017

Keep the data (do this from the start)

Without a volume, docker rm destroys the databases. I learned that by deleting a container full of test data I needed the next morning. Mount /var/opt/mssql, which contains the data, log and secrets directories:

docker volume create sql2017data
docker run -d --name sql2017 \
  -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Dev!Passw0rd2017' \
  -p 1433:1433 \
  -v sql2017data:/var/opt/mssql \
  microsoft/mssql-server-linux:2017-latest

A named volume works on all platforms. A bind mount to a host directory (-v /srv/sql2017:/var/opt/mssql) also works on Linux but not reliably on Docker for Mac, where the shared filesystem does not support the file operations SQL Server needs.

Connecting

The image includes the command-line tools under /opt/mssql-tools/bin, so you can connect from inside the container without installing anything on the host:

docker exec -it sql2017 /opt/mssql-tools/bin/sqlcmd \
  -S localhost -U SA -P 'Dev!Passw0rd2017' \
  -Q 'SELECT @@VERSION;'

From the host, any client that speaks TDS works: SSMS 17.x on Windows, sqlcmd from the mssql-tools package on Linux, or the mssql extension in Visual Studio Code. The server name is localhost,1433.

Restoring a backup into it

Copy a .bak file in and restore it. The paths inside the container are Linux paths:

docker exec sql2017 mkdir -p /var/opt/mssql/backup
docker cp ./ShopDB.bak sql2017:/var/opt/mssql/backup/

RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/ShopDB.bak';

RESTORE DATABASE ShopDB
FROM DISK = '/var/opt/mssql/backup/ShopDB.bak'
WITH MOVE 'ShopDB' TO '/var/opt/mssql/data/ShopDB.mdf',
     MOVE 'ShopDB_log' TO '/var/opt/mssql/data/ShopDB_log.ldf';

Backups from SQL Server 2008 through 2016 on Windows restore directly, which makes this a convenient way to test an upgrade.

Quick sanity check

SELECT SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductVersion'), host_platform
FROM sys.dm_os_host_info;

host_platform should say Linux. Stop and start the container and confirm the restored database is still there:

docker stop sql2017; docker start sql2017

Things that bit me

Container exits immediately. Run docker logs sql2017. The common messages are "ERROR: The SA password does not meet SQL Server password policy requirements" and "sqlservr: This program requires a machine with at least 3250 megabytes of memory." Fix the password or give Docker more RAM.

"Login failed for user 'SA'" from the host but not from inside the container. You changed SA_PASSWORD on a container that already had a persisted master database in the volume. The environment variable only applies on first initialisation; change the password with ALTER LOGIN SA WITH PASSWORD = '...' instead.

Port 1433 already in use. A local Windows SQL Server instance or an earlier container is holding it. Map another port with -p 1434:1433 and connect to localhost,1434.

Backups written inside the container vanish. They were written to a path outside the mounted volume. Write them under /var/opt/mssql/backup or docker cp them out.

If you've ever waited 40 minutes for a Windows SQL Server setup to finish, this feels like cheating.