How to customize migration scripts in state-based approach

AdamYAdamY Posts: 55 Bronze 3
edited September 29, 2020 10:55PM in SQL Change Automation
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?
Tagged:
· Share on Twitter

Best Answer

  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    Answer ✓
    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.
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter

Answers

  • AdamYAdamY Posts: 55 Bronze 3
    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).
    · Share on Twitter
  • way0utwestway0utwest Posts: 312 Rose Gold 1
    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.
    Editor, SQLServerCentral
    · Share on Twitter
Sign In or Register to comment.