How to setup a reviewing migration (and rollback) gate for scripts in Azure DevOps?

[sturlath]

Maybe this is should not bee asked here but I´m doing it because of the work that seems to be going into management of deployment with things like bundles , Improve experience for managing migrations and Improve experience deploying databases created by Migrations so here goes. If you feel strongly against it just close it and I´ll ask it there.

My question is, how can I gate/review migration and rollback scripts in DevOps?

The out of the box functionality to review SQL changes with using AzureDevOps is lacking a lot. And also how can I automatically create rollback files for "thisMigration" agains "lastMigration"?

I managed to get a "poor mans version" working but I think this is something that is not going to cut it.

And maybe this will not get better and we need to move to something like Octopus Deploy?

Looking forward hearing your input.

[roji]

@sturlath that's an important question and we're definitely interested in any and all aspects of deploying EF Core applications and Devops. But I'm not sure we'll have much to contribute specifically to doing that in AzDo.

You seem to be looking for a workflow review step that is somehow automatically triggered for a build artifact (i.e. the SQL script). I'd even go a step further and fetch the SQL script from the last successful build, trigger the required vaildation only when there's as a difference, and present a diff view between the two for validation. How to achieve that is a very different question though :)

[sturlath]

Thanks @roji and yes you are correct about what I am looking for. I just haven´t found anything that fits it except this "poor mans" version I hacked together.

I'll find some place to ask this question and link to this issue so you (or anyone else) can benefit from it later.

And thanks for this input. this would be the dream scenario of course

I'd even go a step further and fetch the SQL script from the last successful build, trigger the required validation only when there's as a difference, and present a diff view between the two for validation.

but let's not hold our breath there :-) I´ll settle for just a small improvement from what we seem to have available in AzDo to day.

[roji]

Agreed. We'd definitely be interested in any guidelines/techniques you come across.

[sturlath]

I have created this feature request/question (hopefully) for the Azure Devops folks. Lets see what comes out of that.

And sorry but I snuck another question in my first one that was "how can I automatically create rollback files for "thisMigration" agains "lastMigration"?"

I know I can do something like dotnet ef migrations script -from (MigrationYouWant-1) -to MigrationYouWant but for a build scenario I don´t want to change the definition so I need to be able to have a command that never changes but creates a rollback script between the newest migration script and the one applied to the database. Or something like that...

Doesn't this make sense? I at least would like to be able to auto create rollback sql with out doing anything manual and then I would add them to the review process.

[roji]

That sounds similar to dotnet/efcore#21615 (planned for 7.0), though that's about dotnet ef database update rather than script generation. We should also support ~1 for SQL script generation. /cc @bricelam

[ajcvickers]

Note from triage: moving this to the docs repo to track investigation and documentation in the 7.0 timeframe.

[ErikEJ]

There is an example of doing this in the accepted reply here:
https://stackoverflow.com/questions/61240633/generate-sql-server-schema-change-script-on-azure-devops-pipeline
(for a .dacpac, but the principle is the same)

[sturlath]

Ah thanks @ErikEJ I didn´t find that. I'll give it a try and let you know how it went... images and all :-)

[sturlath]

@ErikEJ I just got to play with this and as far I can figure out its the same as what I did, just in YAML. It uses Powershell (instead of my CMD) to output the content (doesn´t look any better) that what's shown here below.

I watched Abels video at the bottom and thought I could create my own gate but reading this on gates with "a good gate will typically query an external system"...

So I can´t see that I can add any UI to show the content of an artifact. I´m going to ask this question the pipeline repo and see what feedback I get.