Checksum warnings - "this migration has changed since it was deployed"

• 
  I found a bug a few weeks back that affected comparing white space. It was in the VS extension but maybe it is also in certain versions of the cmdlets. I was able to see this by using SQL compare to compare my VS project against the database. Make sure you have the setting in SQL compare to include white space. Might not be the issue but something to check.

  Monday April 11, 2019 13:33

  - 0 +
• 
  Thanks, @Monday.  Are you suggesting that I work with the:
  

  <SyncOptionIgnoreWhiteSpace>True</SyncOptionIgnoreWhiteSpace>

  setting in the sqlproj file?  Or something different?

  PeterDaniels April 11, 2019 14:44

  - 0 +
• 
  No I had mine set to true in the sqlproj file but the VS extension was not honoring it. To see this I used SQL compare but in SQL compare you need to uncheck ignore white space to see it.  Check out this thread here.

  Monday April 11, 2019 14:53

  - 0 +
• 
  I'm a bit confused, @Monday .  How did you resolve the issue?

  PeterDaniels April 11, 2019 21:02

  - 0 +
• 
  Before you figure out how to resolve it, make sure you know what the issue is. Did you compare your db to your project with sql compare and the only difference was white space? If so then you have the same issue that I had. (You are telling it to ignore white space and it is not) I fixed my issue by making the objects in the DB the same as the objects in the project by matching white space as a temp fix while they fixed the bug in the SCA code. Alternatively this may be a bug in the version of the cmdlet you have that was fixed or was just introduced?  To me, SCA thinks there is a difference for some reason, so the place to start is to find out what that difference is.

  Monday April 12, 2019 16:43

  - 0 +
• 
  @Monday, I think my issue is similar, but not the same.  Wondering how I could actually generate the script checksum of a migration file and compare to what I find in the __MigrationLog table.

  PeterDaniels April 16, 2019 15:50

  - 0 +
• 
  I think we need redgate to chime in with what algorithm they are using to produce the checksum. get-filehash  does not match any of the 7 it supports.
  
  

  Monday April 16, 2019 21:55

  - 0 +
• 
  Hi
  
  The checksum generated is a SHA256 representation of the migration script at the time of the build.  This value is used to determine whether a migration has been changed since it was last deployed.  In the case of a programmable object script, a different checksum will cause the migration to be redeployed.
  
  Many Thanks
  Eddie

  Eddie D April 29, 2019 13:21

  - 0 +
• 
  Thanks, Eddie.  I have been checking this by using powershell's Get-FileHash, and I'm not getting a match.  For example, in my __MigrationLog, one of my scripts (attached) shows a checksum of:
  E0F10404E1FDCBBEF178991783B426B5FCF4BA2A906DDF75CFB54DF025721EDB
  but using Get-FileHash -Algorithm SHA256 gives me:
  222A98897E8773854BED4584B70176BA74188E5EA1D28A8ADE8DB1E1A2B59941
  
  I've also checked my git history and none of my migrations that SCA was warning about has changed.

  PeterDaniels April 29, 2019 18:56

  - 0 +
• 
  None of the algorithms available in PS's GetFilehash match what I'm finding in the script_checksum column in __MigrationLog.

  PeterDaniels April 29, 2019 23:01

  - 0 +
• 
  @Eddie D Please see my previous post.  I am unable to match filehashes using PowerShell's Get-FileHash with the hash value in script_schecksum.  Can you tell me how I can get  matching hash, please?

  PeterDaniels May 16, 2019 16:11

  - 0 +
• 
  Hi Peter
  
  I was under the impression that as long as there was an entry in the migration_id column that matched the id shown in the migration itself (and possibly the checksum to make sure the script hasn't been modified since it was deployed), that it wouldn't attempt to be deployed.
  
  Doing a trial of this, the entry in the database I deployed to is the same except for the complete_dt column value and the deployed value (being 1 instead of 2 since it was deployed to the target, but imported to the dev database).
  
  If I then delete the _MigrationLog table doing the deployment fails because target  needs to be baselined.
  
  Recreating the _MigrationLog table with the same values as are in the development database (but changing it to deployed 1 instead of 2) - I used SQL Compare to recreate the schema for _MigratinoLog and Data Compare to create a script to deploy the data (which I then changed the 2 to a 1 for the migration) - after building again, it then worked as there was nothing to deploy.
  
  Many Thanks
  Eddie

  Eddie D June 14, 2019 14:12

  - 0 +

Add comment

Please sign in to leave a comment.