HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
allsqlinstancedefaultinsteadassignedserveraliaseslinked

Problem

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

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?

Solution

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.

Context

StackExchange Database Administrators Q#233355, answer score: 4

Revisions (0)

No revisions yet.