2019-05-30 | VS2017 hangs on SSDT schema compare against SQL Server 2016 database
My team makes frequent use of SSDT (SQL Server Data Tools) for writing and deploying database changes. Our favorite feature is the Schema Comparison, where you can compare your .sqlproj database project against an actual SQL database. This makes it extremely easy to see every object that your project will be creating, modifying, or deleting, and you can even deploy these differences directly after the comparison. No more cobbling together SQL scripts, worrying about the order of operations, refreshing views, or any of that scary deployment stuff, SSDT does it all for you. All you need to do is compare and deploy.
The problem started when we upgraded our SQL server from 2014 to 2016. We would hit the lovely little Compare button, the 'Initializing comparison' message would pop-up as usual, but after waiting for a few minutes the comparison would stop spinning without showing any results or differences, as if you didn't even click Compare.
Perhaps the strangest and most frustrating thing about this issue was that it was intermittent. It would only occur occasionally, but when it did, the comparison would be out of commission for the entire day, until nightly jobs ran and something changed. We essentially ended up with a 50:50 chance that the comparison tool would work each day, and we couldn't piece together what the cause was or what was different between the days when it would work and when it wouldn't.
The first bit of progress we made against this issue was when we discovered that turning the compatibility mode of the database to 'SQL Server 2014' would always make the compare work once again. We didn't want to do this for the Live environment, but it was a decent work-around for us to use when comparing against test databases. It also didn't do much do explain what the root of the problem was.
We ended up putting in a ticket with Microsoft support, giving them all of the information I've gone over so far. I went back and forth with them for a while, including running PSDIAG diagnostics on our SQL server while the compare failure was occurring. Microsoft couldn't seem to get to the bottom of the issue either, despite us following all troubleshooting and diagnostic advice.
I eventually packaged up our entire database in a .bak backup file, along with our .sqlproj project and the .scmp comparison file, and sent it off to our Microsoft tech, hoping that they'd be able to completely replicate our environment and the issue. Of course, the .bak file was massive, since it was our entire database, so I spent some time making it smaller and more manageable, which ended up allowing me to unlock some secrets.
I discovered that if I wiped out every record from every table in the database, the comparison issue would still occur. This 'skeleton' of a database would be the perfect thing to send off for troubleshooting, and it would also allow me to restore it fairly quickly so I could try different solutions over and over. So, I put together a script that would do this as quickly as easily as possible.
The following SQL will 'skeletonize' all tables in whatever database you run it against, obliterating all records. It works by temporarily disabling all triggers and constraints, attempting to TRUNCATE each table (performing a slower DELETE when that doesn't work), then re-enabling the triggers and constraints. It's probably best not to run this in Live. As I said, this won't fix the issue described in this article, but it will reduce the size of your database and make it much quicker to restore your database over and over for diagnostic reasons.
EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'BEGIN TRY TRUNCATE TABLE ? END TRY BEGIN CATCH SET QUOTED_IDENTIFIER ON; DELETE FROM ? END CATCH'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'After sending the nice little database to our Microsoft tech, I started trying different things to see if I could fix it myself. I knew that something about our nightly jobs could cause or resolve the compare issue for the rest of the day until the job ran again, so I started tearing them apart and running one piece at a time.
Eventually, I discovered that if I was receiving the compare issue and I rebuilt indexes on the entire database (a very quick task after the entire database has been skeletonized as above), then the issue would solve itself. I then narrowed it down to a single table. If I rebuilt all indexes on this table, I could once again compare against the database. This table was not related in any way to the differences in the comparison, it was just a random table as far as I could tell. I tried to narrow it down further to a single index on that table, but was unable to, it appeared to be some combination of them.
I told our Microsoft tech that rebuilding a set of indexes could solve the 'initializing comparison' issue, hoping that it would point them in the right direction for debugging even though it wasn't really a useful solution for us.
Around this time, SSDT for Visual Studio 2019 was released, and being the trendy department, we immediately upgraded to it. From that point onwards, we never had the 'initializing comparison' issue again. VS2019 has apparently solved it somehow. We told Microsoft to close the ticket, and all of the above debugging and diagnostics were for naught.
So, if you're running into an intermittent 'initializing comparison' issue, I recommend upgrading your Visual Studio to 2019. If that's not an option for some reason, there may be some solution hidden in rebuilding indexes that you could look into.