To follow along, you’ll need:
- An Apple Mac with an Apple Silicon processor (M1, M2, etc – not an Intel or AMD CPU)
- Azure Data Studio
- Docker Desktop 4.25 or newer
- An Internet connection
1. Download & Run the SQL Server Container
We’ll follow the instructions from Microsoft’s documentation, but I’m going to abbreviate ’em here to keep ’em simple. Open Terminal and get the latest SQL Server 2022 container. You can run the below command in any folder – the file isn’t copied into your current folder.
1 |
sudo docker pull mcr.microsoft.com/mssql/server:2022-latest |
That’ll download the ~500MB container, which takes a minute or two depending on your Internet connection. Next, start the container:
1 2 3 4 |
sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" \ -p 1433:1433 --name sql1 --hostname sql1 \ -d \ mcr.microsoft.com/mssql/server:2022-latest |
Don’t use an exclamation point in your password – that can cause problems with the rest of the script. (Frankly, to keep things simple, I would just stick with upper & lower case letters plus numbers.)
In the above example, the container’s name will be sql1. If you decide to get fancy and change that, remember the name – you’ll need it later.
You may get an error about the platform – that’s okay, ignore it. Docker Desktop will show the container as running:
And in less than a minute, you can connect to it from Azure Data Studio. Open ADS and start a new connection:
- Server name: localhost
- Port: 1433
- Username: sa
- Password: the one you picked above
With any luck, you’ll get a connection and be able to start running queries. To have fun, we’re going to want a sample database.
2. Download & Restore the Stack Overflow Database
Again, I’m going to abbreviate and change Microsoft’s documentation to keep things simple. Open Terminal and go into a folder where you’d like to keep the backup files. In my user folder, I have a folder called LocalOnly where I keep stuff that doesn’t need to be backed up, and I have Time Machine set to exclude that folder from my backups.
1 |
cd ~/LocalOnly |
If you don’t have a folder like that, you can just go into your Downloads folder:
1 |
cd ~/Downloads |
Download the Stack Overflow Mini database, a small ~1GB version stored on Github:
1 |
curl -L -o StackOverflowMini.bak 'https://github.com/BrentOzarULTD/Stack-Overflow-Database/releases/download/20230114/StackOverflowMini.bak' |
Make a backups folder inside your Docker container – note that if you changed the container name from sql1 to something else in the earlier steps, you’ll need to change it here as well:
1 |
sudo docker exec -it sql1 mkdir /var/opt/mssql/backup |
Copy the backup file into your container:
1 |
sudo docker cp StackOverflowMini.bak sql1:/var/opt/mssql/backup |
In Azure Data Studio, restore the database:
1 2 3 |
RESTORE DATABASE StackOverflowMini FROM DISK='/var/opt/mssql/backup/StackOverflowMini.bak' WITH MOVE 'StackOverflowMini' TO '/var/opt/mssql/data/StackOverflowMini.mdf', MOVE 'StackOverflowMini_log' TO '/var/opt/mssql/data/StackOverflowMini_log.ldf'; |
Presto – you now have the Stack Overflow database locally.
3. Stop & Start the Docker Container
When you want to stop it, go to a Terminal prompt and type:
1 |
docker stop sql1 |
Because you’re exceedingly smart and almost sober, you can probably guess the matching command:
1 |
docker start sql1 |
And yes, the database will still be there after you stop & start it.
For Mac Users, This is a Godsend.
Because this just works, at least well enough to deal with development, blogging, demoing, presenting, etc. For those of us who’ve switched over to Apple Silicon processors, this is fantastic. I love that I can work on the First Responder Kit without having to fire up a Windows VM.
This isn’t for production use, obviously, and it’s not supported in any official way. In this post, I didn’t touch on security, firewalls, SQL Agent, other versions of SQL Server, performance tuning, memory management, or anything like that, nor do I intend to get involved with any of that in Docker anyway.
If you want to learn more about the two components that are probably the most new to you (Docker and SQL Server for Linux), subscribe to Anthony Nocentino of Nocentino.com. He’s the go-to person for SQL Server on containers & Linux. He’s got several Pluralsight courses on these, too.
12 Comments. Leave new
Works a treat 🙂
Hi Brent
I use something like this normally after the cp of the files to the container. I don’t know if you agree.
$crd = get-credential
$fileStructure = New-Object System.Collections.Specialized.StringCollection
$fileStructure.Add(“/var/opt/mssql/data/StackOverflow2013_1.mdf”)
$filestructure.Add(“/var/opt/mssql/data/StackOverflow2013_log.ldf”)
$filestructure.Add(“/var/opt/mssql/data/StackOverflow2013_2.ndf”)
$filestructure.Add(“/var/opt/mssql/data/StackOverflow2013_3.ndf”)
$filestructure.Add(“/var/opt/mssql/data/StackOverflow2013_4.ndf”)
Mount-DbaDatabase -SqlInstance mysqlserver -Database StackOverflow2013 -FileStructure $fileStructure -SqlCredential $crd
Set-DbaDbOwner -SqlInstance mysqlserver -SqlCredential $crd
No, I’ve always used restores rather than keeping full-size copies of databases around.
Oh, sorry I didn’t know that. When using restore I do to this make my life easier.
This one will be more the way you say.
$crd = get-credential
Restore-DbaDatabase -SqlInstance mysqlserver -Path /var/opt/mssql/backup/StackOverflow.bak -DestinationDataDirectory /var/opt/mssql/data -DestinationLogDirectory /var/opt/mssql/data -SqlCredential $crd
Set-DbaDbOwner -SqlInstance mysqlserver -SqlCredential $crd
OK, cool, yeah, that’s an alternative to what I’m doing in the post.
It doesn’t really add value for me personally because the rest of the workflow doesn’t involve PowerShell, but thanks!
If you get the error “/opt/mssql/bin/permissions_check.sh: line 4: [: : integer expression expected”. then go back and read the post again. You (I) forgot to enable Rosetta.
Hi, Brent, another option is to use Parallels to make a Windows 11 vm, and then use the scripts found at https://github.com/jimm98y/MSSQLEXPRESS-M1-Install
I am NOT the author or maintainer of those scripts. I have used the 2019 Developer Edition variant in a Parallels vm on my M2 Mac, and it worked where using the stock Microsoft GUI installer did not. I am not a Powershell guru but the script does seem to address the issues that cause installation of SQL Server Developer Edition 2019 to fail on a Parallels vm otherwise. The issues mostly involved the master database not being set up, some registry keys not being set, and Windows Update things not working as intended, when using the GUI installer in a vm.
As always, YMMV, beware of running scripts on the internet, etc. But this does seem to do what is advertised, and you or other readers may find it useful.
Really fantastic breakdown. Thanks.
Thanks!
I’ve been a “SQL Server on Windows” person since 1996 so trying this is way out of my comfort zone. However, when running this command in terminal I am being asked for a password: sudo docker pull mcr.microsoft.com/mssql/server:2022-latest
I tried my Mac (M2 MacBook Pro) user password with no luck. Any assistance is appreciated.
Correct, sudo requires your password.
Thank you!!