SSMS Plugin verify success. Invoke-DatabaseBuild ERROR/FAIL

PeterDanielsCRB

SQL Change Automation 4.2.20168

SSMS Plugin.
I have seen cases where "verify" in the SSMS plugin works fine, but when I run:

Invoke-DatabaseBuild $ProjectFilePath -Verbose

I get errors.  It's causing me to not trust the UI's verify, and I have asked team members to use the powershell to "really" see if the build is working locally.

Wondering what "verify" is actually doing behind the scenes?

Thank you,

-Peter

Ivo_Miller

Verify is doing exactly the same checks as Invoke-DatabaseBuild. It is parsing the syntax of all the migrations and programmable objects and then running a deploy against the shadow database.

The only difference is that as a performance optimisation we don't drop the shadow database and perform the deployment from scratch when running Verify. We will drop the shadow database and run the full deployment if the existing state of the shadow database is considered to no longer match the project. This is typically if a migration is manually edited or deleted or a programmable object is manually deleted (i.e. from the Manage Migrations screen, rather than the Generate Migrations screen).

This logic works in the vast majority of cases and should always work if you only import objects to the project via the "Generate Migrations" screen. There are unfortunately a few scenarios where the shadow database invalidation logic doesn't work and we fail to delete the shadow database when we ought to.
This is solved by dropping the shadow database before verify (equivalent of "rebuild" in Visual Studio). We might make this option more obvious in the SSMS UI in the future.

For reference, these are the known scenarios which might cause spurious behaviour as a result of the shadow not being rebuilt:

• Changing the value of a SQLCMD variable does not currently result in the shadow being rebuilt
• Changing the values of SQLCMD variables does not cause programmable object scripts that consume those variables to redeploy
• Adding/removing/changing Pre & Post-Deployment scripts does not result in the shadow being rebuilt
• Adding a migration, or editing an existing deploy-on-change script, does does not result in the shadow being rebuilt <-- this is an intentional optimisation, but it can have some side effects. For example, 1. add a proc as a PO to a project, 2. apply it, 3. add a migration that calls the proc, 4. apply & verify (succeeds), 5. clean solution to drop shadow db, 6. verify (fails to call the proc in the migration)

Ivo_Miller

Hi.

Thank you very much for your feedback, and sorry to hear you have again had difficulties in this area.
We will take the feedback on board.
I can confirm that adding the "rebuild" functionality in SSMS is on our backlog, though I cannot currently offer any form of timescale for when we will add it.

All the best,

Ivo Miller

PeterDanielsCRB

Thank you, @Ivo_Miller.  I appreciate you taking the time to respond to my 3 forum posts from yesterday.

I think this might be a case where a dev either edited a migration and/or deleted one.  I think they also modified programmable objects in hand-crafted migrations rather than utilizing the programmable object functionality.  We are learning.   

I do agree that adding an option in the UI to insist on a full-rebuild/shadow-deletion would be helpful as we continue to trip over these situations.  Meanwhile, thanks for the workaround!

PeterDanielsCRB

Some additional info on this.
I discovered part of the problem - at least related to the difference in errors reported.  I think the UI's "verify" was smart enough to build the DB on the localhost (2019) instance where the dev DB resides.  The default instance for the

Invoke-DatabaseBuild

is a (localDB) instance, which is compatibility 130.  Using

-TemporaryDatabaseServer 'Data Source=localhost'

is more consistent with "verify" in SSMS.

PeterDanielsCRB

@Ivo_Miller.  We had a dev get bitten by this again today - "verify" in SSMS worked, but the build failed on our CI server when code was committed and pushed.  To be honest, I'm not sure if the dev had "generated migrations" before doing the "verify".  Regardless, I would love to see the team add a feature to help with what you brought up before:

"This is solved by dropping the shadow database before verify (equivalent of "rebuild" in Visual Studio). We might make this option more obvious in the SSMS UI in the future"

I think we could also use some general UI improvements that make the overall process more intuitive - step by step.  The tabs in SSMS are just sort of "there".