0

I'm trying to update a SQL Server project in Visual Studio 2019 by using the SSDT schema comparison. My source is a running database server, the destination is the VS SQL Server project.

When the comparison is done and I click "Update", I get the message

Source schema drift detected. Press Compare to refresh the comparison

No matter how many times I refresh the comparison, I always get the same result.

I have tried various connection tweaks (read-only intent, asynchronous processing, multiple active result sets) in the hopes that I can make the comparison run faster and update the project before the drift happens, but to no avail. I have also tried reducing the types of objects included in the comparison, but have not been able to reduce it enough to prevent drift from being detected.

I think the biggest issue I have is that aside from the "schema drift detected" message, I feel like I'm shooting in the dark. By that I mean that I have no idea what is causing SSDT to detect drift, and therefore I can't work around it.

I tried running the SQL Profiler to capture what SSDT is doing so I could find where SSDT is detecting drift. However, I haven't been able to find any query that gives different results when run multiple times within a short period.

So in conclusion, my questions are:

  1. What does SSDT look at to determine when the database schema has drifted?
  2. How can I update my SQL Server project when it always detects schema drift?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nicedream
  • 21
  • 1
  • 3
  • 1
    If you don't name things like constraints, it's possible that if you've dropped or changed them they might have different names. And the _manual_ change detector may be set to ignore things like constraint names. (I don't use SSDT because I trust source control more, but when I used Redgate's compare tools they definitely had many options like ignoring differences in constraint names.) – Stuck at 1337 Nov 01 '22 at 15:26
  • When posting your question was it suggested for you to check [Ignore "Source schema drift detected" Error, Continue With Update](https://stackoverflow.com/questions/49908845/ignore-source-schema-drift-detected-error-continue-with-update)? Does that contain any helpful information? – AlwaysLearning Nov 01 '22 at 21:07
  • @AlwaysLearning: Yes, I saw that post, and it contains some good info about the dll and internal function calls used to detect drift. Unfortunately, I don't know what the code in those functions looks like, and I couldn't find any documentation on the web. Also, my situation is a bit different in that I am comparing against a live SQL db, not dacpac files. – nicedream Nov 02 '22 at 12:40
  • It has to be something like what @RhythmWasaLurker has suggested above. Or, you may have dynamic object creation as part of data loads or something along those lines. Hard to know. Assuming you don't have secondary mechanisms of deploying database code changes, it shouldn't be constantly changing. SSDT just uses the system tables to pull object definitions & then compares them. – Grant Fritchey Nov 03 '22 at 11:13

2 Answers2

0

I also struggled for months to find the cause of the same error. I was already thinking about flashing Windows 10 on my laptop. I won't list the dead ends anymore. In my final desperation, I copied the SQL Server database and VS project to another machine, and there the comparison worked without a bone. The suspicion arose that maybe the error is not in VS, but rather that my SQL server is confusing VS. I have a SQL Server 2012. I put the latest update on it (SP4) and wonder of wonders, compare and sync started working perfectly right away. Of course, now before every update I pray a little so that I don't encounter the "Source schema drift detected" message.

Attila M. Molnár
  • 1
0

I have been unsuccessfully fighting this annoying error for MANY SSDT versions.

Searching for it you will see multiple places where it is claimed to be fixed, WHICH IS FALSE, as it is happening right now with VS 2022 SSDT.

In my case, it ONLY happens when comparing against ONE out of the 5 database servers I regularly use the tool with.

The only workaround I have found that usually works is to REBOOT the destination database server (NOT just cycle the SQL Server Service) and then run the SSDT compare QUICKLY!

As the server that this happens on is an integration server running on a VM in my local network, I can bounce the server, but in other scenarios this would be a show-stopper.

IMO the most onerous things about this issue is that you cannot even generate the script to copy / paste into SSMS, which is how I often use the tool.

This issue has not been fixed for YEARS and is very intermittent, so I have no hope of seeing it actually fixed - I hope this workaround is helpful to someone.

Ken Granderson
  • 1