locked
SQL clustered instance outgoing traffic RRS feed

  • Question

  • Hi,

    I have an interesting issue I'm trying to solve. Hopefully I'm missing something really simple. Here's a description of our environment, and the issue I'm having.

    2 node SQL 2008 R2 SP2 clustered named instance. There are a total of 4 instance names on the cluster.

    Node 1 IP is 192.168.1.121

    Node 2 IP is 192.168.1.122

    Named instance IPs are 192.168.1.123 to 192.168.1.126

    Now here's my dilemma. When I connect to a linked server from one of the 4 named instances, instead of the remote server seeing the named instance IP (eg, 192.168.1.126), it's seeing the IP of the node that is hosting that instance (eg, 192.168.1.121). This is causing us an issue, as the remote SQL server is behind a firewall, and we expected it to use the instance IP and not the host IP. I have already tried to force the IP in the Server Configuration Manager for the instance name, but this has not helped.

    The server logs for the instance show it listening only on the instance IP (eg. 192.168.1.126 <ipv4> 1433).

    All communications to the named instances use the correct IP, and not the node IP. Any help would be greatly appreciated.

    Regards,

    Hugh McDaid

    Wednesday, September 24, 2014 12:17 AM

Answers

  • Hugh,

    Unfortunately, as others have found out many times over the years of working with failover clusters, this is not the case. The IP resource for SQL Server is bound to the physical links of the node when the resources are started, this is how it is able to be fault tolerant across nodes. You're not the only person who gets grief with this. There is currently no way to change this behavior.

    You may want to watch the networking essentials for windows clusters. It's definitely worth the time!

    http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/MDC-B337#fbid=


    Sean Gallardy | Blog | Microsoft Certified Master

    • Marked as answer by Hugh T McDaid Friday, September 26, 2014 6:01 PM
    Wednesday, September 24, 2014 4:27 PM
    Answerer

All replies

  • Hugh,

    Unfortunately, as others have found out many times over the years of working with failover clusters, this is not the case. The IP resource for SQL Server is bound to the physical links of the node when the resources are started, this is how it is able to be fault tolerant across nodes. You're not the only person who gets grief with this. There is currently no way to change this behavior.

    You may want to watch the networking essentials for windows clusters. It's definitely worth the time!

    http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/MDC-B337#fbid=


    Sean Gallardy | Blog | Microsoft Certified Master

    • Marked as answer by Hugh T McDaid Friday, September 26, 2014 6:01 PM
    Wednesday, September 24, 2014 4:27 PM
    Answerer
  • Hello Hugh,

    You need to add an entry to your DNS server that resolves to the virtual IP used by each cluster role, and also add it as a resource for the corresponding role. Make sure the network interface used by SQL server outbound connections has its DNS server set up correctly.

    If that doesn't work, add the DNS name to your hosts file, pointing to the IP address.

    "I have already tried to force the IP in the Server Configuration Manager for the instance name, but this has not helped."

    So, you've set up an ALIAS for your sql server native client, right? Please note that there are two native clients installed in your servers, one for 32bit applications and the other for 64bits. Make sure the alias is set for BOTH clients. SSMS uses the 32bit aliases, and remember that they only apply locally. If you wanna work with them, tou need to configure the same aliases in each host that needs to connect to the instance.

    Is this problem only happening between clustered instances?

    Wednesday, September 24, 2014 5:44 PM
  • Sean,

    I did think that was going to be the case. From all the information I have found, it was leading to the SQL instance using the node's IP and not the virtual IP. Would be nice for them to somehow fix this "feature" in future releases. I'm sure that would resolve a number of headaches for people.

    Regards,

    Hugh

    Thursday, September 25, 2014 11:41 PM
  • Samir,

    The issue is not a DNS issue. All the names resolve without any issue, and I don't understand what you mean by "Make sure the network interface used by SQL server outbound connections has its DNS server set up correctly".

    Also, I don't see a need to use an ALIAS in the native client, as the servers are contactable using either the hostname\instance or fdqn\instance.

    The issue is when the SQL server communicates to a remote SQL server using [linked_server].[database].[schema].[object], the remote server sees the traffic from the node IP and not the virtual IP.

    The problem is from a clustered instance to any type of SQL (clustered or non-clustered).

    Regards,

    Hugh

    Thursday, September 25, 2014 11:50 PM
  • Hugh,

    After some research and testing, I think I found the root cause of this behavior.

    When you connect to another instance by using a linked server, you are using the local OLE DB provider of your SQL Server Native Client. The problem is that the native client is NOT part of your sql server instance and therefore isn't bound to the virtual IP, but rather to the physical one.

    I've consulted with my company's system admins and noone knows if its possible to force the native client to use the virtual IP address. I also spent a few hours searching the internet but didn't find any articles on this subject either.

    Perhaps someone else here may help.

    Friday, September 26, 2014 7:04 PM