Initialize SQL Server database in docker container without sqlcmd

Question

I am working on a MacBook Pro with M1 CPU so I can't use the "normal" mssql docker image. I am using azure-sql-edge that doesn't have sqlcmd to initialize the database (create schema, database, login).

I have created a sql script that I would like to run once the container starts but I can't find any alternative to sqlcmd.

Is there any other way to do it?

Answer 1

I had same issue, I used mssql-tools docker image from Microsoft registry.

Sample docker-compose:

---
version: '3.8'

services:
  mssql:
    image: mcr.microsoft.com/azure-sql-edge:latest
    command: /opt/mssql/bin/sqlservr
    environment:
      ACCEPT_EULA: "Y"
      SA_PASSWORD: "SA_Passw0rd"
    stdin_open: true
    ports:
      - 1433:1433
    networks:
      - db_net
  sqlcmd:
    image: mcr.microsoft.com/mssql-tools:latest
    command: /opt/mssql_scripts/run-initialization.sh
    stdin_open: true
    volumes:
      - ./mssql_scripts:/opt/mssql_scripts
    networks:
      - db_net
networks:
  db_net:
    name: db_net

To use this docker-compose you need to have a shell script named run-initialization.sh with execute rights inside mssql_scripts folder.

The run-initialization.sh script waits for database to start up and then execute sql commands:

/opt/mssql-tools/bin/sqlcmd -S mssql -U SA -P SA_Passw0rd -d master -Q "SELECT version()"

or if you want to execute from test.sql file:

/opt/mssql-tools/bin/sqlcmd -S mssql -U SA -P SA_Passw0rd -d master -i /opt/mssql_scripts/test.sql

Answer 2

The solution above worked for me using Mac M1 chip, don't need to create a shell script can run the commands direct.

sqlcmd:
    image: mcr.microsoft.com/mssql-tools:latest
    stdin_open: true
    environment:
        - MSSQL_SA_PASSWORD=Xxx
        - MSSQL_DATABASE=test
        - MSSQL_BACKUP="/opt/mssql/test.bak"
    volumes:
        - ./test_data.bak:/opt/mssql/test.bak
    command: /bin/bash -c '/opt/mssql-tools/bin/sqlcmd -S mssql -U sa -P $$MSSQL_SA_PASSWORD -d tempdb -q "EXIT(RESTORE DATABASE $$MSSQL_DATABASE FROM DISK = $$MSSQL_BACKUP)"; wait;'

mssql:
    image: mcr.microsoft.com/azure-sql-edge:latest
    environment:
        - ACCEPT_EULA=Y
        - MSSQL_SA_PASSWORD=Xxxx
        - MSSQL_DATABASE=test
        - MSSQL_SLEEP=7
    ports:
        - 1433:1433

Answer 3

Since I am starting a new project I looked into this issue again and found a good solution for me.

I found go-sqlcmd, a new implementation of sqlcmd using golang and it's compatible with M1 chips.

So I am running azure-sql-edge as before using docker compose:

version: "3.9"

services:
  mssql:
    image: mcr.microsoft.com/azure-sql-edge:latest
    command: /opt/mssql/bin/sqlservr
    environment:
      ACCEPT_EULA: "Y"
      SA_PASSWORD: ${DATABASE_SA_PASSWORD}
    stdin_open: true
    ports:
      - 1433:1433

When the database container is up and in idle I run this bash script (in my case I am reading the environmnet variables from a .NET appsettings.json file):

cat <appsetting.json> | jq -r 'to_entries|map("\(.key)=\(.value|tostring)")|.[]' > temp

# Show env vars
grep -v '^#' temp

# Export env vars
export $(grep -v '^#' temp | xargs)

export SQLCMDPASSWORD=$DATABASE_SA_PASSWORD

sqlcmd -U sa \
    -v DATABASE_SCHEMA=$DATABASE_SCHEMA \
    -v DATABASE_DB_NAME=$DATABASE_DB_NAME \
    -v DATABASE_LOGIN_NAME=$DATABASE_LOGIN_NAME \
    -v DATABASE_LOGIN_PASSWORD=$DATABASE_LOGIN_PASSWORD \
    -i sql/init-db.sql,sql/init-user.sql

I had to split the database and schema creation in a script, then I create the user and assign it to the database.

The sql scripts, init-db.sql:

USE master

IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'$(DATABASE_SCHEMA)')
BEGIN
    EXEC sys.sp_executesql N'CREATE SCHEMA [$(DATABASE_SCHEMA)] AUTHORIZATION [dbo]'
END

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'$(DATABASE_DB_NAME)')
BEGIN
    CREATE DATABASE $(DATABASE_DB_NAME)
END

init-user.sql:

USE $(DATABASE_DB_NAME)

IF NOT EXISTS(SELECT principal_id FROM sys.server_principals WHERE name = '$(DATABASE_LOGIN_NAME)') BEGIN
    CREATE LOGIN $(DATABASE_LOGIN_NAME) 
    WITH PASSWORD = '$(DATABASE_LOGIN_PASSWORD)'
END

IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = '$(DATABASE_LOGIN_NAME)') BEGIN
    CREATE USER $(DATABASE_LOGIN_NAME) FOR LOGIN $(DATABASE_LOGIN_NAME)
END