Sometimes, in our environment, we have multiple instances of Sql Server running on the same machine. In default configuration, when named instances come up, they listen on some port number. This port number changes every time they restart.Check out the listening port in two ways:

• Sql Server Error Logs
• SCM >> TCP/IP properties window

See below.

Dynamic Configuration : Listening Port

Sql Browser to rescue. When you connect to a named instance without specifying port number in the connection string, the connection is still successful because SQL Browser running on the machine takes that incoming request and re-directs it to the named instance through appropriate port number.

Solution

If you want to make sure the named instances are listening on static port, that could be done through Sql Server Configuration Manager. Sql Server Configuration Manager >> Sql Server Network Configuration >> Protocols for <instance> >> Right Click on TCP/IP >>

Sql Instance Listening Port Properties

In properties window go to “IP Addresses” tab, and scroll to the bottom (like the image below), and enter a port number in the “TCP Port” section. Make sure the “TCP Dynamic Ports” is empty.

Sql Instanc Set Static Port

Now, when you restart the instance, Sql Server will come back up while listening to this static port. See Sql Server Error Logs and TCP/IP properties window below.

Sql Instance Listening on Static Port

 

Connect To Static Port Named Instance