Katarzyna Kmiotek

Are using a Mac and want to set up an SQL Server and access it via SQL Server Management Studio?
Well, it's not possible. Both tools are developed by Microsoft and unfortunately are not supported on Apple Silicon machines.
There are alternatives!

You can run your SQL server from the docker container and you can use Azure Data Studio to connect to it. In this post, I will show you how to get it setup and how to run it and restore database backup (.bak file) with just a single docker-compose up

Prerequisites:

• Docker
• Azure Data Studio
• .bak file to be in the root of the project (I am using example database backup provided by Microsoft on their website AdventureWorks2017.bak )

Dockerfile uses official Microsoft SQL image for Mac mcr.microsoft.com/azure-sql-edge, sets required environment variables:

• ACCEPT_EULA - accept end user licence agreement
• MSSQL_SA_PASSWORD - password to connect to server (any password you want with a minimum of 8 characters)
• MSSQL_USER - user that we will set to SA (system admin)

In the image, we will also install sqlcmd allowing us to execute the database restore command.
Copy locally stored .bak file - this will be used as a source of data on running SQL Server.
Finally, expose port 1433 (default for SQL communication). Once the container is running we will execute entrypoint.sh that will start the SQL server and run the database restore command

1FROM mcr.microsoft.com/azure-sql-edge:latest
2ENV DB_PASSWORD=MyPassword123
3ENV ACCEPT_EULA=1 \
4    DB_NAME=AdventureWorks2017 \
5    MSSQL_SA_PASSWORD=$DB_PASSWORD \
6    MSSQL_PID=Developer \
7    MSSQL_USER=SA
8
9# temp assume root to install tools
10USER root
11RUN apt-get update -y \
12    && apt-get install -y sudo curl git gnupg2 software-properties-common \
13    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
14    && add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/prod.list)" \
15    && apt-get update -y \
16    && apt-get install -y sqlcmd
17USER mssql
18
19COPY ${DB_NAME}.bak /var/opt/mssql/backup/${DB_NAME}.bak
20
21EXPOSE 1433
22
23COPY entrypoint.sh /usr/src/app/entrypoint.sh
24
25ENTRYPOINT ["/usr/src/app/entrypoint.sh"]

Entrypoint bash script:

• starts server
• restores database from backup (copies needed logical name files to server's volumes)
• keeps server running

1#!/bin/bash
2# start server
3/opt/mssql/bin/sqlservr &
4
5sleep 30s
6
7# restore
8sqlcmd -S localhost -U SA -P "${DB_PASSWORD}" -Q "RESTORE DATABASE ${DB_NAME} FROM DISK = '/var/opt/mssql/backup/${DB_NAME}.bak' WITH MOVE '${DB_NAME}' TO '/var/opt/mssql/data/${DB_NAME}.mdf', MOVE '${DB_NAME}_log' TO '/var/opt/mssql/data/${DB_NAME}_log.ldf', REPLACE"
9
10# keep running
11wait $!

The logical names of backup files can be different - use this command if needed to find names for your backup

sqlcmd -S localhost -U SA -P "${DB_PASSWORD}" -Q "RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/${DB_NAME}.bak'"

You can now run docker build and run:

1docker build -t my-sqlserver . 
2docker run --name sql-bak -p 1433:1433 -d my-sqlserver

At this point your server is running and you can connect to it from Azure Data Studio:

Click new connection > complete form:
Connection type: Microsoft SQL Server
Server: localhost
Authentication Type: SQL Login
User name: SA
Password: MyPassword123
Trust Server Certificate: True
(other inputs can stay as defaults)

You should see on the left hand side connection to localhost and when expand Databases be able to see your database.

To make it even easier you can add docker-CompositionEvent.yml file:

1version: '3.8'
2services:
3  sqlserver:
4    build:
5      context: .
6      dockerfile: Dockerfile
7    env_file:
8      - .env
9    ports:
10      - "1433:1433"

and .env file:

1DB_PASSWORD=MyPassword123
2DB_NAME=AdventureWorks2017 # .bak file

and run everyting with:

docker-compose up -d
and
docker-compose down to stop server running

You can find this project in the repository
Thanks for reading!