Is it possible to have multiple pre/post deployment scripts?

TShiner

My team utilizes SQL Source Control and Azure DevOps Pipelines for deploying database changes throughout our environments.  We recently have a scenario where we would like to use pre-deployment scripts but would prefer to have many scripts over a single script due to the length/purpose of the scripts.  Is it possible to have more than one pre-deployment script defined?  Or is there a better practice/tool for running a directory of scripts?  Thank you!

Peter_Laws

Hello TShiner,

The most immediate answer is unfortunately no, taken from the below documentation, "A SQL Source Control project can contain one Pre-Deployment script and one Post-Deployment script." 

Please see https://documentation.red-gate.com/soc/common-tasks/working-with-pre-post-deployment-scripts

If you want to keep the current tooling, you might be able to extend the longevity with some guard clauses in your scripts to define individual behaviour per environment.

I can't clearly tell if you mean multiple development environments, or rather if you mean promoting changes upstream. However if it were the former you would have multiple Source Control project and as a consequence multiple pre & post deployment scripts as a result. 

Assuming it is the latter, SQL Change Automation, or our latest tooling available in Flyway Desktop may suit you better; Flyway Desktop is closer to the existing SQL Source Control interface you are familiar with.

TShiner

Thank you for the quick response!  From reviewing the documentation I was coming to the same conclusion.  

To clarify, this is multiple environments:  DEV > TEST > UAT > PROD

With only making schema changes the SQL Source Control project has worked fantastically.  It's only with a new requirement to run scripts in the database that we have seen this monkey wrench.  Two tentative solutions I am looking at are to have the single pre-/post-deployment scripts call external scripts (though that seems too uncertain to work) and to add another task in our deployment pipeline to read all scripts in a directory and execute them (most likely to happen).

I have started to review the SQL Change Automation project type as that definitely handles multiple deployment scripts, though when working through a proof of concept it didn't look to keep the full DDL schema in the source code (only what changed since the previous commit / migration script generation), which makes that undesirable.

I have not checked out Flyway Desktop, but I will take time to review that too.

Thank you for!