Develop, Build, and Deploy Microsoft SQL Databases on macOS
Tags: mssql, dacpac, docker, macos
Published: 2020-12-09 21:05:31

---
title: Develop, Build, and Deploy Microsoft SQL Databases on macOS
published: true
description: A simple setup for cross-platform MSSQL database development.
tags: mssql, dacpac, docker, macos
cover_image: https://images.unsplash.com/photo-1550684848-fac1c5b4e853?ixid=MXwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHw%3D&ixlib=rb-1.2.1&auto=format&fit=crop&w=1350&q=80
---

*[Photo by Rodion Kutsaev on Unsplash](https://unsplash.com/photos/pVoEPpLw818)*
*Updated: 2020-12-16*

## Intro
In my day to day work as a consultant on customer projects the typical tech stack involves having a Microsoft SQL Server (MSSQL) for persistence with several ASP.NET Core web APIs on top, and a web-based UI, which nowadays I like writing in React.

Being in home office mode for several months now, I am developing on my personal MacBook Pro - in a Parallels Windows VM. The reason for the latter is the lack (so far) of cross-platform tooling for building Visual Studio database projects and deploying the resulting DACPACs. Our team successfully applied the target-state deployment approach for quite a while and never saw the need for an alternative migration script-based approach (which would not have the Windows platform limitation).

Thanks to a curiosity-driven investigation of one of my colleagues it turns out that the complete toolchain is available now cross-platform which enables us to build and deploy DACPACs on Linux and macOS alike. This post summarizes the steps required to prepare the tools for macOS.

Requirements: Basic knowledge of SSDT, dotnet CLI, and docker.

## Develop, Build & Publish Database Projects in ADS

Until recently developing MSSQL database projects was only possible with Visual Studio in conjunction with the SQL Server Data Tools (SSDT).

[Azure Data Studio (ADS)](https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15) is a cross-platform SQL Server Management Studio (SSMS) replacement and provides a similar set of features. Its recent [SQL Database Projects extension](https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension?view=sql-server-ver15) turns it into a full IDE for MSSQL database projects including build and deployment capabilities.

**Download and install [ADS](https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15)**. If you're already familiar with VS Code, then you'll quickly feel at home with ADS too. Once installed open the extensions view, search for *SQL Database Projects* (preview version 0.4.1 at time of writing) and install it as well.

That's all required for working with MSSQL database projects in macOS (and Linux) from the safety of an IDE with publishing targets somewhere in the cloud.

If you want to automate things, or if you prefer to work in the shell, or like to run a local MSSQL instance for developing purposes read ahead.

## Building Database Projects in the Shell

**Download and install [.NET Core or .NET 5.0](https://dotnet.microsoft.com/download)** if you not already have as we're going to facilitate the `dotnet` CLI.

A database project build is now just a few keystrokes away:
```bash
dotnet build <path-to-sqlproj> \
	/p:NetCoreBuild=true \
	/p:NETCoreTargetsPath=<path-to-build-targets>
```

The one interesting part is the `/p:NETCoreTargetsPath` parameter, which points to the *SQL Database Projects* extension folder from the last section. On macOS this folder is usually `/Users/<username>/.azuredatastudio/extensions/microsoft.sql-database-projects-0.4.1/BuildDirectory`. This folder contains the ingredients which allows `msbuild` to cope with `.sqlproj` files.

The [original article](https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension-build-from-command-line?view=sql-server-ver15) describing this approach recommends copying the extension's `BuildDirectory` folder somewhere to make your scripts independent of the extension, which might be necessary for build servers where no ADS is present. However, this worked not for me. I got a very unspecific error which I was not able to resolve (resp. not willing to put much time into). Instead, I referenced the original `BuildDirectory` folder directly, which worked just fine.

You will find the resulting build output (the DACPAC file) in `./bin/Debug` relative to the `.sqlproj` file.

Some remarks:
- The `/p:NETCoreTargetsPath` parameter worked only if the given path is absolute.
- If you open existing DB projects in ADS, then ADS will modify the projects as described [here](https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension-build-from-command-line?view=sql-server-ver15). They say that the projects will continue to work in SSDT. However, I've not checked this yet.
- If your existing DB project contains pre- or post-deployment scripts, then you will have to replace all backslashes in paths with slashes. The build will fail otherwise.

## Prepare a local MSSQL instance

If we want to deploy the DACPAC to a local running MSSQL instance, we have to create one first with the help of docker. Feel free to skip this section if you've done this before.

Install **[Docker Desktop for Mac](https://hub.docker.com/editions/community/docker-ce-desktop-mac).**

Then pull the docker image for MSSQL server:
```bash
docker pull mcr.microsoft.com/mssql/server:2019-latest
```

After that create a container with a data volume mounted to a host folder:
```bash
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<strong-pwd>' \
	-p 1433:1433 \
	--mount type=bind,source=$(pwd)/data,target=/var/opt/mssql/data \
	--name hi-mssql -h hi-mssql \
	-d mcr.microsoft.com/mssql/server:2019-latest
```

The data volume ensures that DB files and transaction logs survive a container shutdown. Note that in the specific case of the MSSQL docker image, binding host volumes with the `-v` parameter did not work.

Ad-hoc queries can be executed this way:
```bash
docker exec -it hi-mssql /opt/mssql-tools/bin/sqlcmd \
	-S localhost -U sa -P "<strong-pwd>" \
	-Q "SELECT 'Hello from SQL Server ' + CAST(SERVERPROPERTY('Edition') AS VARCHAR) + '!'"
```

If in any case, MSSQL is not starting up correctly, I recommend peeking into the logs. Often MSSQL is just rejecting a not strong enough password. You might find it handy to observe live SQL server log output with this command (in a dedicated terminal):
```bash
docker logs hi-mssql -f
```

Let's close this section with [a good entry point for official documentation around docker and MSSQL](https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15&pivots=cs1-bash).

## Deploy a DACPAC

As a prerequisite for deploying DACPACs from the terminal we need to **[install the `sqlpackage` tool](https://docs.microsoft.com/en-us/sql/tools/sqlpackage-download?view=sql-server-ver15)** first.

Assuming that we have moved the downloaded folder to `~/sqlpackage`, make sure the command file is executable:
```bash
chmod +x ~/sqlpackage/sqlpackage
```

Since `sqlpackage` is not a known app to macOS, we have to completely turn off macOS's security policy right before the first time we run it, and enable it again afterwards:
```bash
sudo spctl --disable-master
sqlpackage
sudo spctl --enable-master
```

**It is NOT recommended to leave `spctl` disabled!** Once re-enabled `spctl` will no longer complain when we execute `sqlpackage` again. Remember to repeat these steps if you've updated the [SQL Database Projects extension](https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension?view=sql-server-ver15).

Finally, let's extend the `PATH` variable by adding the following line to our `~/.zshrc`:
```bash
path+=~/sqlpackage
```

Reload the shell. Now we are ready to deploy a DACPAC!

The general parameterization of `sqlpackage` goes something like this:
```bash
sqlpackage /Action:Publish /SourceFile:<dacpath-path> /Profile:<publish-profile-path> 
```

The publish profile specifies the target DB server and various deployment options. We can pass most of those as dedicated parameters to `sqlpackage`. However, using a publish profile is more convenient most of the time as the configuration possibilities are quite overwhelming.

Sometimes you're only interested in the migration script `sqlpackage` derives from the DACPAC and the target database. In that case, change the `Action` parameter to `Script`.

Parameters and a publish profile also can be used together complementing each other. That is handy, if you don't want to have the credentials in the publish profile (which gets normally committed into source control). I've found the following invocation useful:
```bash
sqlpackage /Action:Publish \
    /SourceFile:<path-to-dacpac> \
    /TargetServerName:localhost \
    /TargetUser:sa \
    /TargetPassword:"<sa-pwd>" \
    /TargetDatabaseName:<db-name> \
    /Profile:<path-to-publish-profile>
```

For more details about `sqlpackage` have a look at the [documentation](https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-ver15).

## Conclusion

Thanks to the latest cross-platform tooling from Microsoft we are now able to develop, build, and deploy DACPACs to MSSQL databases on macOS (and Linux) without having to rely on a virtual machine running Windows.

I hope you enjoyed this post. Any feedback welcome!