SQL Server Instance Name vs Port

Do you know that if you have a named instance of SQL, and that SQL instance is setup to use a static port, you do not need instance name when connecting to the SQL instance? you can use port also

Just brief about instance and port:

SQL Instance: The name of SQL server installed for default its (MSSQLSERVER) and named is your choice name.
                Instance comes in picture in SQL 2000 when sql server start allowing to have multiple instances of SQL server engine on same machine (I am not talking about VM's).

Port: Port is term used in many places in computer world. To connect one machine to another we need ports along with authorization. These ports are specific to application , service or software to avoid a hacker to connect without authentication. SQL server also have mechanism to use port (static and dynamic)


In SQL server you can specify port explicitly (static) or use dynamic ports to connect.

Scenario: I have 2 instances of SQL server  with static ports 20000 and 20001 respectively

1. MAC\SQL1
2. MAC\SQL2

You can connect to Instance 1 "SQL1" by using below in connection string

• MAC\SQL1
• MAC ; 20000
• MAC\SQL1 ;20000
• MAC\SQL2 ;20000

Did i surprise you with last 1 (MAC\SQL2 ;2000)?

May be you are surprised but yes if you give port no., SQL Server will give priority to port than instance name and will connect to instance SQL1 without throwing error.

It might seems wrong to you or you will feel it can cause critical production issue but its by behavior. One of or community member have already raised same in MS connect

Now question arises do we really need all 4 ways to use this behavior?

Yes we need it and below are some features which works with only one of mechanism.

1. If you are configuring replication you can't use port no. , you have to use server\instance name (Reference error)
2. If you are using Notification Services - when configuring a SQL server as a parameter, the UI will actually blow up on the "\" character.
3. For executing WMI calls using the ManagementObjectSearcher object and you specify a server database name, the call actually returns no results with the instance name included.

Thanks to Johnny BizTalk for sharing reference on same .