I have a SQL Database project that I have built our enterprise DB on. It has deployed several times on internal and AWS hosted SQL servers using SSDT's Schema Compare tool.
The problem when I post to Azure Hosted Win 2012 Server running SQL Ent 2012 sp2. It comes back with "Comparison complete. No differences detected."
I know this is wrong, because I can open Enterprise Manager and compare the schema to the SQL Project and see that there is a difference.
I found several articles talking about how the 2014 release broke the tool, but those were in version differences.
[Yes I did Google this. Stating because I am notorious in forgetting to do so. ] https://www.google.com/webhp?ie=utf-8&oe=utf-8#q=ssdt+data+compare+fail+to+detect+difference&start=10
Other things I have checked include making sure my DB account has unlimited access. I can connect with Management console. I can connect with local programs.
Last confirmation that there was a problem:
- I created an SP that has a single return of the number 1.
- It could have a return of nothing for the sake of testing.
- After I created the SP, I ran schema compare on all of my instances and all but the Azure server showed the variance.
Update
I have verified that this is explicitly with the server, in that now two different users on two different computers are having the same exact problem.
Best Answer
TL;DR
The query Visual Studio is executing is being blocked and the query times out.
Turns out I've managed to work this out for our situation: timing out due to blocking!
I ran a profiler trace with a filter on application name of Microsoft SQL Server Data Tools, Schema Compare to capture the Visual Studio schema compare events.
It would reach a certain query and then stop, when VS would claim that the comparison was complete.
Here's the batch of queries in question:
Running the query in SSMS would never finish executing on the offending server but would take less than a second on every other environment.
So I started to pull it apart and found that the queries that are reading from the following 3 system views were the problem:
sys.server_event_session_actions
sys.server_event_session_events
sys.server_event_session_fields
The first of these system views only contained 28 records (found by running a
Count(*)
), but wouldn't return the data unless I used the(NOLOCK)
hint... [b]locking problem!Finding out the head of the blocking chain was easy enough but it was not killable as it was an Extended Events session. There's a fix for this issue here: https://support.microsoft.com/en-gb/kb/2511963
But hopefully in your case you can just kill off the head of the chain nicely and return things to normal!
EDIT: Having now had a viable maintenance window for the affected SQL Server, a restart has cleared the SPID and now compares are working once more!
Also, here's a quick script I wrote to find the head of the blocking chain(s) that might be helpful for future readers:
http://gvee.co.uk/files/sql/admin.blocking_chains.sql