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?
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:
entry for 10.10.0.236 TCP port 1433 - enabled;
Removed Port from IPAll;
Network Config for Inst1:
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
There were two changes that i had to make to get this to work:
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)
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.