Restrict Objects getting dropped accidently

sasankj · October 15, 2018 4:08PM

Is there a way we can force the Deployment step to throw a warning or throw error if it finds any drop statements? This is to make sure there are no accidental "drops" / "deletes" going into production through pipeline .

way0utwest · October 15, 2018 4:54PM

You can use the abort flag from SQL Compare for high level warnings, which includes drops. It has other items, so you want to be careful and experiment in QA/staging to understand the things you do that might trigger this. It might be too gross for you.

What I'd probably do is look for the specific items I want to avoid and use a regex to scan the update.sql script. That way I'd get a note of problems. That being said, you need a way to get around this, or disable this at times, since I will almost guarantee that you will find exceptions over time.

StephanieHerr · October 15, 2018 7:57PM

There's more information on what Steve's talking about "AbortOnWarnings" at https://documentation.red-gate.com/sc13/using-the-command-line/switches-used-in-the-command-line. Data loss is a high warning.

There's information about using SQL Compare options in SQL Change Automation at https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployments/using-sql-compare-options-with-sql-change-automation-powershell-module. You can also put option parameters in the SQL Change Automation Build/Release extensions (TFS/VSTS/Octopus Deploy).

sasankj · October 16, 2018 6:29PM

I tried using it in my Deployment step, but it gave a warning saying

[warning]Cmdlet parameter SQLCompareOptions is not supported for SQL Change Automation projects. This option will be ignored.

[warning]Cmdlet parameter AbortOnWarningLevel is not supported for SQL Change Automation projects. This option will be ignored.

I am attaching a screen shot of my Deployment step and attaching release log.

Image: https://us.v-cdn.net/6029854/uploads/editor/pj/3mbxcgw4b0fu.jpg

sasankj · October 19, 2018 5:29PM

Did you get a chance to look into this?

way0utwest · October 19, 2018 6:28PM

Apologies, I haven't. Can I ask if this is a SQL Source Control project at the client or a SQL Change Automation in VS project?

sasankj · October 22, 2018 2:53PM

This is SQL Change Automation project in VS.

way0utwest · October 22, 2018 3:36PM

Ah, so in the release steps, there is no analysis of the code, just a review of which migration scripts need to be deployed. The scripts are already set and the parsing of changes is done in development to build those scripts. A pre-deployment to intermediate environments is what should be used to catch this type of issue.

I'm looking for something that would help here, but I think this is problematic from our view in that we do perform some drops for procedures in some scripting. Anything that's pre-SQL2016 can't handle CREATE OR ALTER, and the standard pattern we have is: if exists() drop, create.

I think that the solution I'd lean towards here is to fail builds that contain drops, not wait until the release stage. My initial thought is to capture a list of tables and store this in my db. This would essentially be a way of auditing the state of the db. If you maintained this in your existing prod (and qa/staging/UAT, etc), then you'd know what objects exist. After a build, I'd run a left join from this table to sys.objects, and if things were missing, I'd then fail the build or return an error to the user. This would help me detect drops.

You could easily expand this type of check into views, procs, functions, users, etc.

Restrict Objects getting dropped accidently

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk