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

Question

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:

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?

Twinflower

Pretty sure that's not going to work. Best you can likely do to avoid that is extract a dacpac and compare against that instead of the live DB. You need something stable for the comparison. – Ormazd

This happens to me a lot in VS2019 even when there aren't any real database design changes. It feels like Microsoft has frozen development on SSDT since VS2017, including bug-fixes. – Trichotomy

Answer 1

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.

Fluviomarine

Hmmm sound like that might work if your changing the project and then attempting to deploy it to a database, but what about the opposite, what if you make changes to the database and want to pull those down into the project? – Twinflower

You could delete the project and re-create it from the database. 2 ways come to mind to create the project: 1) If you can access the DB from your dev machine, in VS SQL Server Object Explorer right click on DB, select Create New Project; 2) Create a DACPAC from the database then, in VS Solution Explorer create a new empty SQL Server Database Project. Once the project is created right click the project node and select Import > Data-tier Application. I use this second method a couple of times a year to ensure the project stays in sync with the production database. – Fluviomarine

Now that's clever, yes I guess you could do that. Create a DACPAC, then update you main DACPAC from that. Would be painful unless I scripted it out though... – Twinflower

@DavidRogers: I assume by "main DACPAC" you mean the SQL Project in Visual Studio. If you're willing to take a leap of faith and delete your existing project then recreate it from scratch it's not painful. Importing the DACPAC into an empty SQL Project will recreate the standard folder structure (eg folders for each schema, and for Scripts, Security and Snapshots, with sub-folders under each schema for Stored Procedures, Tables, Views, etc). And it will then create SQL scripts under each folder for the appropriate objects, one script per object (eg each table will have a different script). – Fluviomarine

Answer 2

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.

Trichotomy

What confused me about this what that I was comparing an Azure DB (my source) to my db project (my target) , and the message that was coming up was that the SOURCE schema drifted. Rebuilding the TARGET (my db project) solved my problem (even though there were no errors) – Cambria

@Cambria you should make your comment an answer. I ran into this bug when trying to populate a new project, and after reading your comment built the empty project first and that helped. – Rife

Answer 3

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.

Trillion