How to have docker compose init a SQL Server database

Question

I have a docker-compose file that creates a starts SQL Server. This is working fine. I can connect to the database and see the master database.

What I am trying to do is create a new database, and add a table and some data to that table. I have been unable to find an example of doing this using SQL Server. All the examples I have seen are either using PostgreSQL or Mysql.

I have tried to adapt this example Docker Compose MySQL Multiple Database

I have created an init directory with a file called 01.sql and the only thing in it is

CREATE DATABASE `test`;

My docker-compose.yml looks like this

services:
    db:
        image: "mcr.microsoft.com/mssql/server"
        ports:
            - 1433:1433
        volumes:             
            - ./init:/docker-entrypoint-initdb.d
        environment:
            SA_PASSWORD: "password123!"
            ACCEPT_EULA: "Y"

When I run docker-compose up

I'm not seeing anything in the logs that implies it's even trying to load this file. When I check the database I do not see any new database either.

I am at a loss to understand why this isn't working for SQL Server but the tutorial implies that it works for MySql. Is there a different command for SQL Server?

Answer 1

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.