SQL Server Running on a Mac?!

Image for post
Image for post

Dogs and cats living together, mass hysteria!

UPDATE (Oct 3, 2017): The content of this article has been revised to reflect changes in the latest General Availability (GA) release of SQL Server for Linux. A new section on restoring from backup has also been added!

Microsoft’s recent Connect(); event included a lot of interesting announcements. The one that really got my attention was SQL Server for Linux. My first question, of course: Can I run this on my Mac?

Yes.

The answer (and an increasingly common answer, I might add) is Docker. Here are the steps that worked for me.

Install and configure Docker

If you don’t already have Docker installed, you’ll need to download and install it.

Next step, you’ll need to increase Docker’s available memory to 4GB or more.

Image for post
Image for post

Get the Docker image

Open a Terminal window, and download the latest SQL Server for Linux Docker image.

docker pull microsoft/mssql-server-linux:2017-latest

Now, launch an instance of the Docker image.

docker run -d --name name_your_container -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@55w0rd' -e 'MSSQL_PID=Developer' -p 1433:1433 microsoft/mssql-server-linux:2017-latest

You should now have SQL Server running on your Mac, ready for action!

A few notes on Docker parameters

Tip: Get Kitematic

Kitematic is a nice desktop application for managing Docker containers. The first time you click Open Kitematic, it will prompt you to download and install it. You can then use Kitematic to view the output of your containers, manage their settings, etc.

Image for post
Image for post
Image for post
Image for post

It’s running, now what?

sql-cli is a useful command-line tool for SQL Server. To use it, you’ll need Node.js. Download and install Node.js, if you don’t already have it.

From the Terminal, install sql-cli globally, so you can use it anywhere.

npm install -g sql-cli

Next, connect to your SQL Server instance running in Docker.

mssql -u sa -p P@55w0rd

You can now query and execute SQL Server commands from the mssql> prompt. Type .quit and press Enter to exit.

Getting an existing SQL database into Docker

If you’re like me, you have a SQL database you use for development you want to move to your new SQL container running on your Mac. I have good news.

Option 1: restore a backup

Yes, you can restore a SQL backup file (.bak) created on Windows! You can start by creating a backup of your existing database using SQL Server Management Studio running on your Windows PC/Server.

1. Generate a backup file

Image for post
Image for post
Image for post
Image for post

Next, locate the backup file on your Windows machine and copy the file to your Mac. The details of this step I leave to you, dear reader. I hope it’s not too painful.

2. Restore the backup file

You’ll need to use Docker commands from the Terminal to copy the backup file into the container, and restore the database.

Note: Change the paths and names, such as dogfood and container-name to match the name of your database and Docker container.

Step 1: Copy the .bak file into your Docker container.

docker cp ~/Downloads/sql/dogfood.bak container-name:/tmp/dogfood.bak

Step 2: Run SQL RESTORE

docker exec container-name /opt/mssql-tools/bin/sqlcmd -U sa -P P@55w0rd -Q "RESTORE DATABASE [dogfood] FROM DISK='/tmp/dogfood.bak' WITH MOVE 'dogfood' TO '/var/opt/mssql/data/dogfood.mdf', MOVE 'dogfood_Log' TO '/var/opt/mssql/data/dogfood_Log.ldf' "

Step 3: Verify your database is alive

docker exec container-name /opt/mssql-tools/bin/sqlcmd -U sa -P P@55w0rd -Q "SELECT [name] FROM sys.databases"

Ready to rock!

Option 2: generate scripts

Another option is to use sql-cli to run the scripts to recreate a database running in Docker.

1. Generate scripts

First step is to use SQL Server Management Studio to generate scripts from an existing database.

Right-click on your database, and choose Tasks -> Generate Scripts…

Image for post
Image for post

I chose to separate my table and data scripts from my Views, Stored Procedures, User-Defined Functions, etc.

Image for post
Image for post

Next, click on Advanced

Image for post
Image for post

I chose to “Check for object existence,” “Script DROP and CREATE,” and set “Types of data to script” to “Schema and data.”

Checking for object existence and DROP/CREATE allows me to re-run the same script against an existing database, if I want to reset it back to its original state.

Image for post
Image for post

Choose a location to save your scripts. When finished, repeat the steps to script out your Views, Stored Procedures, and UDFs, if necessary.

2. Run the scripts against your SQL Server running in Docker

mssql> use master
mssql> CREATE DATABASE devdb

Note: Change devdb to match the name of the database you scripted. The generated scripts expect a database with that same name to exist.

mssql> use devdb
mssql> .run script.sql

You now have a copy of your database, running on your Mac, without the need for entire Windows VM!

Further reading…

Written by

Family man, developer, speaker, musician, illustrator, and Microsoft MVP. Runs on a high-octane mixture of caffeine and JavaScript. Made entirely of bacon.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store