4

I saw a strange case recently and still clueless on what should be the reasoning behind it. On one of my development server, two SQL server instances are running - one is default and other one is UAT instance.

Recently we made the change to the default port of both of these instances and same was communicated to development team and other stakeholders to start their change and test the same. In order to not impact their existing application, port 1433 was kept as it is on default instance. So, below are details of server and port to connect:

Connection for Default --> Server_Name,35683

Connection for Named(SIT) instance --> Server_Name\SIT,35685

Default Instance TCP/IP Properties

Named Instance TCP/IP Properties

Both the instances have similar databases however different set of data as they point to different environment.

One of senior developer was using the connection as Server_name\SIT,35683 meaning mismatch of instance and port. He was using SIT instance however port of default instance and it was connecting him to default instance.

This was caught when I was at his desk and we were testing some code and I noticed this because my change was not reflecting at default instance as I had made changes at SIT instance.

Can someone please explain this behavior and how to sort this out?

Improve this question

1 Answer 1

Reset to default
7

A port number specification overrides the instance name, effectively ignoring the instance name altogether. For example, you can connect using SSMS using "YourSqlServer\badname,1433 even if the instance name is invalid.

When the port number is omitted with an instance name, the client queries the SQL Server browser to determine the port needed.

Improve this answer
2
  • Thank you for the answer. Is it documented anywhere by Microsoft? I couldn't find it.
    – Learning_DBAdmin
    Jun 30, 2019 at 12:58
  • 1
    @Learning_DBAdmin, I don't think this is spelled out in the doc, or at least I couldn't find it. The behavior does make sense, though. With both instance and port, if there's conflict with the actual listening port, the client would either need to reject the connection or use precedence rules. It seems MS chose the latter with the port specification being the winner. That way, an extra browser query is eliminated with the port spec.
    – Dan Guzman
    Jun 30, 2019 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.