I've been working with SQL Server and its instance types – default and named. My understanding has always been that to connect to a named instance, it's necessary to specify the instance name. However, I've recently discovered that if I set the TCP port to 1433, I can connect to the named instance without having to specify the instance name, and without any need for setting aliases.
I'm curious to know if this is expected behavior. I haven't been able to find any documentation that explains or confirms this behavior. Can anyone provide some insight into this? Is it typical to be able to connect to a named instance without providing the instance name when the TCP port is set to 1433?
I appreciate any clarification on this matter.
Thanks in advance!
Best Answer
To connect to any SQL Server instance, named or default, the network library that does the work needs to know 2 parameters, IP and port number. There is a default port reserved to the default instance, 1433, so if you don't specify any port number the connection is made using this port. But if you have a named instance and know its port number you can connect to it by passing
IP,<port number>without using the name of named instance. When you use the name the following work is done for you: SQL Server Browser accepts the name and gives back port number so no mystery about a name: it still being translate to a port number. Without SQL Server Browser running you'll get a connection error because there is no way to know the port.What did you do when set your named instance on 1433? You just make it impossible now to install a default instance on the same pc as the named instance just occupied it's default port. Yes, now you can connect without specifying port number but you can only do this for one instance of SQL Server for this pc.