Best Practices for Docker to run SQL Server on a Mac

By:   |   Updated: 2024-07-12   |   Comments   |   Related: > Containers


Problem

I use Apple hardware almost exclusively and have done so for more than half of my career. These days, I do less and less local testing of SQL Server syntax and features, but when I do, I use Docker containers.

I've written about SQL Server and Docker on a Mac before, for example, Testing SQL Server Edge and Docker on the latest MacBooks. But things have changed. Most importantly, we can now run a real SQL Server edition on Apple M chips instead of the Azure SQL Edge flavor. I talked about this development last year: Regular SQL Server on Apple Silicon? Yes you can!

What else do I think you need to know?

Solution

In this tip, I want to share a consolidated list of things you can (or should) do when using Docker to run SQL Server on your own Mac. Some of this may only apply to M chips, but some apply in any scenario - even Windows.

Where to Get Docker

Docker Desktop has a download page. If you are using an M chip, make sure to choose the Apple chip option:

Docker Desktop download options

What Settings to Enable

To use a full-on version of SQL Server, you need to enable Rosetta emulation ("Use Rosetta for x86_64/amd64 emulation on Apple Silicon"). It should be enabled by default, but you can check under Settings (the screenshot is from version 4.30):

Rosetta option in Docker Desktop / Settings

This has been supported since Ventura 13.3; I am on Sonoma 14.2 at the time of writing.

How to Get SQL Server

Once Docker is up and running, you can pull the latest container image per version, e.g., from whatever terminal you prefer (I use iTerm2 and Hyper):

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

How to Start a Container

I'm not a huge fan of yaml or trying to understand docker-compose; if you grok those, great! If not, it's simplest to issue a docker run command to spin up a container on demand. Here is an example:

docker run -d
           --name SQL2022 
           --platform=linux/amd64 
           -e ACCEPT_EULA=1 
           -e MSSQL_SA_PASSWORD=Bak3D_B3anZ 
           -p 6666:1433 
           mcr.microsoft.com/mssql/server:2022-latest

The -d option tells the container to run "detached"; if you leave it out, the container will run in the context of your terminal window and spit out all the error log entries there.

There are several other options to explore…

Name Your Container

Give your container a meaningful --name; without it, your container will have a randomly generated name like this:

c1a39b8aedef60a97aaa96989107253383f7493da605b81bfa1ed9fcd198cfbd

Yuck. With a short and maybe even meaningful name, you will have a much easier time interacting with the container and issuing commands. While you can use shorthand (for example, docker start c1a39b will work), even a few characters are too much for me to want to remember, starting fresh every time.

Indicate the Platform

The --platform argument is only applicable if you are running on an Apple M chip, in which case it must be set to linux/amd64. If you're on an Intel Mac or a Windows machine, just leave this line out.

Accept the EULA

I know it's a well-guarded secret, but nobody reads the license agreement. Still, you need to pretend you did. -e ACCEPT_EULA will take 1 or Y, but your container won't start otherwise.

How to Create a Password

You need to be careful about your sa password, because it must satisfy complexity requirements (they can't be bypassed like in CREATE LOGIN). If your password has a special character (like $), it must be escaped in some contexts. In either case, SQL Server may not start or be able to connect. I suggest something relatively long, with a healthy mix of alphanumeric characters and "safe" symbols, like the one above or:

-e MSSQL_SA_PASSWORD = L00ney_T00ns_43va

What Port to Use

You specify the port SQL Server should run on (mapped internally to 1433) using -p. I like using ports that mean something; for example, if I'm testing SQL Server 2019 and SQL Server 2022 (even if I don't intend to run both at the same time), I'll set the former to port 2019 and the latter to port 2022. This approach won't always make sense; just be sure to use a port that isn't already used on the host (or that might potentially conflict in the future, including other containers). If I don't have a specific one in mind, I tend to default to something easy, like 6666 or 411.

Some Other Tidbits

How to Attach or Restore a Database

SQL Server's installed, so what? You can poke around in master if you like, but what if you want to do something real? Like, with a real database?

An easy way to create small-ish databases inside a container is to copy your backup there and restore it from within.

Docker allows you to interact with the filesystem within the container, so you can easily create folders and move files there from the host. Let's say you have downloaded an AdventureWorks sample database to /Users/me/Downloads/aw/AdventureWorksLT.bak. You can make a backup directory inside the container using docker exec and then copy the file there using docker cp: 

docker exec -it SQL2022 mkdir "/var/opt/mssql/backup"
cd /Users/me/Downloads/aw/
docker cp . SQL2022:/var/opt/mssql/backup/

See where giving the container a logical name comes in handy?

Note: I recommend putting a downloaded file into its folder so you can use cp . (all files) instead of typing out the filename. cp doesn't support wildcards, and I'm sure you don't want to copy your entire downloads folder!

With the file in place within the container, SQL Server can see it so that you can issue a normal RESTORE DATABASE command. But, if the backup was originally created on Windows, you'll definitely need WITH MOVE options. For the 2022 version of AdventureWorksLT:

RESTORE DATABASE AdventureWorksLT 
   FROM DISK = N'/var/opt/mssql/backup/AdventureWorksLT2022.bak'
   WITH REPLACE, RECOVERY, 
   MOVE N'AdventureWorksLT2022_Data' TO N'/var/opt/mssql/data/awlt2022.mdf',
   MOVE N'AdventureWorksLT2022_Log'  TO N'/var/opt/mssql/data/awlt2022.ldf';

Depending on what database you're trying to restore, you should confirm the list of files (and that you don't have hints of unsupported features like filestream or In-Memory OLTP) using RESTORE FILELISTONLY.

Be Aware of Size

So far, I've found that anything under 40 GB is okay. What happens if you get into larger territory, like the backups are too large to move to the container or the databases will be too big once restored or grow too large once you begin using them? You can then override the default container size with an additional option for docker run. The following will create the container with a base size of 256 GB:

 --storage-opt dm.basesize=256G

See this post for some other possible workarounds.

How to Connect from Another Container

Probably the most common issue I've seen on Stack Overflow is when people try to connect to a SQL Server container from another container using localhost or 127.0.0.1. That's the local machine, according to that container, so the container itself, not the local machine, according to the host running that container. You need to tell the container to connect to the right port on the host's IP, which you can get from ifconfig (for me, it was in ifconfig en10 inet on one machine and en7 on another; yours may be elsewhere depending on your configuration):

Ifconfig output showing IPv4 address of host

In this case, where SQL Server is running on port 6666, then to connect from another container, I would tell the application inside that container to connect to:

192.168.1.79,6666

You can potentially do something with the hosts file or other mechanisms within the container to give a name to the host's IP, but I don't do anything like this enough to bother. Some answers, like this one, suggest it can be easily handled by docker exec.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2024-07-12

Comments For This Article

















get free sql tips
agree to terms