Get SQL Server 2022 Running on macOS via Docker

These directions are based on this YouTube video. The comments helped with making sure I was able to connect to the Azure SQL Edge (SQL Server) instance running via Docker the first time.

0. Install Homebrew

Homebrew is an open source package manager for macOS and Linux. ALthough we will not need it for these instructions, if you do not have the brew command already, this will install it.

Open Terminal on your Mac and execute the following command:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

This will require you to enter your password for logging into your account on your computer.

1. Install Docker

You can download Docker Desktop from this page.

Docker is software that runs containers which are like small virtual machines designed to handle smaller portions of an application. In this example, we just need to run a database server.

2. Install Azure Data Studio

You can download and install Azure Data Studio from this page.

Azure Data Studio is the application that will allow us to interact with the database that we will run from the Docker container. This will enable us to run SQL statements and learn SQL.

A Docker ID is an account to use with the Docker Desktop application. To create an account, go to docker.com, click Sign In. At the bottom of the page, click the Don't have an account? Sign Up link and create an account. Once your account is created, it is recommended to enable two-factor authentication in the Account Settings once you have logged in.

Once you have created the Docker account, open the Docker Desktop application your computer. Click the Sign In button in the upper right-hand corner of the application.

4. Download the Official Microsoft SQL Container Image

What exactly is Azure SQL Edge?

It is an optimized database made for IoT (Internet of Things) that is built on the same engine as SQL Server and Azure SQL. Azure SQL Edge also provides the same Transact-SQL (T-SQL) programming functionality.

Docker containers:

What we need are the shell commands for pulling (downloading) the Docker image locally to our computer so that we can use it with Docker. Enter this command in your terminal to download the image to your computer.

# Downloads Microsoft's official Docker image for Azure SQL Edge (SQL Server for IoT) to your computer
docker pull mcr.microsoft.com/azure-sql-edge

# To see what images are on your computer:
docker image ls

5. Run the SQL Image in a Container

In Docker terms, a container is a running instance of an image. Therefore, the docker run command creates a container from the Azure SQL Edge docker image that was downloaded in the previous step.

For the MSSQL_SA_PASSWORD parameter in the command below, change the text yourStrong(!)Password to a password with at least 8 characters containing at least one of each:

uppercase letter
lowercase letter
digit
symbol

These password requirements are confirmed in the documentation.

The SA in MSSQL_SA_PASSWORD stands for system administrator, which means the MSSQL_SA_PASSWORD parameter is setting the system administrator account on SQL Server (username: SA). You should generate and store a password just in case, even if this is for local development.

# Creates a container and starts it while opening access to port 1433
# both inside and outside the container for access to SQL Server from Azure Data Studio
docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=yourStrong(!)Password1' -e 'MSSQL_PID=Developer' -p 1433:1433 --name azuresqledge -d mcr.microsoft.com/azure-sql-edge

# Run this command to confirm that the container named "azuresqledge" is running
docker ps -a

According to the documentation for the Docker image, the MSSQL_PID environment flag defaults to "Developer". MSSQL_PID determines the edition of Azure SQL Edge that will run in the container. More documentation on how to configure Azure SQL Edge can be found here.

In the video, the presenter uses the MSSQL_USER parameter and sets it to SA. However, I could not find documentation for this parameter and therefore, it should not be used.

6. Connect Azure Data Studio to the Docker Image

Open Azure Data Studio and create a connection. In the Connection Details pane, enter the following:

Connection type - Microsoft SQL Server
Server - localhost,1433
Authentication type - SQL Login
User name - SA
Password - yourStrong(!)Password1
Trust server certificate - True

Trust server certificate is required because you will be prompted by Azure Data Studio that the certificate inside the container is invalid, which is to be expected.

Here is the code that the video used to create a table and add some data:

CREATE TABLE Persons (
    [PersonID] int,
    [LastName] varchar(255),
    [FirstName] varchar(255),
    [Address] varchar(255),
    [City] varchar(255)
);

BEGIN TRANSACTION
    INSERT INTO Persons (PersonID, LastName, FirstName, [Address], City)
    VALUES
        (1, 'Smith', 'John', '31 Central Park', 'New York'),
        (2, 'Damon', 'Matt', '5 Gold Lane', 'San Francisco'),
        (3, 'Prescott', 'Dak', 'The Star', 'Dallas')
    ;
COMMIT TRANSACTION;

To run this code, on the left-hand side, find your server, localhost,1433 and expand Databases --> System Databases. Right-click the master database and choose New Query. You can also start a new query document by going to File --> New Query from the Finder bar or by pressing CMD + N.

Once you copy and paste the code above into the query document and run it, if you expand the Tables folder under the master database, you should see the dbo.Persons table has been created, where dbo is the schema (which works like a group) that the Persons table belongs to.

Right-click the dbo.Persons table and choose Select Top 1000 to run a SELECT query on the dbo.Persons table to see the three rows that we added in the code above using the INSERT INTO statement.

7. Starting and Stopping the SQL Server Docker Container

If you attempt to connect to localhost,1433 from Azure Data Studio and you cannot connect, the first thing to check is whether Docker itself is running and whether the Docker container that we created, azuresqledge, is running. From the Docker Desktop application, on the left-hand side, go to Containers. If you do not see the azuresqledge container, turn off the option to Only show running containers. Under the Actions column, you can choose to stop and start the container.

From the terminal, you can use the following commands:

# Show all containers regardless if they are running or not
docker ps -a

# Stop the container
docker container stop azuresqledge

# Start the container
docker container start azuresqledge

# Create the container and start it in case it does not exist
# This command will fail if a container with the same name already exists
# This is the same command as above
docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=yourStrong(!)Password1' -e 'MSSQL_PID=Developer' -p 1433:1433 --name azuresqledge -d mcr.microsoft.com/azure-sql-edge

tap52384/README.md