From Windows to Mac: Hosting Azure SQL locally without missing SSMS

Introduction

I recently switched from Windows to a new MacBook M4 running macOS Sequoia, and since then, I've had to find new tools and ways to fulfill my everyday development tasks. By the way, I've never looked back.

One of those tasks was to download a dump from an Azure SQL database and host it locally in a Docker container to ease development and save on Azure costs. This can be helpful, e.g., when testing migration scenarios.

I'd have started SQL Server Management Studio on Windows to perform this task. However, SSMS doesn't run on macOS.

But there are two options left. Using Azure Data Explorer with the SQL Server Dacpac extension or using the CLI sqlpackage

Export the backup

To export your Azure SQL database to a bacpac file, you need an Azure Storage Account beforehand to store it. Then, utilize Azure CLI.

az sql db export \
  --name sqldb-foobar-dev \
  --resource-group <resource-group> \
  --server sql-server-instance \
  --admin-user <your-admin> \
  --admin-pass <your-pass> \
  --storage-uri https://<your-account>.blob.core.windows.net/<container>/sqldb-foobar-dev.bacpac \
  --storage-key-type StorageAccessKey \
  --storage-key <your-key>

Start a SQL Server in Docker

mkdir -p $HOME/mssql/data
mkdir -p $HOME/mssql/log
mkdir -p $HOME/mssql/secrets

docker run \
    -p 1433:1433 \
    -v $HOME/mssql/data:/var/opt/mssql/data \
    -v $HOME/mssql/log:/var/opt/mssql/log \
    -v $HOME/mssql/secrets:/var/opt/mssql/secrets \
    --env ACCEPT_EULA=y \
    --env MSSQL_SA_PASSWORD=<your-pass> \
    --name mssql \
    --rm \
    --pull missing mcr.microsoft.com/mssql/server:2022-latest

Import the backup using Azure Data Explorer

Ensure you have the SQL Server Dacpac extension installed and download the .bacpac to your machine.

Connect to SQL Server

• Open Azure Data Explorer and click on "New Connection"
  • Server name: localhost
  • Authentication Type: SQL Login
  • User name: sa
  • Pasword: <your-pass>
• Click Connect

Import the .bacpac file

• Open the Command Palette and search for DacFx: Data-tier Application Wizard.
• Select Create a database from a .bacpac file [Import Bacpac]
• Select the File location, Target Server, and Target Database
• Click Import and lean back

Import using sqlpackage

Alternatively, you can install the sqlpackage tool. Since it was migrated to .NET Core, it will run on MacOS.

Install

dotnet tool install microsoft.sqlpackage -g

Import the .bacpac file

sqlpackage \
 /action:import \
 /sourcefile:'<your-bacpac-location>' \
 /targetservername:localhost \
 /targetdatabasename:'sqldb-foobar-dev' \
 /targetuser:sa \
 /targetpassword:<your-pass> \
 /targettrustservercertificate:true 

Further reading

Download and Install SqlPackage - SQL Server

Download and Install SqlPackage for Windows, macOS, or Linux

Microsoft Learndzsquared

SQL tools overview - SQL Server

SQL query and management tools for SQL Server, Azure SQL (Azure SQL database, Azure SQL managed instance, SQL virtual machines), and Azure Synapse Analytics.

Microsoft Learnmarkingmyname