Setup SQL Server and Docker on a MacBook

Problem

Docker is a great way to play with SQL Server without the administrative overhead of managing an instance. I wanted to get started using my new MacBook Pro M1 for local development and testing, but quickly discovered there are no Docker images for SQL Server that support ARM64 just yet. Can I test SQL Server locally on this new hardware?

Solution

To get around the lack of ARM64 Docker images, I decided to use the Azure SQL Edge flavor of SQL Server. When I got it working, I thought I would share how I set this up (though note that this tip is equally relevant if you have an Intel Mac or, barring a few minor syntax differences, a Windows PC).

Using Azure SQL Edge probably sounds risky, and may imply I am building solutions only for sensors or IoT devices or that can only run in Microsoft’s cloud. In truth, it can run wherever I like, and supports all of the SQL Server features I actually use. The list of limitations is significant, but only slightly longer than the list of features not supported on Linux. Some of the notable missing features:

• Active Directory (read: SQL authentication only)
• Replication, mirroring, snapshots, Availability Groups, Failover Clustering
• Filestream, Filetable, StretchDB, Service Broker, and Full-Text Search
• In-Memory OLTP, Polybase, and language extensibility (e.g. Spark)
• Distributed query, Distributed Transaction Coordinator, and linked servers
• CLR (including HierarchyId, Geography/Geometry methods, and functions like TRY_PARSE and FORMAT)
• Resource Governor, Buffer Pool Extension, persistent memory, and Hot-add CPU / memory

A more exhaustive list is available here: Supported features of Azure SQL Edge. If none of those features are deal-breakers and you’re still with me, let’s see how easy it is to set up.

Docker

We’ll need Docker first to get started – easiest is to install Docker Desktop. If we go there with a Mac, we’ll be offered these options; choose wisely, as they indicate the platform we’re using, not anything related to the containers we want to run:

There is a great guide to getting started here: Install Docker Desktop on Mac – guides are also available for Windows and Linux (e.g. Ubuntu). But for our purposes, we can jump right in by double-clicking docker.app (or docker.exe).

Next, we can open up a terminal/cmd window, and check that the service is running:

docker run hello-world

Part of the output should include:

Hello from Docker!
This message shows that your installation appears to be working correctly.

Azure SQL Edge

With Docker up and running, next we need to pull the most recent Azure SQL Edge container image:

docker pull mcr.microsoft.com/azure-sql-edge:latest

We can create the appropriate container with the following command (it is quite annoying that some of these parameter names are just slightly different from the ones for “regular” SQL Server):

docker run -d 
  --name SQLEdge 
  -p 6666:1433 
  -e ACCEPT_EULA=1 
  -e MSSQL_SA_PASSWORD=3dg3Y0urB3ts 
  -e MSSQL_PID=Developer  
  mcr.microsoft.com/azure-sql-edge

A brief breakdown of the parameters:

• -d stands for detach, which means run the container in the background (if you leave out -d, you will get all the errorlog output in the terminal window,
• --name – I tend to give my containers explicit names so I don’t have to care about the arbitrary hex identifier they have been assigned
• -p is for port configuration, and the value is in the form external:internal. I like to use specific external ports to help with ambiguity and avoid conflicts with other apps and services, but usually my choices here are version-specific – in this case 6666 was completely arbitrary. I always use 1433 for the internal port for SQL Server containers.
• -e is for passing in additional parameters (environment variables for the container):
  
  • ACCEPT_EULA means you agree to the terms of use (Azure SQL Edge Developer EULAY and sometimes listed as 1, and either will work. If you exclude it:

    The SQL Server End-User License Agreement (EULA) must be accepted before SQL Server can start. The license terms for this product can be downloaded from ...

  • MSSQL_SA_PASSWORD needs to have three of these four items: upper case, lower case, number, symbol. I recommend sticking to the first three since certain symbols (like $) will give us fits. Don’t make it too short or simple; otherwise, SQL Server will just shut itself down:

    ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.

  • MSSQL_PID is the edition – currently Developer and Premium are supported. For testing and local development we will almost certainly always want Developer, which is the default, so arguably the parameter can be left out.
• the last (unnamed) argument is just telling Docker which container image we want to use.

We can run the following to make sure the container is up and running, and we should see SQLEdge in the list (and also how much space it is using):

docker ps -s

Though we need to wait long enough (~10 seconds) to be sure (a) the container has had enough time to fully initialize and (b) it’s also had enough time to shut down due to a runtime or configuration error. If the container isn’t running after 10 seconds, it’s quite likely that it crashed. We can read the SQL Server errorlog directly, even if SQL Server isn’t running, using the following command (another reason it’s a good idea to give containers an intuitive name):

docker logs SQLEdge

If the container crashed, the output should give us some hints about what happened, and hopefully they are easy to resolve.

We can validate SQL Server is running by using Azure Data Studio to connect and then running SELECT @@VERSION;

Microsoft Azure SQL Edge Developer (RTM) - 15.0.2000.1559 (ARM64)
    Jun  8 2021 15:48:33
    Copyright (C) 2019 Microsoft Corporation
    Linux (Ubuntu 18.04.5 LTS aarch64) <ARM64>

Restoring a real database

I like to use Stack Overflow and AdventureWorks databases for consistency. In this case, let’s use the “lightweight” AdventureWorks database to steer clear of any advanced functionality that might not be supported (and also to help keep the size within the magic 40GB threshold, which I talk about in this blog post). You can download the sample databases here: AdventureWorks sample databases (2019 direct link).

We can get this backup file into the container using Docker commands to create the folder and copy the .bak file (note that your source location may be different):

docker exec -it SQLEdge mkdir "/var/opt/mssql/backup"
docker cp ~/Downloads/AdventureWorksLT2019.bak SQLEdge:/var/opt/mssql/backup/

Then we can restore:

RESTORE DATABASE AdventureWorksLT2019 
  FROM DISK = '/var/opt/mssql/backup/AdventureWorksLT2019.bak'
  WITH REPLACE, RECOVERY, 
  MOVE 'AdventureWorksLT2012_Data' TO '/var/opt/mssql/data/aw2019.mdf',
  MOVE 'AdventureWorksLT2012_Log'  TO '/var/opt/mssql/data/aw2019.ldf';

Output should be something like this:

Processed 840 pages for database 'AdventureWorksLT2019', file 'AdventureWorksLT2012_Data' on file 1.
Processed 2 pages for database 'AdventureWorksLT2019', file 'AdventureWorksLT2012_Log' on file 1.
Converting database 'AdventureWorksLT2019' from version 904 to the current version 921.
Database 'AdventureWorksLT2019' running the upgrade step from version 904 to version 905.
…
Database 'AdventureWorksLT2019' running the upgrade step from version 920 to version 921.
RESTORE DATABASE successfully processed 842 pages in 0.029 seconds (226.697 MB/sec).

Now we can run queries against that database, e.g.:

SELECT COUNT(*) FROM SalesLT.vProductAndDescription;
----
1764

Removing the container

If we are done testing or wish to wipe it clean and start over, we can issue stop and rm commands:

docker stop SQLEdge
docker rm SQLEdge

Conclusion

Azure SQL Edge is a way to test and develop using SQL Server locally, and have a consistent experience between machines, whether they’re PCs running Windows, Intel-based Macs, or the new Apple silicon (M1). In a future tip, I’ll take a closer look at any performance or functional differences I observe as I start to compare.

Next Steps

• Grab Docker Desktop and start playing with containers for local development and testing!
• See these tips and other resources regarding SQL Server containers or SQL Server on Linux:
  • Run SQL Server vNext (CTP1) as a Docker Container on a Mac
  • Build Docker Containers with External Storage on Your Desktop
  • SQL Server 2017 installation on Ubuntu using Docker
  • Edwin Sarmiento’s series on Docker containers: Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6
  • Run and Manage SQL Server 2019 CTP 2.0 RHEL Docker Container
  • All SQL Server on Linux tips

Aaron Bertrand

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He also blogs at sqlblog.org.

• MSSQLTips Awards: Author of the Year – 2016, 2023 | Leadership (200+ tips) – 2022