SQL Server Compare Schema in Visual Studio 2017

Question

I am attempting to compare schemas of two databases on the same server.

The two databases are named benchmarkdb and benchmarkdb_dev.

In Visual Studio 2017, I go to Tools > SQL Server > New Schema Compare...

From both drop down menus labeled "Select Source" and "Select Target" (as seen in the picture below), I want to select benchmarkdb as source and bencmarkdb_dev as target.

After clicking to select a source, the "Select Source Schema" pops up, and I clicked the "Select Connection..." button, which brought another popup. The newest popup (the one on the right in the picture below) shows both databases I am trying to compare.

However, after I choose one for either the source or the target, all pop ups close, but nothing populates the "Select Source..." or "Select Target..." drop down menus. In fact, it just goes back to looking like the first picture.

Is there a solution to this so I can compare the schemas between the databases?

Update:

I have tried this on VS 2012, 2015, and 2017. All are facing the same issue. VS software and system software are up to date. Below are some versions of software that I am using, if it is any help.

Windows 7 Enterprise SP1

VS 2012, 2015, and 2017

SQL Server 2014

.NET Framework v4.7.02053

Answer 1

OK, so I managed to fix it. First off, you need to install SSDT from Microsoft pages. Just google it. Make sure that you also have it listed in individual components when starting Installer (on Modify). Only SQL Server Database is required (I could choose from Integration Services and 3 more).

Next, you need to "Remove connection from history" in Schema Compare modal for connection selection.

I closed VS at this point, to make sure that the connection is not in history anymore. Starting VS the next time, you should not have the problematic connection in history.

Recreate the connection (I checked "remember password") and voila. Schema compare works.

Not really sure, but in desperation I also installed "Redgate ReadyRoll Core", "Redgate SQL Prompt Core" and "Redgate SQL Search". I don't think it matters, but if all else fails...

Edit: This worked on 2 machines and it doesn't seem to be connected to VS version.

Answer 2

I had the same issue, but I don't know if I had the same root cause.

My problems started after changing my database from SQL Server on a Window Server to SQL Server on a Linux Container. The schema compare would appear to accept my selection but the database field would never actually populate and I could not perform a comparison.

There were 2 things I had to do to solve the problem.

1. Update Visual Studio (using the standard updater within Visual Studio).
2. Update the SQL Server Development Tools (SSDT) add-on for Visual Studio to a preview version that supports SQL Server on Linux. That download is available here.

After both updates were in place I could select the database and execute Schema Comparisons between my project and SQL Server on Linux exactly as I had been doing for SQL Server on Windows.