What happens when port number is not specified in SQL Server connection string?

Question

I have two instances of SQL Server on my local machine. They both listen to separate ports. The first instance that I installed runs on the default port: 1433. I have set the other to listen to port 1434.

My application is using some old shared code that we have here to generate the connection string. So until now I didn't really know what was happening there. Due to a new requirement I found myself needing to examine the connection strings that I'm using to connect to the SQL Server.

What I found that was that for the connection string be built to connect to each of the SQL instances did not include Network Library, nor did they include the port number as part of the Data Source. The Data Source was just set to <Server Name>/<Instance Name>. I did find in the MS documentation that if the Network Library is not included then it is treated as (local), but it doesn't really explain how (local) is treated.

So my question is why is a connection string in this format able to connect to the SQL Server instance that runs on the non-default port? will this only work if the instances are on the local machine, or local network? Can I put <ip>/<sql instance> without the port if the server is remote?

I just need some clarity on how this works, and when is the port number needed and when it is optional as I'm trying to make my connection UI as simple as possible for our users.

Answer 1

There is such a thing called "SQL Server Browser Service":

http://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx

It is intended to provide clients with information about sql server instances, ports. It actually listens on UDP port 1434. With this service turned off you will still be able to connect to the instance, but you need to specify TCP/IP port.

Answer 2

I have two instances of SQL Server on my local machine. They both listen to separate ports. The first instance that I installed runs on the default port: 1433. I have set the other to listen to port 1434.

Just out of curiousity, why don't you just have them setup as named instances both running on the default of 1433 ?

As for why it works, if you search for SQL Configuration in your start menu, you'll find a screen similar to this.

Sql Server supports a number of different ways of connecting. TCP/IP is one way (ie IP addresses and ports) but it also supports a Shared Memory connection if you're on the local machine. that is, if you SQL Server executable, and management studio/client are also on the same machine.

At a guess I'd say it defaults to shared memory when you specify local and therefore you don't need to specify the port.

You could test this out by temporarily disabling shared memory in the above config, and see if your (local)\InstanceName stops working.