Question 83841 in Docker Question posted in
The official documentation can be found here.

Initialize SQL Server database in docker container without sqlcmd

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?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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

    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search