SQL Server 2019: Two Aliases point to same instance

aliassql serversql-server-2019

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?

Best Answer

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.

Related Question