patternsqlMinor
All SQL Server aliases linked to default instance instead of instance I assigned
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?
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
The "name" of a
When the
When there is
You can test it by yourself by changing
if you create an
you'll be connected to the
Vice versa, if you omit
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,1433you'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.