Final Addendum – use dbatools!

Addendum – There is a repo on GitHub which I wrote and recommend using for build/deployment of DACPACs. Read up on my last post about PoshSSDTBuildDeploy. But for a quick and dirty overview, read on…

When it comes to managing Dacpacs with automation (eg either by deploying or extracting Dacpacs from a database, amongst others), you have a couple of options available to you.

SQLPackage

SQLPackage is a command line utility that is shipped with SSDT, and it’s main focus is to manage Dacpacs. It’s certainly an in-depth tool: This is the MSDN site for all the parameters that are available for SqlPackage.exe.

I’ve written before about how to put this into a PowerShell module, however it’s basically a wrapper around the command line. Not that there is anything wrong with this approach, however if you want a more programmatic method then you may want to use DacFx.

DacFx

DacFx, or to give it it’s full title, the Data-tier Application Framework “is a component which provides application lifecycle services for database development and management for Microsoft SQL Server and Microsoft Azure SQL Databases“. Essentially, it is another method we can use to manage our Dacpacs. However instead of using the external process SQLPackage and initiating it via cmdline you can use C# or PowerShell to manage Dacpacs. In fact, SQLPackage uses the “Microsoft.SqlServer.Dac.dll” itself. You can verify this by going and deleting the dll and trying to run sqlpackage via command line…. or you can just take my word for it.

I’ve provided a sample below as to how you can deploy a dacpac using DacServices. The PowerShell script below takes 4 arguments (database server, database name, dacpac and profile) and deploys the changes to make to the database. It also creates a script of the database changes for reference. If you have multiple dacpacs, it would not take too much effort to loop through the dacpacs and find their relative profiles, if any exist. Note that the profile argument is entirely optional, and you can build up your own profile options from within the PowerShell script. The options I’ve added here are the “least risk” options; that is, no objects/permissions or data will be lost when deploying. It’s probably these three options that people are most afraid of when automating database deployments.

Also note that dacFX dll may be in a different location.

View the code on Gist.