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?

Picrite

No sqlcmd? Have you tried docker run -it --rm mcr.microsoft.com/azure-sql-edge:latest ls -la /opt/mssql-tools/bin yet? – Dray

Returns ls: cannot access '/opt/mssql-tools/bin': No such file or directory. mssql-tools folder doesn't exist in the container. – Picrite

I apologize, you're completely correct - I wasn't testing on ARM64. The Docker hub page even says, sqlcmd tool is not available inside the ARM64 version of SQL Edge containers. The bcp tool isn't there either because the whole folder is missing. – Dray

In the absence of bcp and sqlcmd on ARM64 I can only suggest using nodejs, pwsh (PowerShell) or Python scripts to execute the changes against the container, either from the host or from another container within the same Docker network. Be aware that although SQL Containers start "instantly" the mssql service inside them can take several/tens of seconds to start. Your initialization script will need to retry-with-waits until it can get a successful connection as demonstrated at mssql-node-docker-demo-app – Dray

Yes, in the end I had to go for a node script. – Picrite

Can you post your solution? Would be really helpful for me. Thanks – Tease

@Tease sorry, I didn't see your comment. Here you can find the script, nothing fancy, just get the DB config and run a query to create the database. – Picrite

Answer 1

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

Picrite

Answer 2

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

Violinist

That won't work since sqlcmd is not available on M1 ARM64 – Tease

@Tease in my case it is working, just emulated to linux/amd64 platform. You just need to have Rosetta 2 installed. – Violinist

@Tease I can confirm that this works (at least with some adaptations). Docker will run the mssql container as x64, and the sqlcmd as ARM64. Note that in this approach, sqlcmd connects to the SQL Server over the (virtual) network. That's why it doesn't matter that they're different architectures. – Pegram

A variation of this solution worked for me on an M1 chip. – Kemberlykemble

I am starting a new project so I wanted to try this approach. I am getting standard_init_linux.go:228: exec user process caused: exec format error on the sqlcmd container and looking into it, it seems like that error is thrown when running on ARM64 chip, so this doesn't work for me at the moment. – Picrite

It might be that the script you are trying to execute is not supported by sh, and you must use bash instead. Try changing the command property to command: bash /opt/mssql_scripts/run-initialization.sh – Landau

Answer 3

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

Moline

Answer 4

• Fisrt. run mcr.microsoft.com/azure-sql-edge

version: '3'

services:
  azuresqledge:
    image: mcr.microsoft.com/azure-sql-edge
    container_name: azuresqledge
    environment:
      - ACCEPT_EULA=1
      - MSSQL_SA_PASSWORD=xxxxxxxx(PASSWORD)
      - MSSQL_PID=Premium
    cap_add:
      - SYS_PTRACE
    ports:
      - "1433:1433"
    networks:
      - sql_server_my


networks:
  sql_server_my:
    name: sql_server_my

• then, run mcr.microsoft.com/mssql-tools use same network

# -v . I need init sql file
docker run --network=sql_server_my -v ~/Downloads/xxx.sql:/data/xxx.sql  -it mcr.microsoft.com/mssql-tools

• you can use sqlcmd cmd with -S azuresqledge (docker CONTAINER name)

sqlcmd -S azuresqledge -U sa -P "<YourNewStrong@Passw0rd>"

Pyrrhuloxia