All SQL Server aliases linked to default instance instead of instance I assigned

Question

I have multiple SQL Server instances(different versions) on my computer. I try to create aliases for each instance as enter image description here

First I thought I had everything settled then I found no matter which alias name I connect, it will all be led to SQL2017 instance.

I wanna provide more information but don't know where to start with. And idea what I might do wrong?

• Denis Rubashkin: Different instances must have different Port no, using a default port (1433) you of course connect to the default instance
• Circle Hsiao: @DenisRubashkin I thought that's something SQL Server browser will take care for me, lesson learned!

Answer 1

To correctly connect to SQL Server instance the network library has to know 2 things: IP address and port number. These two are necessary and sufficient.

The "name" of a named instance make no difference to the library, it was invented when named instances were introduced and it is used just to determine the port number.

When the port number is missing in the connection string, the network library asks for it SQL Server Browser (on UDP 1434) sending it the instance name. As the responce it receives port number.

When there is port number in the connection string, instance name is just ignored.

You can test it by yourself by changing instance name to bla-bla-bla: if you create an alias or just type in SSMS connection dialog .\bla-bla-bla,1433 you'll be connected to the default instance.

Vice versa, if you omit instance name of the named instance but indicate the correct port where your named instance is listening on, for example .,8852 where 8852 is the port of named instance you'll be connected to this named instance even if it's name was not specified.

Answer 2

You also will need to make sure that the other instances are NOT using dynamic ports - otherwise your port number may change after a restart and your Alias will again be broken.

Answer 3

The port number is very essential when you have named instances running on a box. For each alias to be unique, you will need to use the port number associated with each instance which can found in the tcp/ip properties.

1. Note down the Static port number for each instance.
2. Specify the port number while creating alias

A few useful links :

1. MSDN Link

2. Sample blog

The parameter field and Server are most important which direct the traffic to the correct instance.

For example , the below will connect to the hostname(Provide the server name here) and to the instance which is listening on 2466.

enter image description here

To connect remotely from App server (any other), you will need to configure same alias name using cliconfg.exe for both 64 and 32 bit. This will help you connect to the alias remotely.