How to have docker compose init a SQL Server database

Here is a docker-compose.yml sample with SQL server Microsoft image (without build) that does not rely on delay (I am not sure if it is a reliable way to wait for the SQL Server instance to start).

This approach is using additional setup container that executes your initialization SQL scripts as soon as main SQL Server container initializes itself.

I used the same mcr.microsoft.com/mssql/server:2019-latest image as it contains SQL Server tools in it, but feel free to use any image you like with SQL Server tools installed in it.

version: '3.8'


volumes:
  sqlserver_data:
  
services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=${Sa_Password:-#password123sdJwnwlk}
      - MSSQL_PID=Developer
    ports:
      - 1433:1433
    volumes:
      - sqlserver_data:/var/opt/mssql
    restart: always
    healthcheck:
      test: ["CMD-SHELL", "/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${Sa_Password:-password123} -Q 'SELECT 1' || exit 1"]
      interval: 10s
      retries: 10
      start_period: 10s
      timeout: 3s
  sqlserver.configurator:
    image: mcr.microsoft.com/mssql/server:2019-latest
    volumes:
      - ./init:/docker-entrypoint-initdb.d
    depends_on:
      sqlserver:
        condition: service_healthy
    command: >
      bash -c '
      /opt/mssql-tools/bin/sqlcmd -S sqlserver -U sa -P ${Sa_Password:-#password123sdJwnwlk} -d master -i docker-entrypoint-initdb.d/init.sql;
      echo "All done!";
      '

This compose file require you to add init.sql file to init subfolder. Here is an example to create new user:

USE [master];
GO

IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = 'newuser')
BEGIN
    CREATE LOGIN [newuser] WITH PASSWORD = '#password123sdJwnwlk', CHECK_POLICY = OFF;
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [newuser];
END
GO

`

Update 07.11.2024: To address comments from Wojtek vanDer and Igor Kanshyn The app did not start with the exact above script because the provided default password: password123 did not match the default SQL Server password policy.

I have updated the default Passwords in scripts so now it should work fine.

So things have changed a bit since these answers came out, here's my solution that uses the latest 2022 sql Server image. I also have a few other minor QOL features to this answer

First our docker-compose

  my_db_container:
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "Hey!Don't look here. Bad!"
    ports:
      - "1433:1433"
    networks:
      - dev_network
    volumes:
      - ./db-init/init.sql:/db/init.sql
      - ./db-init/entrypoint.sh:/entrypoint.sh
    entrypoint: ["/bin/bash", "/entrypoint.sh"]

First our docker-compose sets up the db image. The networks: - dev_network is to make it easier to reach my db container from my app container. The volumes' are how my container has access to the scripts it needs to create the db. In my docker-compose project I also need to have the two files specified in volumes (under db-init)

init.sql

in my case I just need the database created. All my migrations happen in the app code, but everything breaks if the db doesn't exist, so this is all I needed.

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'Jeff')
BEGIN
    CREATE DATABASE [Jeff];
END

entrypoint.sh.

This is the one that I had to fight with, and the one that doesn't work anymore with Linda or Koryakov's solutions (AI was similarly no help because it probably used their answers... I went down a solid rabbit hole). Microsoft change the location of their tools folder, so mssql-tools18 needs to be referenced. If it changes again - just inspect the file system on your docker image and see what they renamed or moved it to this time. I've also used a solution from elsewhere to reduce the lead time for the db creation as much as possible, rather than waiting X seconds, it waits in a loop till it can do a handshake.

#!/bin/bash

# Start SQL Server in the background
/opt/mssql/bin/sqlservr &

# Wait until SQL Server is ready
echo "Waiting for SQL Server to be available..."
until /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -C -Q "SELECT 1" > /dev/null 2>&1; do
  sleep 1
  echo "Still Waiting for SQL Server to be available..."
done

# Run your init script
echo "Running init.sql..."
/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -C -i /db/init.sql
echo "Finished running init.sql."
# Keep container alive
wait

Following to the answer, I faced few issues related to access and environment variables, corrected those issues and shared the working version of the below docker-compose.yml

volumes:
  sqlserver_data1:
  
services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=1StrongPwd!!
      - MSSQL_PID=Developer
    user: root
    ports:
      - 1433:1433
    volumes:
      - sqlserver_data1:/var/opt/mssql
    restart: always
    healthcheck:
      test: ["CMD-SHELL", "/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 1StrongPwd!! -C -Q 'SELECT 1' || exit 1"]
      interval: 10s
      retries: 10
      start_period: 10s
      timeout: 3s
  sqlserver.configurator:
    image: mcr.microsoft.com/mssql/server:2022-latest
    user: root
    volumes:
      - ./init:/docker-entrypoint-initdb.d
    depends_on:
      sqlserver:
        condition: service_healthy
    command: >
      bash -c '
      /opt/mssql-tools18/bin/sqlcmd -S sqlserver -U sa -P 1StrongPwd!! -C -d master -i docker-entrypoint-initdb.d/init.sql;
      echo "All done!";
      '

Notes:

1. If you are running on the machine co-exists with SQL-Server (SSMS) stop those services.
2. Add "-C" as parameter "sqlcmd" to avoid certificate related issue

Docker images for mysql and postgres know about docker-entrypoint-initdb.d directory, and how to process it.

For example, for mysql, here is Dockerfile

It runs script docker-entrypoint.sh:

COPY docker-entrypoint.sh /usr/local/bin/
RUN ln -s usr/local/bin/docker-entrypoint.sh /entrypoint.sh # backwards compat
ENTRYPOINT ["docker-entrypoint.sh"]

And docker-entrypoint.sh runs sql scripts from docker-entrypoint-initdb.d directory:

docker_process_init_files /docker-entrypoint-initdb.d/*

Looks like SQL Server docker image does not have processing for docker-entrypoint-initdb.d, you need to study SQL Server Dockerfile or documentation, probably there is some tools to init DB. If there not - you can create your own Docker image, basing on original:

Dockerfile:

FROM mcr.microsoft.com/mssql/server
# implement init db from docker-entrypoint-initdb.d