You can use docker compose to deploy and manage a Microsoft SQL Server 2022 (or older) database using the Microsoft Artefact Registry (their container registry) and any suitable docker installation.

I am by no means an expert at docker so consider this a leading learner article to get you started rather than something that could be considered best practice. This is my process to developing a database that can run as highly available infrastructure.

Running Microsoft SQL Server in Docker on Windows

If you are running the database on a windows machine, make sure to start WSL using the command line and move your project (or volumes) inside your WSL installation.

I’ve opted for Ubuntu however any Linux distribution is applicable. For best performance you probably would want to run the database on a host directly to avoid any abstraction bottlenecks.

Running the database inside the WSL installation ensures that the mounted volumes are native to the Linux installation and the WSL files aren’t on the windows file system and translated on the fly.

Docker Compose for Microsoft SQL Server 2022

Here is the minimal docker compose file I created for the database. It uses mounts for the database files and a dockerfile to set the permissions. For this installation I did some experimentation with High Availability and it will work with this setup however some additional configuration would be required beyond this compose file to deploy a highly available cluster for redundant DNS. I have also enabled the agent to use scheduling however you may need to pick a licence for your use case using MSSQL_PID.

I’ve also included watchtower to automatically upgrade the database however you may want to remove it if you do not require automatic updating or need a higher uptime.

name: msssql-docker
services:
    database-server:
        build:
            context: .
            dockerfile: dockerfile
        environment:
            - ACCEPT_EULA=Y
            - MSSQL_SA_PASSWORD={$MSSQL_SA_PASSWORD}
            - MSSQL_ENABLE_HADR=1
            - MSSQL_TCP_PORT=1433
            - MSSQL_AGENT_ENABLED=true
            - MSSQL_DATA_DIR=/var/opt/mssql/data
            - MSSQL_DUMP_DIR=/var/opt/mssql/dump
            - MSSQL_LOG_DIR=/var/opt/mssql/log_data
            - MSSQL_BACKUP_DIR=/var/opt/mssql/backup_data
            - MSSQL_ERROR_LOG_FILE=/var/opt/mssql/log/errorlog
        ports:
            - 1433:1433
        restart: unless-stopped
        image: mcr.microsoft.com/mssql/server:2022-latest
        volumes:
            - ./data:/var/opt/mssql/data
            - ./log_data:/var/opt/mssql/log_data
            - ./backup_data:/var/opt/mssql/backup_data
            - ./dump:/var/opt/mssql/dump
            - ./secrets:/var/opt/mssql/secrets
            - ./logs:/var/opt/mssql/log
    watchtower:
        image: containrrr/watchtower
        volumes:
            - /var/run/docker.sock:/var/run/docker.sock
volumes:
  data:
  log_data:
  backup_data:
  dump:
  secrets:
  logs:

And here is the dockerfile.

# Use latest windows 2022 image
FROM mcr.microsoft.com/mssql/server:2022-latest

# Switch to root for chown
USER root 

# set permissions on directories
RUN chown -R mssql:mssql /var/opt/mssql
 
# switching to the mssql user
USER mssql

And here is the .env file for the docker secret (the SA password). In future I will use active directory authentication but this should do for testing.

MSSQL_SA_PASSWORD=Badpassword1#

Directory Structure in the SQL Server container

I have laid out my directory structure as follows, if you deploy the docker container yourself it will make the directories (and the files) for you. You need only the .env , docker-compose.yml and dockerfile . Docker will handle the rest.

C:\Users\Aidan\Documents\mssql-docker>tree /f
...
│   .env
│   docker-compose.yml
│   dockerfile
│
├───backup_data
│       test.bak
|
├───data
│       Entropy.bin
│       master.mdf
│       mastlog.ldf
│       model.mdf
│       modellog.ldf
│       model_msdbdata.mdf
│       model_msdblog.ldf
│       model_replicatedmaster.ldf
│       model_replicatedmaster.mdf
│       msdbdata.mdf
│       msdblog.ldf
│       tempdb.mdf
│       tempdb2.ndf
│       tempdb3.ndf
│       tempdb4.ndf
│       templog.ldf
│       exampledatabase.mdf
│
├───dump
├───logs
│       agent_log.log
|       ...
|
├───log_data
│       exampledatabase_log.ldf
│
└───secrets
        machine-key

C:\Users\Aidan\Documents\mssql-docker>

Once your container database is up you will still need to make the database using SQL Server Management Studio or interact with the database using SQLCMD for your application.

Caveats using this method

• Using containers adds a layer of abstraction which may affect performance, this seems to be most notable on windows where the filesystem doesn’t run in WSL’s filesystem.
• The volumes are managed by docker so using faster disk may be difficult (or easier) depending on usecase.
• You have to pay for an MSSQL licence.
• You should put your backups on a different media than your data, which this currently doesn’t do.
• There is some additional setup with replication that doesn’t seem to be present on a standard windows installation of SQL Server 2022.

Thanks for reading, have fun.