Sql-server – DNS Alias for SQL Instance – LOTS of Instances – All going to default instance

sql serversql-server-2012

We run an SQL server (2012 SP4) that has 40+ named instances (one for each client) and the default instance. We are thinking about replace the single server with multiple servers over the next few years. I'd like to setup a DNS alias for each of the instances so that we can make switch for the users more seamless (plus the instance names are long and gross and we get users complaining about them all the time).

We also have software the uses the full instance name (Server\Inst1) to connect, and i'd like to keep that functionality.

I was trying to implement DNS Aliases, the alias connects, but only connects to the default instance. Below is what i've setup so far, I'm not sure what is causing all of the aliases to connect only to the default instance but the normal connection (server1\inst1) works fine. It seems like it should work. What am i missing?

Server: Server1

Network Cards: 2
1. normal traffic and default instance (10.10.0.236)
2. SQL Alias connections w/ 40+ IP's all pingable; separate NIC so that "Register this connection address in DNS" can be unchecked and manually setup.

DNS: Host A records for all Aliases created. Verified that pinging the Alias resolves to the correct IP.

Instance: Server1\Inst1; DNS Alias: Inst1; IP for Inst1: 10.10.0.237;

SQL Server Config Manager:
Network Config for Default Instance:
SM/NP/TCP Enabled;
TCP: ListenAll=No;
entry for 10.10.0.236 TCP port 1433 – enabled;
Removed Port from IPAll;

Network Config for Inst1:
SM/NP/TCP Enabled;
TCP: ListenAll=yes; (to support normal Server\Inst1 connection)
Changed entry to 10.10.0.237 TCP port 1433 – enabled;
Left Port in IPAll;

Breakdown of the below query results for different types of connections:

    SELECT @@Servername, @@ServiceName
    SELECT * FROM sys.dm_exec_connections WHERE session_id = @@SPID

Alias INST1 – SQL Authentication

    @@Servername=Server1, @@ServiceName=MSSQLSERVER 
    SQL Auth: Net_Transport=named pipe; Auth_Scheme=SQL

Alias INST1 – Windows Authentication 

    @@Servername=Server1, @@ServiceName=MSSQLSERVER 
    Net_Transport=named pipe; Auth_Scheme=NTLM

Server1\Inst1 – SQL Authentication 

    @@Servername=Server1\Inst1, @@ServiceName=Inst1
    Net_Transport=TCP; Auth_Scheme=SQL

Server1\Inst1 – Windows Authentication 

    @@Servername=Server1\Inst1, @@ServiceName=Inst1
    Net_Transport=TCP; Auth_Scheme=NTLM

.
.
.
EDIT (Solution):

There were two changes that i had to make to get this to work:

  1. Disable IPAll and manually configure both 10.10.0.236 connection
    Full Inst1 Network config now:
    Protocol tab:
    ListenAll=no;
    IP Address tab:
    CUT port from IPAll to IP(n) that had 10.10.0.236 IP; Enabled .236 IP; (to support normal Server\Inst1 connection)
    Changed entry to 10.10.0.237 TCP port 1433 – enabled; (in my case i had to manually overwrite one of the unused IP(n) connections because .237 wasn't showing up)

  2. After restarting the SQL Inst1 instance i had to restart the SQL Browser before it would actually update the port listeners and link .237:1433 with Inst1

Thank you David Browne – Microsoft & BradC for responding.

Best Answer

Below is what i've setup so far, I'm not sure what is causing all of the aliases to connect only to the default instance but the normal connection (server1\inst1) works fine. It seems like it should work. What am i missing?

You're allowing Named Pipes connections to the default instance:

@@Servername=Server1, @@ServiceName=MSSQLSERVER 
SQL Auth: Net_Transport=named pipe; Auth_Scheme=SQL

You need to turn off Named Pipes if you want to control connectivity through specific IP addresses and ports. It's only the default instance that listens to the \\.\pipe\sql\query pipe. Named instances will listen on a pipe called \\.\pipe\MSSQL$SOMEINSTANCE\sql\query. And all the named pipes are available on all the IP addresses.

So basically in your case, the client is connecting to

\\INST1\pipe\sql\query

Which is the default instance's pipe. When a named pipes client connects to the server without an instance name, it will always connect to the default instance's pipe.

Related Question