Stop deployment of SQL DACPAC if there is DROP statements

Question

Stop deployment of SQL DACPAC if there is DROP statements

ajupulickal

SSC Enthusiast

Points: 172
More actions
March 13, 2023 at 2:59 pm

#4160162

Hi all,
We have a SQL project to be deployed to an Azure SQL server. We are looking for s specific scenario where we can stop the ADO pipeline if we have DROP/ALTER statements in deployment scripts or DACPAC.?
Basically I am looking for a way to validate the DACPAC before deploying to production for some sensitive keywords. Is there a suggestion for the above issue .?
Aju

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

Deploy it at a test instance?

You can decompress a dacpac and view it's xml contents https://learn.microsoft.com/en-us/sql/relational-databases/data-tier-applications/unpack-a-dac-package?view=sql-server-ver16

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

If you have the database registered as a data tier application, there's a way to do a test run to generate a script that would be run. You could then search through the script for drops or alters (although, why look for alter, isn't that a good thing?). It's in the documentation here, scroll down to "View Upgrade Actions."

Another alternative is to look to a 3rd party tool that uses migrations instead of state based comparisons to avoid this problem entirely.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Does this mean that, ultimately, the new code will go essentially directly from the Developer to Prod without going through QA and UAT. If so, that's a really, really bad idea. If you have SOC2, SEC, or ISO audits, that process will cause you to fail your audit.

My sidebar is that, unless you're very, very careful, such tools and "Continuous Integration" can easily become one of the fastest ways to deploy bad/erroneous code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)