none
Schema Compare/Update error: "Target schema drift detected" RRS feed

  • Question

  • I'm using schema compare (VS 2010) to keep two DB schemas (2012) in sync and I'm seeing the error "Target schema drift detected" when I try to update the target. This only happens occasionally though--most of the time it works fine.

    I don't know why I'm seeing this error since I'm trying to run Update right after running Compare and I know that nothing has changed on the target. Source is local, target is a remote machine.

    The current error is occuring with one schema difference between the two: a sproc has been changed on the source and I'm trying to update it on the target

    BTW -- I'm not using DACPAC and haven't registered DB apps


    - cawood

     blog |  twitter

    Thursday, June 14, 2012 11:51 PM

Answers

  • Hi all,

    At this stage we are still unable to reproduce this problem here. I'd be happy to investigate this issue further if anyone is still experiencing this problem.  Please follow up here, or to me directly (tgedge at microsoft dot com) if you need further assistance. If it is at all possible for you to share the projects, the DACPACs of the databases causing the problem and ETL trace files with me directly, that would be extremely helpful.

    Tony.

    • Proposed as answer by Tony Gedge Friday, August 10, 2012 5:29 PM
    • Marked as answer by Janet Yeilding Friday, August 10, 2012 8:25 PM
    Friday, August 10, 2012 5:26 PM

All replies

  • Hi,

    Apologies for the inconvenience.  Could you confirm that you are using the released version of SSDT by reporting the version you have installed (reported by Help > About MS VS).  The number below is the released version.

    A few questions to help pin this down:

    • Have you seen any pattern at all regarding what you're doing with SSDT or the databases concerned when you have encountered this problem? 
    • Can you confirm that you are comparing against a target database when you see drift reported? 
    • How often is this occurring?  Would it be practical for you to enable tracing/logging so we can gather some additional information that will help pinpoint what is happening?
    • Is your source a project that has a refactor log?

    Thanks,
    Bill

    Monday, June 18, 2012 7:44 PM
  • thanks Bill

    Yes, it's the same: 10.3.20225.0

    • Have you seen any pattern at all regarding what you're doing with SSDT or the databases concerned when you have encountered this problem?

    - I haven't noticed a pattern, but I'm watching for one. I just did a relatively complex update and didn't run into the issue. I haven't seen the issue since the day I posted the question.

    • Can you confirm that you are comparing against a target database when you see drift reported?

    - Yes 

    • How often is this occurring?  Would it be practical for you to enable tracing/logging so we can gather some additional information that will help pinpoint what is happening?

    - I'll try the next time I see the issue.

    • Is your source a project that has a refactor log?

    - No

    I'm wondering if network issues could cause the problem.


    - cawood

     blog |  twitter

    Monday, June 25, 2012 4:54 AM
  • Hi,

    Thanks for the additional info.  It's difficult to pin down without a repro so if you can get a trace that will be useful. 

    Thanks again,
    Bill 

    Monday, June 25, 2012 2:21 PM
  • I just started seeing this problem as well. It has been about 2 weeks since I last used the schema compare to update a remote database with my local schema. I have the the same version in the post by Bill Gibson. I am not seeing any network related issues. I have tried multiple times and the schema compare always works but generating a script to update the target database always produces the "target schema drift detected" error (at least for the last few hours). I restarted VS with no luck. 

    I am using Entity Framework 4.3.1 with code first to generate the schema.

    The target database has always been updated from the source.

    Wednesday, June 27, 2012 3:06 AM
  • I'm seeing the same error - every time. I can't even generate the update script.

    I'm using VS 2012 (Data Tools v11.1.20425.00) to compare databases on two SQL express instances (source is 10.50.1600.1 and target is 10.50.2789.0).

    Monday, July 2, 2012 8:18 PM
  • I'm seeing the same error - every time. I can't even generate the update script.

    I'm using VS 2012 (Data Tools v11.1.20425.00) to compare databases on two SQL express instances (source is 10.50.1600.1 and target is 10.50.2789.0).

    i take it back. i closed the comparison, created a new one and it worked fine.

    Monday, July 2, 2012 8:24 PM
  • Hi, folks.  This is going to be difficult to track down as its clearly an intermittent problem and one that we have not see here.  If anyone sees this and is able to get a trace that includes the problem then we may be able to make some more progress.

    Thanks for your help! 

    Friday, July 13, 2012 10:58 PM
  • And to enable tracing, enter the following at a command prompt

    logman create trace -n MyTrace -p
    "Microsoft-SQLServerDataTools" 0x800 -o
    "%LOCALAPPDATA%\SSDTDebug.etl" -ets

    Then reproduce the problem.

    Then stop tracing:

    logman stop MyTrace -ets

    The resulting ETL file will be located at %LOCALAPPDATA%\SSDTDebug.etl and can be navigated to using Windows Explorer.

    Thanks!

    Friday, July 13, 2012 11:20 PM
  • Hi all,

    At this stage we are still unable to reproduce this problem here. I'd be happy to investigate this issue further if anyone is still experiencing this problem.  Please follow up here, or to me directly (tgedge at microsoft dot com) if you need further assistance. If it is at all possible for you to share the projects, the DACPACs of the databases causing the problem and ETL trace files with me directly, that would be extremely helpful.

    Tony.

    • Proposed as answer by Tony Gedge Friday, August 10, 2012 5:29 PM
    • Marked as answer by Janet Yeilding Friday, August 10, 2012 8:25 PM
    Friday, August 10, 2012 5:26 PM
  • Thanks for being thorough. I haven't been able to reliably reproduce the issue--I've only seen it once in the last month and re-running the operation worked. One thing that seems consistent is that I've seen the issue occur when comparing a target DB on an underpowered remote machine. For example, before Azure offered VMs, I used Amazon (AWS EC2) and their free instances only have about 650MB of RAM (yes, that's right). By comparison, Azure has about 1.7GB of RAM for their small VMs.

    - cawood

     blog |  twitter

    Friday, August 10, 2012 6:21 PM
  • Hi i am using SQL Server Data Tools 10.3.20905.0 (September Release)
    Does anyone know how to ignore schema drift
    i keep getting "Target schema drift detected against my sql server database"
    It's a production server, restarting the database does not work, restarting the server also does not work

    I attach here for Bill Gibson MSFT
    http://files.fluidweb.co.id/SSDTDebug.zip

    Thank u :)

    Best regards,
    Nelsen

    Wednesday, October 3, 2012 1:18 PM
  • Hi Nelsen,

    Thanks for the files, we'll take a look and see if we can track this down.

    Cheers, Bill

    Wednesday, October 3, 2012 3:13 PM
  • Hi Nelsen,

    Thanks for the ETL trace file you've provided. Unfortunately, the September Release now has two separate tracing streams, called "Microsoft-SQLServerDataTools" and "Microsoft-SQLServerDataToolsVS".  Would you be able to repeat the problem with both of these tracing streams enabled and send me the two trace files?

    You'll need to do the following two commands to create the trace streams:

    logman create trace -n MyTrace -p
    "Microsoft-SQLServerDataTools" 0x800 -o
    "%LOCALAPPDATA%\SSDTDebug.etl" -ets

    logman create trace -n MyTraceVS -p
    "Microsoft-SQLServerDataToolsVS" 0x800 -o
    "%LOCALAPPDATA%\SSDTDebugVS.etl" -ets

    Then reproduce the problem.

    Then stop tracing with the following two commands:

    logman stop MyTrace -ets

    logman stop MyTraceVS -ets


    You can send them to myself directly via email (tgedge[at]microsoft.com).

    Tony.

    Wednesday, October 3, 2012 6:17 PM
  • Thank you very much for your help.
    I have sent the files to your mail.
    You can also download it here

    http://files.fluidweb.co.id/SSDTDebug-Nelsen-4Oct.zip

    Best regards,
    Nelsen

    Thursday, October 4, 2012 2:51 AM
  • Hi Nelsen,

    We are investigating the trace files you have provided.  Unfortunately, these are not as helpful as we had hoped.

    Could you do the following and send the files to us:

    1) Generate a DACPAC from the problem (target) database immediately before the comparison and immediately after the drift is reported,

    2) Generate a DACPAC from the source database you are trying to update,

    3) Save the schema comparison file (SCMP) immediately before you perform the update,

    4) Describe the source and target database versions.

    You can use the Power Tools for the September Release to generate a DACPAC from a connected database, or you can use SqlPackage.exe from the command line.

    Details on the Power Tools is available here (http://blogs.msdn.com/b/ssdt/archive/2012/09/14/new-ssdt-power-tools-now-for-both-visual-studio-2010-and-visual-studio-2012.aspx).

    Tony.

    Wednesday, October 10, 2012 6:48 PM
  • Hi there. I'm having the same problem as reported by the OP, except that I get it every time I'm trying to update the DB on the remote server while having no problems with local DB updates. Here are the files requested for the analysis: https://skydrive.live.com/redir?resid=DFAF01C9CB4CA871!383&authkey=!AL9Q390rG2PzLjM. Hope you'll make something out of it :)


    • Edited by kavalczuk Monday, October 15, 2012 11:37 AM
    Monday, October 15, 2012 11:32 AM
  • Hi Kavalszuk,

    Thanks for providing the files.  Could you also tell me what SQL server version each of the source and target databases are?

    I'm investigating the files that you've provided.  An initial scan of the files doesn't show any differences between the two target databases, so I will have to look deeper to find the root cause.

    While comparing the databases, I noticed that the "seven set" options are different between the source and target databases.  According to your SCMP file, you have database options ignored on your schema comparison, however, are you expecting this difference? In particular, the setting of ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, and QUOTED_IDENTIFIER can affect creating and changing indexes on computed columns and indexed views.  I don't expect that this difference should provoke the behavior you are seeing, however I did want to bring this to your attention in case you were not aware of the difference.

    Tony.


    Tony.

    Friday, October 19, 2012 6:00 AM
  • Hi Tony. The SQL servers are now both 2012; I've experienced the same behavior on 2008 R2 as well. Regarding the database options, I'm well aware of that, thanks.
    Friday, October 19, 2012 11:08 AM
  • Hi Kavalszuk,

    I've looked into the issue some more and I'd like to send you a set of SQL queries to run and send me the results.  These queries will help me determine why the drift detection code is flagging drift when we think it should not be.

    Can you forward your email address to me at (tgedge[at]microsoft.com) so I can send on the queries and information on how to use them?

    Thanks,

    Tony.


    Tony.

    Wednesday, October 31, 2012 9:16 PM
  • Hi Tony,

    Did that just now.

    Thursday, November 1, 2012 9:40 AM
  • I’ve looked at this issue some more with the additional details Viktar provided. Many thanks to Viktar for allowing me to examine his database instance directly.

    In Viktar’s case, Schema Compare is correctly reporting drift in the “sa” user. This drift was occurring frequently (e.g. every few minutes or less) while an EF-based application was running and accessing the DB as the “sa” user. When the EF-based application isn’t running, the “sa” user remained unmodified. Viktar, I suggest you ask on the EF forum as to why the framework might be making changes to the “sa” user.

    With this finding it would be useful if others who experience this problem use the following query to look for drift in the “sa” user.  Run the query immediately before running schema compare, immediately before applying updates, and afterwards also if you hit the issue:

                   select * from sys.server_principals where principal_id = 1

    If the modify_date value changes, then the “sa” user has been changed and the database has ‘drifted’.

    As the only evidence uncovered so far indicates that drift is correctly being reported (although it’s unclear what is causing it) we are going to mark this thread as answered.  If anyone encounters the problem please verify that no applications are running against the database and that there is no other activity against the database while using schema compare.  If further issues come to light that cannot be traced to EF and the SA user then we will happily re-open the discussion here, or with me directly (tgedge at microsoft dot com).

    Tony.


    Tony.

    • Proposed as answer by Tony Gedge Thursday, November 15, 2012 8:46 PM
    Thursday, November 15, 2012 8:46 PM
  • Hi Tony,

    I seem to have a similar problem here. When I publish the DB (without blocking on drift), no changes are done (nothing logged, no modification actions in the SQL script). Then when I try to publish again (with blocking on this time) the drift error shows up.

    I'm running SQL Server Data Tools 10.3.21208.0 against SQL Server 10.50.4266. My user is quite simple:

    CREATE USER [testuser] FOR LOGIN [testuser];
    

    The drift report doesn't seem to have any details on why a drift got flagged:

    <?xml version="1.0" encoding="utf-8"?>
    <DriftReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02">
      <Additions />
      <Removals />
      <Modifications>
        <Object Name="[testuser]" Parent="" Type="SqlUser" />
      </Modifications>
    </DriftReport>

    According to the "server_principals" and "database_principals" tables, this user and its login haven't gotten changed by anything else. The schema compare shows no differences.

    - Dan

    Thursday, January 24, 2013 8:53 PM
  • Hi Dan,

    The drift report isn't the same as the drift detection done internally.  Drift is tested at a database level and includes almost all objects in the database.  That is, a change to an object that is filtered out of the schema compare display will trigger the drift error you are seeing.  For most objects, it is based on the timestamps of the object.

    If I understand you correctly, you are using Publish and not Schema Compare to perform the database update.  Could you describe in detail the sequence of steps you are doing? It would also be helpful if you could:

    1) Generate a DACPAC from the problem (target) database immediately before the publish and immediately after the drift is reported,

    2) ZIP up the source project.

    If you could send both the above to me (tgedge at microsoft.com), I'll have a look and see if anything stands out.

    Tony.


    Tony.

    Thursday, January 24, 2013 11:51 PM
  • Hi All, 

    Though I am not sure of the exact solution, but in my case SQL Server Project's solution contained more than one database project ( VS 2012), When I just removed all unnecessary db projects, just alone project compared with source database, Comparision and updation of Target project completed successfully. 

    Hope it Helps! 

    HydTechie


    HydPhani

    Tuesday, July 2, 2013 10:51 AM
  • If this helps anyone else ... in our case, it just didn't like the change action that involved a delete of a stored procedure. We unchecked the delete and the rest of the compare then worked.

    So it sounds like there could be many different situations that can cause this drift.


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

    Wednesday, July 10, 2013 2:38 AM
  • Hi Dan,

    The drift report isn't the same as the drift detection done internally.  Drift is tested at a database level and includes almost all objects in the database.  That is, a change to an object that is filtered out of the schema compare display will trigger the drift error you are seeing.  For most objects, it is based on the timestamps of the object.

    If I understand you correctly, you are using Publish and not Schema Compare to perform the database update.  Could you describe in detail the sequence of steps you are doing? It would also be helpful if you could:

    1) Generate a DACPAC from the problem (target) database immediately before the publish and immediately after the drift is reported,

    2) ZIP up the source project.

    If you could send both the above to me (tgedge at microsoft.com), I'll have a look and see if anything stands out.

    Tony.


    Tony.

    Hi Tony and Dan,

    Did you get a resolution on this.  I have the same problem where users are being reported as modified in the drift report upon second publish when they have not changed at all (the first publish registered the DB a minute earlier).

    Thanks,

    Chris.

    Tuesday, January 7, 2014 8:13 AM
  • Hi Tony,

    In my case you were right, that the modify_date was changed. Actually it constantly changes for the sa user every second or so. Do you have any idea of what could be causing this?

    /Mark

    Monday, February 17, 2014 2:23 PM
  • Found the answer - and not the one I expected! Two IP's in China tried to brute force their way into my DB, and apparently each wrong attempt updated the modify_date. So - if anyone suddenly is experiencing schema drift and you have external access to the DB, then your firewall is a good place to start looking for the problem.
    Monday, February 17, 2014 4:31 PM
  • Found the answer - and not the one I expected! Two IP's in China tried to brute force their way into my DB, and apparently each wrong attempt updated the modify_date. So - if anyone suddenly is experiencing schema drift and you have external access to the DB, then your firewall is a good place to start looking for the problem.

    YOU ARE A GENIUS.  This is EXACTLY what was happening to me.  I ended up disabling "public" access to the SQL Server 1433 port by going to the advanced tab in the inbound rule properties, under interface types clicking "customize" button, and only selecting the "remote access" interface type, which would equate to a VPN client.  Now I connect to it for development purposes through VPN instead of having it so naively open to anybody with an internet connection and malicious intentions!!

    Seriously, THANK YOU.


    Saturday, August 9, 2014 9:30 PM
  • Glad I could help :)
    Monday, August 11, 2014 6:47 AM