Setting up Microsoft SQL Server on a Mac

In this blog, I share how I set up a SQL Server on my Mac using Docker, and created a streamlined environment for practicing T-SQL with Visual Studio Code and the AdventureWorks database.

Introduction

If like me you’re learning T-SQL, and would like to practice this at home on your Mac, you’ll need to set up a SQL server. As this is Microsoft software, designed for Windows computers, a few extra steps are necessary for Mac users. Here I show how I accomplished this using Docker. I also show how I set up the AdventureWorks database, used in Microsoft’s DP-080 T-SQL course, and queried this using Visual Studio Code.

Docker makes it possible to run software in containers, which include everything needed to run software (and thus are like lightweight virtual machines).¹ For my SQL Server, as shown below, I used a Linux container running Ubuntu. The result was a setup like this:

This setup means that while SQL Server runs in a Linux environment inside Docker, it’s possible to interact with it directly via VS Code on a Mac just as if it were running natively on Windows. Windows users can also use Docker, of course, using the process outlined here – they just have the additional, simpler, option of installing the SQL Server directly via the .exe executable file.

Setting up the Docker container

Docker Desktop is something I’ve used periodically, so it was already installed for me (it’s available here). Once it’s running, the following Terminal command pulls Microsoft’s latest SQL Server image (which, as of writing, remains the 2022 SQL server):

docker pull mcr.microsoft.com/mssql/server:2022-latest

The downloaded image can then be launched with the following code, after deciding on a suitable password:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourStrongPassword' -p 1433:1433 --name sqlserver -d mcr.microsoft.com/mssql/server:2022-latest

To clarify the different elements of this:

• -e 'ACCEPT_EULA=Y': Accepts Microsoft’s license agreement
• -e 'SA_PASSWORD=YourStrongPassword': Sets the system administrator password
• -p 1433:1433: Maps the container’s port 1433 to Mac’s port 1433
• --name sqlserver: Names the container (which then appears in Docker Desktop’s container list)
• -d: Runs the container in detached mode (in the background)

After running this command, I checked that the container was running with:

docker ps

This confirmed the container was up and running:

CONTAINER ID   IMAGE                                        COMMAND                  CREATED              STATUS              PORTS                                       NAMES
3cc4d166a506   mcr.microsoft.com/mssql/server:2022-latest   "/opt/mssql/bin/perm…"   About a minute ago   Up About a minute   0.0.0.0:1433->1433/tcp, :::1433->1433/tcp   sqlserver

This can be the last use of terminal commands here: now that the container is set up, it can be started or stopped via the Docker Desktop UI (the relevant buttons are in the ‘Containers’ list within the app).

Connecting VS Code to the SQL Server

With SQL Server running in Docker, the question was now how to interact with it. I decided to use Visual Studio Code (available here), as it’s now Microsoft’s recommended tool for this following the announced retirement of Azure Data Studio.

To interact with the server I installed Microsoft’s “SQL Server (mssql)” extension for VS Code. Once installed, a connection to the server can be created as follows:

1. Click on the SQL Server “Connect” icon in the VS Code activity bar (or search for “MS SQL: Connect” in the command palette)
2. Select “Create Connection Profile”
3. Enter the following connection details:
   • Server: type localhost,1433 (the default port we mapped earlier)
   • Database: <Default> (i.e. press return, because we haven’t created a database yet)
   • Authentication Type: select “SQL Login”
   • User name: type sa (the default System Administrator account)
   • Password: The password set when creating the container
   • Optionally, choose to save the password
   • Profile Name: e.g. SQL_Server_Docker

After saving this, VS Code connects to the SQL Server running in Docker (you may need to click on “Enable Trust Server Certificate” in a popup which appears). The message “mssql: Profile created and connected” should appear in the lower right of VS Code. This connection then appears in the VS Code SQL Server sidebar, showing a series of folders (including a “Databases” folder with a “System Databases” subfolder which includes the databases “master”, “model”, “msdb”, and “tempdb”)

Setting up the AdventureWorks database

At this point, I had a functioning SQL Server on my Mac, accessible through VS Code, but I still needed to set up the AdventureWorks database used in the DP-080 course materials.

Microsoft provides a lightweight version of the AdventureWorks database specifically tailored for this course. To set it up, I downloaded the adventureworkslt.sql script from Microsoft’s GitHub repository, dragged and dropped it into my VS Code folder, and opened the script in VS Code.

In the GitHub repository, Microsoft advises: “Ensure the master database is selected, and then run the script to create the adventureworks database. This will take a few minutes”. To ensure the master database was selected, I added this at the start of the adventureworkslt.sql script:

USE master;
GO

I then executed the script, and when prompted clicked to connect to the database – the process took around a minute to create the database and populate it with the AdventureWorks data.

Streamlining access to the database

This created the AdventureWorks database, which I could explore within .sql scripts in VS Code – yay! However, there were a couple of things that irritated me.

First, because my initial profile didn’t specify a database name, it was necessary to prepend the following code to any SQL queries:

USE AdventureWorks;
GO

My solution to this was to create a new connection profile, following the steps above, this time specifying the database as adventureworks. Once connected with this profile, the USE AdventureWorks; GO statement was no longer necessary.

Second, it was always necessary to specify the schema SalesLT, as well as the table name, to select a table, e.g.

SELECT TOP 10 * 
FROM SalesLT.Product
ORDER BY ListPrice DESC;

To take a step back, the overall database schema can be inspected with this SQL code:

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

This shows:

Although it’s perfectly fine to access tables this way, because I wanted to use this database for T-SQL practice I was keen to avoid unnecessary code and wanted to enable more direct table access.

My solution was to create synonyms for the tables as follows:

CREATE SYNONYM Address FOR SalesLT.Address;
CREATE SYNONYM Customer FOR SalesLT.Customer;
CREATE SYNONYM CustomerAddress FOR SalesLT.CustomerAddress;
CREATE SYNONYM Product FOR SalesLT.Product;
CREATE SYNONYM ProductCategory FOR SalesLT.ProductCategory;
CREATE SYNONYM ProductDescription FOR SalesLT.ProductDescription;
CREATE SYNONYM ProductModel FOR SalesLT.ProductModel;
CREATE SYNONYM ProductModelProductDescription FOR SalesLT.ProductModelProductDescription;
CREATE SYNONYM SalesOrderDetail FOR SalesLT.SalesOrderDetail;
CREATE SYNONYM SalesOrderHeader FOR SalesLT.SalesOrderHeader;

It’s only necessary to run this code once, because the synonyms are stored as metadata within the database. Following this, the query above can also be executed as follows:

SELECT TOP 10 * 
FROM Product
ORDER BY ListPrice DESC;

Finally, a handy way to practice T-SQL on a Mac!