Run MSSQL 2017 Server in Docker for Windows

Goal: Run MSSQL Server 2017 in Docker on Windows with persistent database storage mapped to the host filesystem.

Note: MSSQL 2019 not available in official images at time of writing.

Docker Compose Setup

// docker-compose.yaml
version: '3.8'
services:
  SQLServer:
   image: microsoft/mssql-server-windows-developer:2017
   environment:
     - ACCEPT_EULA=Y
     - SA_PASSWORD=Password_01
     # - attach_dbs=[{'dbName':'Joker','dbFiles':['C:\\SQLData\\Joker1.mdf','C:\\SQLData\\Joker1.ldf']},{'dbName':'Joker2','dbFiles':['C:\\SQLData\\Joker2.mdf','C:\\SQLData\\Joker2.ldf']}]
   ports:
     - '1433:1433'
   volumes:
     - ./data:c:\SqlData

Important: On first run, omit the attach_dbs argument. Use it only when restarting with existing database files. Databases can also be attached via T-SQL after server startup.

Note: Mapping to C:\Program Files fails due to permission issues.

Create Database with Mapped Storage

CREATE DATABASE Joker
ON
( NAME = Joker,
    FILENAME = 'C:\SqlData\joker.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'C:\SqlData\joker.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )

Test Persistence

Create a table and insert data:

USE Joker 

CREATE TABLE FunnyJokes (
  Id INT   NOT NULL    IDENTITY    PRIMARY KEY,
  Title   VARCHAR(100)  NOT NULL,

);

INSERT INTO FunnyJokes (Title) 
VALUES ('The one with the tiger');

Verify persistence:

  1. Stop the container
  2. Uncomment the attach_dbs line in docker-compose.yaml
  3. Start the container again
  4. Confirm the database and data are intact

Database files should be visible in the host’s ./data folder.