Running Microsoft SQL Server on a Mac

Because we can

I truly sorely miss the days I was a cowboy running wild on SQL Servers. A little bit of sp_executesql here, a little bit of xp_cmdshell there 😆…Needless to say, I owe a lot of my early career and resume building to Microsoft in this regard. I started off with SQL Server 2005 running under a desk at my office, but had over time graduated to IT supported servers running versions 2012 - 2017, and sizable rigs for analytics with 256GB of ram (this was circa 2015ish). So that amount of RAM was considered decent at the time.

I had also at one point early in my career attempted to launch my own small business with SQL Server, specifically around simplifying the ETL of data in and out of a SQL Server by automating the creating/execution/destruction of SSIS packages on the fly. What let me to this product was simply my job. I had made a name for myself on moving data quickly from point A to point B via SSIS packages. I had a pretty good pattern down for how I built the packages, keeping them very simple and focusing mainly on lifting the data over to the SQL Server into a temp table and then transforming from there. This was more of an ELT approach. But I had found that I had become a bottleneck with all the demand flying my way. And the few times I tried to teach others how to build and deploy SSIS packages, it was like a deer in headlights (maybe I was just not a good teacher). Thus, I asked myself “Could I instead just give the users some SQL query templates that they could change parameters on and that would go run some executable in the background to create a package, wire it up, run it, and then kill it?” With SQL Server 2012 and the inclusion of the SSISDB, the answer to this question was yes. In a matter of months, I had a working prototype and in less than 6 months, I had a full application that I could deploy to SQL Servers in a matter of minutes. And this application took off like wildfire at my office. To better conceptualize how this tool worked, here is a sample template that could in 4 lines of code pull csv’s off a network drive, build an ssis package, pump the data into the sql server to a temp table called ##raw_dataset1, and then delete the package:

EXEC SSISDB.dbo.usp_run_data_transfer 
    @src_sys = 'FLATFILE' 
   ,@src_file_path = 'F:\some_network_drive\some_data_stuff*.csv'
   ,@dest_tbl_nm = '##raw_dataset1'

I still have an old copy of this application, that can be found here, but be warned - I have not touched it since probably 2018, so use and experiment at your own risk:

• SQLETL - Dynamic SSIS Packages

Alright, Now that I Got That Out Of The Way…

Where were we? Oh yea, today we are going to demonstrate how to run MSSQL on a Mac, specifically an M2 Pro. I had found this example article from Microsoft that runs SQL Server in a docker container, and they swear it works on Macs…really MS? No gotchas? No weird edge cases? Or did you run this in some pristine lab and forgot half the steps you actually did to make it work?

Well rest assured, this tutorial actually will show you how to make it work as well as load and run a script for fun.

So when I took the example that MS provided on their site and converted it to a docker-compose file as well as added a volume for my own shared scripts, this is what I came up with:

Pretty straight forward right?

…However, once I ran this command to bring up the container, things unraveled quickly:

docker compose up -d

I noticed the container spun up almost immediately but then started restarting every 3 seconds or so.I tried logging into the container but kept getting bounced. And now we are at:

Rabbit Hole #1

For this first rabbit hole, I actually used chat gippity to interrogate the errors I was getting in the docker logs and surprisingly, chat gippity actually gave me the correct answer on what to do. I had to install an emulator on my Mac called colima. I used this home brew command to pull it:

brew install colima docker

Once installed, we had to launch the colima emulator as follows:

colima start --arch x86_64 --memory 4 --cpu 2

Now let’s try launching the container again:

docker compose up -d

Rabbit Hole #1-a

I was now getting issues about colima not behaving well when docker tried to launch our mssql container. However, the silver lining on this one was that colima at least gave me a verbose and informative error message saying I was missing library qemu. From their docs, it looks like yet another emulator. Thus, let’s give this a whirl:

brew install qemu

…and now docker again:

docker compose up -d

Alright, no errors and the container is stable is no longer restarting every few seconds. So now that the container is “stable”, let’s login to it via:

docker exec -it sql2022 "bash"

Next, let’s CD into the directory inside the container that has the sqlcmd executable so we can interact with our SQL Server:

cd /opt/mssql-tools18/bin

Rabbit Hole #2

The Microsoft article that I linked above showed a pretty decent example of how to log into the SQL Server in the container running this sqlcmd:

./sqlcmd -S localhost -U sa -P "PA11Word$"

However, I was getting inconsistent and bizarre error messages each time I attempted logging in ranging from:

• Invalid certificates

• TCP/IP connection closed abruptly

Here’s a screenshot of one of the example errors:

But I would not give up. I knew I was close. I went to sqlcmd’s docs, and looked for any flag that had to do with certificates and saw the “-C” flag…so hey, why not give it a shot?

./sqlcmd -S localhost -U sa -P "PA11Word$" -C

Alright, We Are In! Now What?

Let’s start by running a simple query to check our MSSQL version

select @@version
go

Pro Tip - In sqlcmd, you need to add the word “go” as a separate line to execute code

Alright, we are running SQL Server 2022, just like our container image promised we would 😁. But now, let’s run an actual script. For this tutorial, I have included 2 scripts (sp1.sql and sp2.sql).

Here is what sp1.sql looks like:

To run a script in sqlcmd, its pretty straight foward; we simply run this:

:r /scripts/sp1.sql
go

Pro Tip - Remember in our docker compose file the “volumes” section. That is where we effectively mounted our folder “scripts” into the container as a shared drive. You can still locally edit those scripts and the container will recognize them immediately when you run them again. This makes QA pretty easy and doesn’t require stopping/restarting the container to test new scripts.

The “:r” thing is a command for sqlcmd to run a script in a file. Again, don’t forget to put the “go” on a separate line. If we did that all correctly, we would see this:

Now that it’s loaded, lets run the stored proc as follows:

exec dbo.sp_do_some_bad_stuff
go

And if we did that correctly, we would see this output:

And there ya go.

Clean Up

To get out of all this stuff in the terminal, we need to:

1. exit sqlcmd by typing “exit” and hitting enter

2. exit the container by typing “exit” and hitting enter

3. run “docker compose down” to bring down the container

4. run “colima stop” to stop the Colima emulator

Summary

This article has demonstrated how to run Microsoft SQL Server in a container on a mac m2 pro (Apple silicon). The road for this one wasn’t easy, but it was rewarding at the end. Going forward, you can fork this code if you’d like, change/add/alter the existing scripts that I included to your liking, and have at it.

Hope you learned something from today’s fun tutorial.

THE CODE

Thanks for reading,
Matt