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
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?
Best Answer
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.