Are Migration Scripts right for my company?

xsinc.msanders

Hi there,
When utilizing SQL Source Control on a company-wide scale roughly two years ago, we chose not to use SQL Migration scripts. Instead, we build a simple project to our TFS Build controller that has sql files which contain our migration scripts. We then have the build controller execute said files.

Why did we do this? We opted for having the migration scripts executed locally by the build controller because we had some scripts that needed to be ran before comparison deployment scripts were run, and others that needed to be ran after. I'd like to move us to using SSC Migration Scripts in the future, but we need the ability to run migration scripts before the deployment script that SQLCompare generates is executed. Is this possible?

Sergio R

Hi,

I am afraid that Migration Scripts in SQL Source Control only act at the deployment stage.

SQL Source Control 5, which is going to be released in the next few weeks, will feature a new approach to migrations, however they will still only act at the deployment stage.

For more information regarding migrations in SQL Source Control 5 please read the following:
https://documentation.red-gate.com/disp ... ripts+work

Thank you,

xsinc.msanders

Hi Sergio,
It looks like the first line of the documentation you linked has what I may need:

When you add a migration script, it's added to a specific point in the deployment script. This point is defined in the deployment order file (DeploymentOrder.json).

If I can somehow modify this json file, I may be able to order some migration scripts to run before, and some after. Would you agree with this?

Sergio R

Hi,

DeploymentOrder.json controls the order migrations run in during deployment and is primarily for branching + merging, so that when I merge two branches with migrations I can choose which one should be deployed first (so it only changes the order of the migration scripts inside the deployment phase).

If you need those pre and post deployment scripts to run every time you deploy then I am afraid SQL Source Control will not be able to help you.

If you only need each script to run once then it should work. Let's assume that you start from having everything in your repository deployed. If you commit a migration script then commit some schema changes, when you come to deploy then we'll run the migration script followed by the other schema changes. If you were to commit another migration after the schema changes, then it won't necessarily run at the end of the deployment - it will only run after the schema changes which it depends on.

Thank you,