Can Flyway Compare Server Level Objects via T-SQL Scripts for Change Report

biztalk

We are using SQL Server 2019 and are in the process of implementing a ci/cd process for our database using Flyway.  In addition to database level objects, we are considering also including server level objects which we would add to a separate flyway project but stored within the same devops git repo as the db project.  For the database, Flyway can generate a change report using a build db, the baseline script and the migration history stored in the flyway_schema_history table.  For server level objects (like sql agent jobs and linked servers) that we script out into an initial baseline script and then make changes using migration scripts, is there a way to generate some sort of change report so that we can see what is going to change in production?

DanC

What is the rationale for including the server level objects in a separate flyway project? Flyway won't auto-generate migration scripts for these objects, so you'll have to write the migration scripts yourselves. There are two approaches you can take.

1) Just add normal versioned migrations (V prefix) that represent changes to your server objects.

2) Use repeatable migrations (R prefix), one for each server object.

The advantage of option 2 is that as the repeatable migration changes on the file system, git will build up an object level history which is similar to what exists in the schema model folder for database-level objects.

As Flyway's comparison engine doesn't support server objects, you won't see these changes in the changes report, so if you want to alert a DBA or release manager to changes to these objects, one approach is to build custom code analysis rules that spot the SQL commands that alter these server level objects (eg, "EXEC sp_update_job "). If these appear as warnings in the code analysis report, a DBA can navigate to the part of the dry run script (deployment script) that describes the specific change.