Post-update schema check failed - how to see whats different?

jerfer

I am in the process of migrating an existing build/deploy pipeline that leverages TeamCity/Octopus and corresponding Redgate DLM Automation templates to a new set of servers.  The configuration has been working for several years on existing servers and the new servers have slightly newer versions of redgate dlm automation and step templates.

During the "Redgate - Deploy from a Database Release" step within the octopus deploy process the database is deployed to the target database but the step fails with the text "Post-update schema check failed" because  the schema in the database release object and the target database are different.

Is there a way to see a log of what differences are being found causing the step to fail?  There are several /out: parameters specified in the various cmdlets that refer to .log files but these are deleted after the deployment fails.

Sergio R

The Post-Update schema check is a comparison between a scripts folder that's stored on your nuget package (States\Source) and the target DB . The nuget package can be opened with any zip compression utility, you can open it using the Windows shell if you change the extension to .zip.

The States\Source folder captures the state of the Source when the update script was created (at the Create Database Release step).

You can enhance the logging on this step by customizing the template and adding
$DebugPreference = 'Continue'



Since the deployment is not rolled back when the Post-Update fails, you can also troubleshoot this after it finishes using the SQL Compare command line, ideally you should use the SQL Compare command line executable that's on you DLM Automation installation folder on the SC subfolder.

If you expand your Octopus log you should see the exact syntax that was used by SQL Compare for the Post-Update schema check, you will need to adjust /scripts1 to point to where you unzipped States\Source and possibly to change the location of the log (/out)


You will get more information on the console by running SQL Compare interactively this way and you should get a table detailing what objects/static data SQL Compare finds different:

jerfer

Thanks Sergio I was able to start executing sqlcompare using the corresponding switches and package sources to see what database objects were the issue.

Adding the "IgnoreSquareBrackets" option in the SqlCompare call causes 2 of the previously flagged objects to no longer show up in the diff report.  What was the behavior with respect to this flag in DLM Automation 1.x as I do not see it mentioned in the 1.x docs but it does exist in the 2.x docs.