CODEWITHSUNDEEP
visual-studiovisual-studio-2017sql-server-data-toolsschema-compare
David Rogers
David Rogers

Reputation: 2633

Ignore "Source schema drift detected" Error, Continue With Update

I have a SQL Project in Visual Studio 2017 and I'm using SSDT to update my project from a SQL Database, looks like this:

enter image description here

Often the database I'm using as my source is changing(often in small and irrelevant ways), when this occurs I cannot update my project, I get the "commonly" "experienced":

Source schema drift detected. Press Compare to refresh.

However in my case this error is legitimate, I just don't care, I would like to update my target anyways. Is there a way to ignore this message and have Visual Studio update my project irregardless of the schema drift?

Upvotes: 10

Views: 9108

Answers (3)

Abdusalam Ben Haj
Abdusalam Ben Haj

Reputation: 5423

I faced the same issue because my database is accessible online and It had 100s of failed login attempts per minute. I had to adjust my firewall to prevent unwanted ips from trying to connect. The compare worked perfectly after that.

Upvotes: 0

Dai
Dai

Reputation: 154995

Despite Microsoft claiming this issue was fixed in Visual Studio 2019, 16.6 back in May 2020, I was able to consistently reproduce the issue on my computer today - so I did some digging through Visual Studio 2019's SSDT files (yay for unobfuscated assemblies) after I was able to reliably reproduce the "Source schema drift detected. Press Compare to refresh." message and I believe I found the issue:

  • The decision to abort the comparison is made by Microsoft.Data.Tools.Schema.Utilities.Sql.SchemaCompare.SchemaCompareController::VerifyParticipantsNotDrifted()

    • The SchemaCompareController class is in this assembly:
      • Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\Microsoft.Data.Tools.Schema.Sql.dll
    • This method returns a bool value if it thinks either source or destination is "drifted" when the method is invoked.

  • It determines this by calling ISchemaCompareParticipant::IsStale() on both the Source and Target objects.

    • In my case, my Source was my *.sqlproj project. So SSDT was thinking my project was "stale" - but why?

  • After some more digging, I saw that SSDT uses the same logic for comparing *.sqlproj as it does with *.dacpac files.

    • Which makes sense: as when you do a Schema Comparison it actually builds your *.sqlproj to a *.dacpac file in your MyProject\bin\Debug and MyProject\bin\Release directories.
    • And the logic for determining if a .dacpac file is "stale" is to check 2 things:
      1. If the Schema Comparison Configuration has changed
        • In our case, it most definitely hasn't, so we can eliminate this.
      2. If the .dacpac file itself has an old Build Date - or if the .dacpac doesn't exist at all.
        • This logic is in SchemaCompareParticipantForDacpac.BuildArtifactStale()

  • The BuildArtifactStale() method just grabs the .dacpac's FileInfo.CreationTimeUtc, LastWriteTimeUtc, and Length properties and compares those with a snapshot of the same file's properties taken when the Schema Comparison was started.

  • So something is modifying the bin\Debug\MyProject.dacpac file between the comparison-start and when it performs the generate-scripts (or update-target) step - or failing to produce an up-to-date .dacpac output.

  • I'll admit that I wasn't able to find out what thing was causing my bin\Debug\MyProject.dacpac file to not be rebuilt correctly, but I did see some new build warnings (from Static Analysis). After addressing those build warnings and deleting both the bin\Debug\MyProject.dacpac and bin\Release\MyProject.dacpac files, and then re-running the Schema Comparison the schema drift errors stopped appearing.

My hunch is that SSDT's static analysis process interferes with the dacpac build process or otherwise invalidates the build, preventing the final .dacpac file from being generated, even if it builds fine.

After reverting my changes to restore the static analysis warnings I wasn't able to reproduce the issue (ARGH!) so I'm guessing it's a race-condition in the build process or static analysis part.

TL;DR:

Do these 3 things (you should not need to restart Visual Studio or reload the SSDT project)

  1. Ensure you don't have any Build Errors or Warnings, especially not any from SSDT's Static Analysis tools.
  2. Delete your bin\Debug\YourProject.dacpac and bin\Release\YourProject.dapac files.
  3. Do a project rebuild (both Debug and Release) and check the Last-Modified timestamp on the .dacpac file is unchanged between the comparison completing and you clicking Generate Scripts or Publish.
    • If you do see the Last-Modified time change, see if you can figure out what caused it and let us know in this QA so we can give MS a reliable bug report.

Upvotes: 6

Simon Elms
Simon Elms

Reputation: 19628

The way I've got around this is to do a SSDT project snapshot before updating the project, which will be saved as a DACPAC. After updating the project I do a schema compare between the SSDT project and the DACPAC file. That picks up only the changes that have been made to the project. Then, rather than clicking the Update button on the schema compare, which won't work if the target is a DACPAC, I click the Generate Script button. I can then run the generated script against the target database.

NOTE: The generated script will use the name of the DACPAC file as the database name at the head of the file. Change it to the correct name of the database before running the script.

By the way, If you forget to take a snapshot of the SSDT project before making changes (which I do occasionally) it's not a problem as long as the SSDT project is in source control. Just commit your changes then check out the last commit before your changes before taking the project snapshot. Then check out your latest commit again and do the schema compare between the changed SSDT project and the saved snapshot DACPAC.

Upvotes: 2

Related Questions