How to customize migration scripts in state-based approach

AdamY

I've been reading and watching many of the docs/videos about SCA as we try to use SCA for the first time. But it has been tough. The latest question is about using custom migration scripts with the state-based approach - instead of a SCA project, we continue to use SSC and generate deployment scripts with the SCA PowerShell cmdlets.

The videos/docs show how to generate the release artifacts in the state-based approach, but I don't see a way to customize the migration script like you can with the migration-based approach. Can anyone point me to another article or video that shows that? I've really tried but can't find one.

I have found a few articles by Phil Factor (e.g. this one) that use pre- and post-deployment scripts which means the script has to work every time it is run (idempotent). That feels like a hack - I like the "old" way of a custom script per change. But is Phil's approach the only solution?

David Atkinson

While you can use pre/post scripts to customize your SQL Source Control deployments to some extent, the most flexible approach is to adopt migrations-base deployments. Crucially this doesn't mean changing your development model away from using the state-based approach - you get to keep using SQL Source Control as before. Instead, you create an SCA project strictly for deployment purposes, and set the SQL Source Control project as its "source". From the SQL Change Automation project you periodically generate migration scripts that will eventually be used for deployment, which means of course that you can customize them as you go. These customized migration scripts become your version controlled deployment artifact. More detail can be found in this Redgate Hub article.

AdamY

Thank you, David. We have been playing with the hybrid approach for a few days, but it seems like duplicate effort. We make changes, check them in to Git via SSC. Then we have to do more steps in SCA to create scripts and check those in to Git. Two tools to manage and the object scripts end up in Git twice. Thus, we are currently leaning toward the migrations-based approach only and avoiding the use of SSC (which is a bummer because I love that tool).

way0utwest

It's not duplicate work, to me. This is a shift left of much of the script review from post VCS in a release, to a point in time where you load the SCA project from your changes in SSC (SQL Source Control, not SQLServerCentral). Once you have changes, you can load them into a migration script. This is checked into git twice, but git doesn't case. This also lets you then track what items are loaded.

I wouldn't load every change as a migration script, but only once I thought we were close to being done and needed customization. If you don't need customization, there is a need to generate and commit one script, but I'd think that's minor compared to the shift left capability of script review early on.