2

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.

  • 1
    So each of those named instances is set up with different IPs but the same port, 1433? We do the opposite, same IP but different port for each. – BradC Sep 30 at 18:18
  • 3
    If you have multiple (non-FCI) instances listening on port 1433 on different IPs, you can't have any of them configured to ListenAll on port 1433. That would be a port conflict. That dosen't explain the the behavior you are seeing, though. Add the portion of the default instance's SQL Log where it displays the listening ports and addresses. It appears to be listening on port 1433 on all IPs, perhaps using IPv6. – David Browne - Microsoft Sep 30 at 18:24
  • 1
    @DavidBrowne-Microsoft The default instance is setup to only listen to 10.10.0.236; IPAll is disabled; Netstat -an | find "1433" TCP 10.10.0.236:1433 0.0.0.0:0 LISTENING – Chandler R Reeves Sep 30 at 18:38
3

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.

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.