-1

We have a SQL Server 2008 server that I successfully migrated to a new computer with SQL Server 2019.

The SQL Server 2008 server name is 123.123.123.123.

It has two instances named: 123.123.123.123 and 123.123.123.123\ABC.

The SQL Server 2019 server name is CMP123456.

The instances are named: CMP123456 and CMP123456\ABC.

I would like to use aliases on the 2019 server because unfortunately some programmers hardcoded the instance names into their code.

I created two aliases:

[123.123.123.123      TCP    1433    localhost]    
[123.123.123.123\ABC  TCP    1433    localhost\ABC]

When I use SSMS to login, however both aliases connect me to CMP123456. I am unable to create an alias that will connect me to CMP123456\ABC.

These are the aliases I have tried.

They all fail as "server not found" errors:

[123.123.123.123\ABC  TCP    1434   localhost\ABC]   
[123.123.123.123\ABC  TCP    1433   CMP123456\ABC]    
[123.123.123.123\ABC  TCP    1433   ABC]

[(No Alias) In this case I was hoping the one localhost alias would work for both instances.]

Any ideas? Is not allowing two aliases a bug/feature of SQL Server 2019?

0
2

For the first two aliases you point both to 1433, which is the default instance. That explains why both of then goes to the default instance (the port number takes precedence over the \instance name).

In your alias config, either specify a backslash with the instance name or specify the port name for the alias which is to point to the named instance.

Also, remember that SSMS is a 32 bit app, so SSMS check the 32 bit alias configs (not the 64 bit configs).

Aliases is a client config, so you will have do this on all your client machines (that need these aliases). The info is in the registry, so you could push out those registry entries by some automated means. Note that you probably want to push out both the 32 and 64 bit registry entries (so the aliases exists for both types of apps). The 32 and 64 bit registry entries are in different places in the registry.

DNS won't help you here, since a DNS entry can't have an \Instance name. That is why some people have each instances have a separate IP adress and then have all instances listning on port 1433 (on their IP), to facilitate "rerouting" through DNS config. That won't help you here since you already have clients including the instance name in the connection string.

4
  • This worked! But how do I set the alias on all of the client machines? All of the machines that access this database don't have SQL Server installed. They are just accessing the database over the network.
    – Koughdur
    Jul 25 at 20:01
  • OK. So I just read that this alias is only for local use. I would need to contact our DNS support group to request that the alias be added, however they will most likely say no. So it looks like I'm going to have to get everyone to make the changes to their code to use the new server name. Oh well, fortunately a while back most people put the path in a resource text file. There are probably just a few cases where the path is located somewhere in the code.
    – Koughdur
    Jul 25 at 20:21
  • I added some more text to my answer above regarding pushing out modifications to clients and DNS. Jul 26 at 11:04
  • Thanks! That additional info is good to know even though not applicable in our case.
    – Koughdur
    Jul 26 at 13:33
0

There is a naming convention

in your case

NetBIOS computer names can't contain the following characters:    
    backslash (\)    
    slash mark (/)    
    colon (:)   
    asterisk (*)    
    question mark (?)    
    quotation mark (")    
    less than sign (<)    
    greater than sign (>)    
    vertical bar (|)

see more

https://docs.microsoft.com/en-us/troubleshoot/windows-server/identity/naming-conventions-for-computer-domain-site-ou

Also don't name a computer localhost it is really really bad, as you don't folow the the convention

The name localhost is reserved for loopback purposes by RFC 6761 (Special-Use Domain Names),[4] which achieved the Proposed Standard maturity level in February 2013. The standard sets forth a number of special considerations governing the use of the name in the Domain Name System

see wikipedia

in short localhost is bad and the second name is against naming and will not work

4
  • The naming convention for SQL Server instances is either [Computer Name] or [Computer Name]\[Instance Name]. All of the documentation on using aliases includes the "\" in the alias. If the hosts file can't have entries that include a slash, then the hosts file will not be a solution. Is there a solution using SQL Server Configuration Manager?
    – Koughdur
    Jul 25 at 16:24
  • instances have [comuterma,e]/[server] that has nothing to do with aliases, which are configured in the hist file
    – nbk
    Jul 25 at 16:30
  • and yes in the sql configuration manager you can make a alias, but in sql server 2017 all changed a bit
    – nbk
    Jul 25 at 16:33
  • The entries I showed you were from the SQL Server Configuration Manager Aliases. They were not from the hosts file. The examples on line have a backward slash and use localhost. I used to be a UNIX sysadmin and this confused me as to why it would work for the main instance, yet it did.. I did try a forward slash and that also gave me a "server not found" error. I tried a couple of things with the host file (using only valid characters) but nothing worked. Any ideas on what the alias should look like in SSCM?
    – Koughdur
    Jul 25 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.