Ggthjy

How does it work when somebody invests in my business?

Trouble understanding the speech of overseas colleagues

Inappropriate reference requests from Journal reviewers

Why are there no referendums in the US?

Go Pregnant or Go Home

Why doesn't a table tennis ball float on the surface? How do we calculate buoyancy here?

Can the discrete variable be a negative number?

Why were Madagascar and New Zealand discovered so late?

Is a stroke of luck acceptable after a series of unfavorable events?

Crossing the line between justified force and brutality

How do I rename a Linux host without needing to reboot for the rename to take effect?

What happens if you roll doubles 3 times then land on "Go to jail?"

Abbreviate author names as "Lastname AB" (without space or period) in bibliography

Hostile work environment after whistle-blowing on coworker and our boss. What do I do?

Is this apparent Class Action settlement a spam message?

Why use "finir par" instead of "finir de" before an infinitive?

Where does the Z80 processor start executing from?

How does Loki do this?

Is there a korbon needed for conversion?

How does the UK government determine the size of a mandate?

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

Higher powers of matrix A

Pole-zeros of a real-valued causal FIR system

Tiptoe or tiphoof? Adjusting words to better fit fantasy races

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

SQL 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

2

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

share|improve this question

asked 2 hours ago

蕭為元蕭為元

1808

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Different instances must have different Port no, using a default port (1433) you of course connect to the default instance
  
  – Denis Rubashkin
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  On that same view, go to SQL Server Network Configuration, select your instance, double click TCP/IP, IP Addresses and check the TCP Port value for your instances.
  
  – EzLo
  55 mins ago
  
  
  

  
  
  
  

add a comment | 

2

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

share|improve this question

asked 2 hours ago

蕭為元蕭為元

1808

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Different instances must have different Port no, using a default port (1433) you of course connect to the default instance
  
  – Denis Rubashkin
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  On that same view, go to SQL Server Network Configuration, select your instance, double click TCP/IP, IP Addresses and check the TCP Port value for your instances.
  
  – EzLo
  55 mins 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

share|improve this question

asked 2 hours ago

蕭為元蕭為元

1808

share|improve this question

asked 2 hours ago

蕭為元蕭為元

1808

share|improve this question

asked 2 hours ago

蕭為元蕭為元

1808

asked 2 hours ago

蕭為元蕭為元

1808

asked 2 hours ago

蕭為元蕭為元

1808

3 Answers
3

active

oldest

votes

2

enter code hereTo 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.

share|improve this answer

answered 15 mins ago

sepupicsepupic

7,781820

add a comment | 

0

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.

share|improve this answer

answered 39 mins ago

Martin CairneyMartin Cairney

112

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 | 

0

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.

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.

share|improve this answer

edited 50 secs ago

answered 41 mins ago

Ramakant DadhichiRamakant Dadhichi

1,008319

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
});


}
});

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

2

enter code hereTo 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.

share|improve this answer

answered 15 mins ago

sepupicsepupic

7,781820

add a comment | 

2

enter code hereTo 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.

share|improve this answer

answered 15 mins ago

sepupicsepupic

7,781820

add a comment | 

enter code hereTo 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.

share|improve this answer

answered 15 mins ago

sepupicsepupic

7,781820

share|improve this answer

answered 15 mins ago

sepupicsepupic

7,781820

answered 15 mins ago

sepupicsepupic

7,781820

answered 15 mins ago

sepupicsepupic

7,781820

0

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.

share|improve this answer

answered 39 mins ago

Martin CairneyMartin Cairney

112

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 | 

0

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.

share|improve this answer

answered 39 mins ago

Martin CairneyMartin Cairney

112

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.

share|improve this answer

answered 39 mins ago

Martin CairneyMartin Cairney

112

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.

share|improve this answer

answered 39 mins ago

Martin CairneyMartin Cairney

112

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 39 mins ago

Martin CairneyMartin Cairney

112

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 39 mins ago

Martin CairneyMartin Cairney

112

0

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.

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.

share|improve this answer

edited 50 secs ago

answered 41 mins ago

Ramakant DadhichiRamakant Dadhichi

1,008319

add a comment | 

0

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.

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.

share|improve this answer

edited 50 secs ago

answered 41 mins ago

Ramakant DadhichiRamakant Dadhichi

1,008319

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.

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.

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.

share|improve this answer

edited 50 secs ago

answered 41 mins ago

Ramakant DadhichiRamakant Dadhichi

1,008319

share|improve this answer

edited 50 secs ago

answered 41 mins ago

Ramakant DadhichiRamakant Dadhichi

1,008319

answered 41 mins ago

Ramakant DadhichiRamakant Dadhichi

1,008319

answered 41 mins ago

Ramakant DadhichiRamakant Dadhichi

1,008319