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!
Top comments (1)
Data-tire application wizard on azure data studio is instance of sqlpackage.? and with it I can transfer my database on server?