Database Lifecycle Management - Dacpacs

I came across an excellent article recently @ Simple-Talk discussing Dacpacs, working with them, structures, etc. in quite a bit of detail for a short article. This post discuses benefits, drawbacks, and potential areas of concern for working with dacpacs for your database management.

https://www.simple-talk.com/sql/database-delivery/microsoft-and-database-lifecycle-management-dlm-the-dacpac/

The PowerShell scripts are well worth checking out and there are several interesting calls for SQLPackage along the way. I appreciated his findings with some of the issues - not always seeing changes that should be detected. I suspect that there's a setting not configured properly there, but couldn't say for sure. I know that I haven't hit any issues when using SQLPackage and dacpacs for normal DB development of the sort discovered at the end of the article.

The conclusion is spot on, whatever solution you use - check your scripts, test, and verify before just putting the solution into production.

Pre-deploy SQL Scripts before checking for changes

I just came across a post from Ed Elliot (b | t ) about running Scripts before the SQL Package process runs. Sort of a "Pre" Pre-deploy script.  The concept is simple enough, but it's something people have wanted for a while.

Here's the main article.

The general idea is that you add a SQL Script to your project with:
Build Action = "None"
Copy To Directory = "Copy Always" (or "Copy if Newer")

When you set up your publish actions, look for this script first, run it in PowerShell, SQLCMD, or whatever other option you might have, then run your SQLPackage commands as normal. It will run through whatever scripts you want to run first, then compare against the modified database to bring it up to date.

The idea is pretty clever, though I haven't come across a lot of need for it when trying to deploy DB changes. I'm not quite comfortable with a script running to change the state of the database to something unknown prior to running the script to bring it in line with version control. I suppose it has some use-cases, though, as I've come across this request several times on Stack Overflow.