All SQL Server aliases linked to default instance instead of instance I assigned The Next CEO of Stack OverflowSQL Server SMO Method “Discover()”Besides attaching the debugger, is there any other differences between “(local)Instance” and “MyLocalNameInstance”?Troubleshooting a SQL Server instance that won't startHow to create a server alias in SQL Server 2012How to uniquely identify SQL instanceFollow up to: SQL Server 2008 R2: Problems after computer name changeMulti-instance SQL Server Standard Editon MaxDop settingsSQL Server 2008R2 Alias not workingDefault instance name different than server nameSQL Server : cannot add subscriber to publication
What connection does MS Office have to Netscape Navigator?
Is it professional to write unrelated content in an almost-empty email?
calculus parametric curve length
What happened in Rome, when the western empire "fell"?
Indicator light circuit
Why did we only see the N-1 starfighters in one film?
Bold, vivid family
Why don't programming languages automatically manage the synchronous/asynchronous problem?
Is "for causing autism in X" grammatical?
Interfacing a button to MCU (and PC) with 50m long cable
Can I equip Skullclamp on a creature I am sacrificing?
Anatomically Correct Strange Women In Ponds Distributing Swords
Can we say or write : "No, it'sn't"?
Contours of a clandestine nature
Complex fractions
What does convergence in distribution "in the Gromov–Hausdorff" sense mean?
Is it my responsibility to learn a new technology in my own time my employer wants to implement?
Won the lottery - how do I keep the money?
Example of a Mathematician/Physicist whose Other Publications during their PhD eclipsed their PhD Thesis
What is the purpose of the Evocation wizard's Potent Cantrip feature?
Help understanding this unsettling image of Titan, Epimetheus, and Saturn's rings?
Preparing Indesign booklet with .psd graphics for print
Does it take more energy to get to Venus or to Mars?
How to invert MapIndexed on a ragged structure? How to construct a tree from rules?
All SQL Server aliases linked to default instance instead of instance I assigned
The Next CEO of Stack OverflowSQL Server SMO Method “Discover()”Besides attaching the debugger, is there any other differences between “(local)Instance” and “MyLocalNameInstance”?Troubleshooting a SQL Server instance that won't startHow to create a server alias in SQL Server 2012How to uniquely identify SQL instanceFollow up to: SQL Server 2008 R2: Problems after computer name changeMulti-instance SQL Server Standard Editon MaxDop settingsSQL Server 2008R2 Alias not workingDefault instance name different than server nameSQL Server : cannot add subscriber to publication
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?
sql-server sql-server-2012 sql-server-2017
asked yesterday
蕭為元蕭為元
1829
add a comment |
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?
sql-server sql-server-2012 sql-server-2017
asked yesterday
蕭為元蕭為元
1829
4
Different instances must have differentPort no, using a default port (1433) you of course connect to the default instance
– Denis Rubashkin
yesterday
@DenisRubashkin I thought that's something SQL Server browser will take care for me, lesson learned!
– 蕭為元
16 hours ago
add a comment |
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?
sql-server sql-server-2012 sql-server-2017
asked yesterday
蕭為元蕭為元
1829
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?
sql-server sql-server-2012 sql-server-2017
sql-server sql-server-2012 sql-server-2017
asked yesterday
蕭為元蕭為元
1829
asked yesterday
蕭為元蕭為元
1829
asked yesterday
蕭為元蕭為元
1829
asked yesterday
蕭為元蕭為元
1829
asked yesterday
蕭為元蕭為元
1829
1829
4
Different instances must have differentPort no, using a default port (1433) you of course connect to the default instance
– Denis Rubashkin
yesterday
@DenisRubashkin I thought that's something SQL Server browser will take care for me, lesson learned!
– 蕭為元
16 hours ago
add a comment |
4
Different instances must have differentPort no, using a default port (1433) you of course connect to the default instance
– Denis Rubashkin
yesterday
@DenisRubashkin I thought that's something SQL Server browser will take care for me, lesson learned!
– 蕭為元
16 hours ago
4
4
Different instances must have different
Port no, using a default port (1433) you of course connect to the default instance– Denis Rubashkin
yesterday
Different instances must have different
Port no, using a default port (1433) you of course connect to the default instance– Denis Rubashkin
yesterday
@DenisRubashkin I thought that's something SQL Server browser will take care for me, lesson learned!
– 蕭為元
16 hours ago
@DenisRubashkin I thought that's something SQL Server browser will take care for me, lesson learned!
– 蕭為元
16 hours ago
add a comment |
3 Answers
3
active
oldest
votes
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.
answered yesterday
sepupicsepupic
7,806820
add a comment |
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.
answered yesterday
Martin CairneyMartin Cairney
212
New contributor
Martin Cairney is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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.
- Note down the Static port number for each instance.
- Specify the port number while creating alias
A few useful links :
MSDN Link
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.
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.
answered yesterday
Ramakant DadhichiRamakant Dadhichi
1,028319
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233355%2fall-sql-server-aliases-linked-to-default-instance-instead-of-instance-i-assigned%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered yesterday
sepupicsepupic
7,806820
add a comment |
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.
answered yesterday
sepupicsepupic
7,806820
add a comment |
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.
answered yesterday
sepupicsepupic
7,806820
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.
answered yesterday
sepupicsepupic
7,806820
edited yesterday
answered yesterday
sepupicsepupic
7,806820
answered yesterday
sepupicsepupic
7,806820
answered yesterday
sepupicsepupic
7,806820
7,806820
add a comment |
add a comment |
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.
answered yesterday
Martin CairneyMartin Cairney
212
New contributor
Martin Cairney is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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.
answered yesterday
Martin CairneyMartin Cairney
212
New contributor
Martin Cairney is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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.
answered yesterday
Martin CairneyMartin Cairney
212
New contributor
Martin Cairney is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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.
answered yesterday
Martin CairneyMartin Cairney
212
New contributor
Martin Cairney is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered yesterday
Martin CairneyMartin Cairney
212
New contributor
Martin Cairney is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered yesterday
Martin CairneyMartin Cairney
212
answered yesterday
Martin CairneyMartin Cairney
212
212
New contributor
Martin Cairney is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Martin Cairney is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Martin Cairney is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
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.
- Note down the Static port number for each instance.
- Specify the port number while creating alias
A few useful links :
MSDN Link
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.
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.
answered yesterday
Ramakant DadhichiRamakant Dadhichi
1,028319
add a comment |
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.
- Note down the Static port number for each instance.
- Specify the port number while creating alias
A few useful links :
MSDN Link
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.
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.
answered yesterday
Ramakant DadhichiRamakant Dadhichi
1,028319
add a comment |
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.
- Note down the Static port number for each instance.
- Specify the port number while creating alias
A few useful links :
MSDN Link
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.
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.
answered yesterday
Ramakant DadhichiRamakant Dadhichi
1,028319
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.
- Note down the Static port number for each instance.
- Specify the port number while creating alias
A few useful links :
MSDN Link
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.
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.
answered yesterday
Ramakant DadhichiRamakant Dadhichi
1,028319
edited yesterday
answered yesterday
Ramakant DadhichiRamakant Dadhichi
1,028319
answered yesterday
Ramakant DadhichiRamakant Dadhichi
1,028319
answered yesterday
Ramakant DadhichiRamakant Dadhichi
1,028319
1,028319
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233355%2fall-sql-server-aliases-linked-to-default-instance-instead-of-instance-i-assigned%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4
Different instances must have different
Port no, using a default port (1433) you of course connect to the default instance– Denis Rubashkin
yesterday
@DenisRubashkin I thought that's something SQL Server browser will take care for me, lesson learned!
– 蕭為元
16 hours ago