0

I am using Visual Studio 2015 Update 3 and SQL Server 2016 on two remote servers, one with default port and the other with non-default.

When using the SQL Schema Compare tool to compare 2 databases on the first (default port) server, everything seems to work ok.

However, when I try to use the tool on the second server I seem to be unable to select a source. I can go into the source settings and see the details (via SQL Auth). The list of databases populates (i.e. authentication is ok). When I press Connect, it returns to the "Select Source Schema" form but the Database textbox is not populated and the OK button remains disabled.

For both servers, the accounts I am using have been granted full rights in SQL Server. Querying databases on both servers in SSMS works fine, suggesting no firewall issue. The only difference I can see is that the second server has a non-default port.

I have run out of ideas to try to fix this and would be most grateful for any suggestions.

SQLian
  • 21
  • 2

1 Answers1

1

Make sure you are specifying the port in the server name, i.e. it should looks like SERVER:1234\INSTANCE, where 1234 is the port, on which your instance listen. If it isn't a default instance, then by default the port is dynamic, unless you configured it to use static port. But from your post it looks like it is already the case.

If the port is correctly specified in the server name, but you still have issues connecting, you can try to define an alias for this server. This means that you define a name, e.g. SERVER2, which the SQL client will then decode as real server name and port to be used. Run C:\Windows\System32\cliconfig.exe and C:\Windows\SysWOW64\cliconfig.exe to define an alias for 32/64 bit clients. In Alias tab, click Add... button and define a unique alias for your server, i.e. something that doesn't exists in your network (SERVER2 from the screenshot):

enter image description here

Then select TCP/IP protocol and enter real SQL server name and port there (SERVER and 1234). After that you should be able to connect to the alias (SERVER2) as it is a real server, without specifying the port at all.

Hope this helps!

Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32
  • Thank you for your reply. Although the Aliasing itself works, the issue with the SQL Schema Compare tool persists. i.e. I configure the connection, it works ok in that the database combobox is populated, but when I click select it returns to the "Select" form with nothing in the database textbox. – SQLian Nov 15 '18 at 14:54
  • Ok, I have managed to fix the problem. It seems that there is a SSDT (Sql Server Development Tools) component in Visual Studio which needed updating. It had to be updated via VS itself. I tried to download the standalone tools but they were reporting incompatible O/S errors. The solution is in the following link (refers to 2017, but it also applies to 2015): https://stackoverflow.com/questions/44419310/sql-server-compare-schema-in-visual-studio-2017 – SQLian Nov 16 '18 at 16:21