14

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?

Improve this question
7
  • No sqlcmd? Have you tried docker run -it --rm mcr.microsoft.com/azure-sql-edge:latest ls -la /opt/mssql-tools/bin yet? Commented Apr 7, 2022 at 7:02
  • 1
    Returns ls: cannot access '/opt/mssql-tools/bin': No such file or directory. mssql-tools folder doesn't exist in the container. Commented Apr 7, 2022 at 7:07
  • 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. Commented Apr 7, 2022 at 7:10
  • 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 Commented Apr 7, 2022 at 7:17
  • 1
    @Wlada 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. Commented Sep 14, 2022 at 11:09

4 Answers 4

Reset to default
7

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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

That won't work since sqlcmd is not available on M1 ARM64
@Wlada in my case it is working, just emulated to linux/amd64 platform. You just need to have Rosetta 2 installed.
@Wlada 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.
A variation of this solution worked for me on an M1 chip.
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.
|
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
Improve this answer

Comments

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
Improve this answer

Comments

0
  • 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>"
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.