Develop, Build, and Deploy Microsoft SQL Databases on macOS

Photo by Rodion Kutsaev on Unsplash
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) is a cross-platform SQL Server Management Studio (SSMS) replacement and provides a similar set of features. Its recent SQL Database Projects extension turns it into a full IDE for MSSQL database projects including build and deployment capabilities.

Download and install ADS. 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 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:

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 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. 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.

Then pull the docker image for MSSQL server:

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

After that create a container with a data volume mounted to a host folder:

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:

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):

docker logs hi-mssql -f

Let's close this section with a good entry point for official documentation around docker and MSSQL.

Deploy a DACPAC

As a prerequisite for deploying DACPACs from the terminal we need to install the sqlpackage tool first.

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

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:

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.

Finally, let's extend the PATH variable by adding the following line to our ~/.zshrc:

path+=~/sqlpackage

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

The general parameterization of sqlpackage goes something like this:

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:

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.

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!

Comments

[hastiAu]

Data-tire application wizard on azure data studio is instance of sqlpackage.? and with it I can transfer my database on server?