SQL Server With Docker

Setting Up SQL Server on Your Mac Using Docker

Modern Microsoft is working really hard to make sure that all their tools work on any operating system. In 2016 they announced support for SQL Server on Linux. Fast forward to 2019 and if you’re a .NET developer there’s a decent chance you want to develop on a Mac since .NET Core will run anywhere and tools like Visual Studio Code offer a great .NET Core experience. But if you’re using SQL Server and don’t want the delay or cost of talking to a cloud hosted database, how do you do it? There’s no Mac installer for SQL Server. And anyone who’s used the installer on Windows, you might not want to revisit that experience anyway.

The answer is Docker! Setting up Docker on MacOS and running SQL Server is a pretty straightforward task with only a couple hiccups along the way. Here’s how to get it done.

Install Docker

This is pretty straightforward. I can’t do a better job of explaining the process than the official Docker for Mac docs. To summarize:

  1. Get a Docker ID at https://cloud.docker.com.
  2. Get the Docker for Mac installer from the official download site.
  3. Install it.
  4. Run it.
  5. Log in.

Install SQL Server

SQL Server is available from Docker Hub as an image. Docker uses this image to create a running container on your computer. I won’t get into images and containers here but if you’d like to read more, here’s the official overview. To install the SQL Server image for Docker we’ll run the following command from your terminal (setting values for “your password” and “local db directory”) and :

sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<your password>' -p 1433:1433 -v mssql:/var/opt/mssql -v <local db directory>:/var/opt/mssql/backups --name mssql-server -h sql1 -d microsoft/mssql-server-linux:latest

I know, it’s a long command but it can be broken down pretty simply.

  • The -e arguments pass a couple parameters that accept the end user license agreement and set an admin password.
  • The -p argument sets the port docker will make available. I suggest leaving this as 1433 to simplify connecting to your server from 3rd-party clients, etc.
  • The first -v argument tells Docker to store the database files in a Docker volume. If you’re thinking why not store this on the host file system, there’s a permission issue that prevents it. This is one of the hiccups I mentioned earlier.
  • The second -v argument maps a second folder (arbitrarily named backups) to a file system folder. This will make it much easier to work with SQL scripts saved in files later. For example, creating a local copy of a production database.
  • The –name argument assigns the name Docker will use for the container. This simplifies starting and stopping the container later.
  • The -h argument sets the hostname Docker will use for the container. This isn’t critical now but may be useful in more advanced scenarios.
  • The -d tells Docker to run the container in daemon mode. Simply put, your terminal will go back to normal operation after executing the command and leave the SQL Server server running.
  • The last argument is the name of the image to use. This will be downloaded from Docker Hub if it wasn’t already. Naturally, if you want a specific version of SQL Server instead of the latest, just change :latest to :<version>.

Make Sure It’s Running

Once the above command finishes, execute the following command in your terminal:

docker container ls

You should see something like the following:

CONTAINER ID   IMAGE                                 COMMAND                    CREATED   
b22c1b74af60   microsoft/mssql-server-linux:latest   "/opt/mssql/bin/sqls..."   6 Weeks ago

 

STATUS        PORTS                          NAMES
Up 30 hours   0.0.0.0:1433->1433/tcp mssql   mssql-server

That’s really all there is to setting up a SQL Server instance on a Mac with Docker!

Starting the Server If It Stops

If you reboot your computer, Docker won’t restart the server automatically. To start it yourself, simply enter the command in your terminal:

docker container start mssql-server

Of course, you could add this to a startup script to have it start automatically.

Connecting to the Server

To connect an external program, just use localhost with the port 1433, user of sa, and the password you set.

To connect a .NET app, you can use the following connection string:

Data Source=localhost;Initial Catalog=<database name>;User id=sa;Password=<your password>;MultipleActiveResultSets=True

Executing Scripts Against the Database

Once your server is up and running, there’s a good chance you’ll want to execute a SQL script on it. You can do this through your 3rd party tool of choice or you can do it quite easily from the terminal thanks to that second volume argument we used above.

-v <local db directory>:/var/opt/mssql/backups

1.Put your script file in the directory you entered for “local db directory”.

2. Connect to your docker container with the command:

docker exec -it mssql-server bash

3. Navigate to the /var/opt/mssql/backups folder with:

cd /var/opt/mssql/backups

4. Execute your script with the command:

/opt/mssql-tools/bin/sqlcmd -U sa -P <your password> -i <script file>

That’s it, your script should now be executing! The sqlcmd executable is installed in your container along with the image.

Deleting Your Server and Data

Completely removing your server is pretty easy. Just execute the following commands in your terminal.

1.Stop the container

docker stop mssql-server

2. Delete the container

docker rm mssql-server

3. Delete the volume

docker volume rm mssql

And that’s it, it’s like it was never there.

Want more development tips? Our talented development team has extensive programming experience, and they’re always working to improve their skills. Contact us today and let us know about development topics you’d like to see more of!

Scroll to Top