Is it possible to have multiple pre/post deployment scripts?
Best Answer
-
Peter_Laws Posts: 259 Silver 2
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.
Answers
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!