Run SQL Server on a Mac

Install Docker

You don’t have to know anything about Docker to run this tutorial. But to follow along, you’ll need to install Docker Desktop Community for Mac.¹

Get the docker image

Microsoft is going to stop publishing images to docker hub, and instead, publish only to the Microsoft Container Registry (MCR). At the time of this writing, SQL Server images are still on docker hub, but the example below pulls the image from the MCR.

1

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

e769e11b6344

Create the container

We need to set a few parameters when we initially create the container. We only need to set these once, but you will want to remember the sa user’s password for connecting to the server instance later on.²

1
2
3
4

docker container run -d --name sql \
  -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=D0ntUsebl@nk!' \
  -p 1433:1433 \
  mcr.microsoft.com/mssql/server:2017-latest

• The docker container run command will create and start a new container based on the image supplied at the end of the command.
• The -d command simply tells docker to run the container as a daemon.
• The --name parameter gives our container a name (sql) so that we can use it in subsequent docker commands.
• The two environment variables (-e) that we set are to accept the license agreement, and set the sa user’s password.
• The -p maps traffic on port 1433 of your computer to port 1433 on the container.

If you like to see proof that your container is running

1

docker container ls

This will list all running containers you have. You will see a record for your container, and it should have a “STATUS” of “Up”

Connect to the Server

You have a couple of options for how to connect to your server.

sqlcmd

The image contains sqlcmd located in the /opt/mssql-tools/ directory. You can get to it by logging into a bash prompt within your container, or you can execute the program using the docker container exec command.

Work from within the container

1
2
3

docker container exec -it sql bash
cd /opt/mssql-tools
bin/sqlcmd # will show help

• -it means run interactively, like a terminal
• sql is the name we gave to the container
• bash is the program to run

To get out of the container, we need to terminate the program we executed (bash). To do this, just type exit.

Call into the container to execute the command

1
2
3

docker container exec sql \
  /opt/mssql-tools/bin/sqlcmd -U sa -P D0ntUsebl@nk! \
  -Q "SELECT [name] FROM sys.databases"

• Here, we leave off -it because we don’t want to work interactively. Once the sqlcmd program exits, we are returned to our command prompt.

An example: Restore a database backup

What if you have a database backup file, MyDb.bak, and you want to restore that in your container? First, you need to copy the file into the container. Use docker container cp for that.

1

docker conatiner cp MyDb.bak sql:/tmp/

• MyDb.bak is the full path to the local file you want to copy into the container.
• sql: in the “copy to” path refers to the container name. It is followed by the path on the container that you want the file to be copied to.

If you’d like to verify that the file was copied, you can log into a bash prompt on the container (see above), and ls /tmp. You should see your file listed.

After the file has been copied, issue a RESTORE DATABASE query.

1
2
3

docker container exec sql \
  /opt/mssql-tools/bin/sqlcmd -U sa -P D0ntUsebl@nk! \
  -Q 'RESTORE DATABASE MyDb FROM DISK = "/tmp/MyDb.bak" WITH MOVE "MyDb" TO "/var/op/mssql/data/MyDb.mdf", MOVE "MyDb_Log" TO "/var/op/mssql/data/MyDb_Log.ldf"'

mssql-cli

mssql-cli is an

interactive command line query tool for SQL Server.

It’s pretty cool, and you’ll get immediate confidence that your server is running properly.

Installation was a little tricky for me. mssql-cli requires Python, and I wanted to use my brew installed Python 3, instead of the Mac pre-installed Python 2.7. Therefore, I created a python virtual environment to run mssql-cli.

First, get into your python virtual environment

1
2

cd <virtual_environment_directory>
. bin/activate

Then, launch mssql-cli, with a username and password

1

mssql-cli -U sa -P D0ntUsebl@nk!

The above command will connect to the default instance. If you want to connect to a particular server, include the -S option.

Once connected, you can start running some commands. Try \ld to list the databases. Pick one, and issue USE <database>. Then, type \lt to list all the tables in that database. Type SELECT * FROM and watch the intellisense work. See the docs for more.

When you are ready to quit, exit mssql-cli by typing quit.

And then leave your virtual environment:

1

deactivate

What about “next” time?

At some point, you’ll probably shut down your container. To get back in and working again, just start the container:

1

docker container start sql

• sql is the name of the container

Now you can connect to it just like before!