SQL Server Performance

IP changes

Discussion in 'SQL Server 2005 Clustering' started by jpopp, Dec 3, 2009.

  1. jpopp New Member

    We have an intensley-used Windows 2003 R2 SP2 Enterprise/SQL Server 2005 SP3 two-node cluster.
    The server's IP is going to change (moving to a new subnet).
    I know there is code and configuration pointing to the cluster IP address and the sql IP address and not the DNS name.
    Is there any way to identify those sources?
    One way I've done this in the past is to create a route on the switches with the old IP, pointed it at both nodes, then put loopbacks on the nodes. DNS is changed to the new IP on the box. I then monitor connections. Anything connecting to the old IP, I can easily capture on the switches.
    An idea I had would be similar to just add another SQL IP in Cluster Administrator and do similar monitoring as my first suggestion. I would move the DNS name to the new IP.
    Does anyone have a less cludgy, less latency-adding solution? I guess I'd be interested in any ideas though. The operating premise is to proceed sanely and identify all IP-based connections (and then change them to using DNS).
  2. melvinlusk Member

    Do you have the source code for the applications utilizing this cluster? If so, are you source control?
    I would do a blanket search of any source code that contains the old IP. This is fairly straight forward in SourceSafe or Team Foundation Server.
  3. jpopp New Member

    In some cases yes, in some cases no. Our environment could be approximated by the system that was described in The Cuckoo's Egg. Hunting through source won't get us fixed.
    I tried the IP forwarding in the switches, but SQL didn't like that and even though I could connect to everything else via the forwarded IP, SQL wouldn't, probably because the server's binding on the IP somewhere.
    Adding a SQL IP 2 in the Cluster Administrator seems to satisify everything but I don't want the cluster to keep registering the DNS name on both IP's, just the new one. No solution for that yet.
  4. melvinlusk Member

    Well, unlike the Cuckoo's Egg, you don't have to worry about supporting DEC VAX [:)]
    Not sure of what to tell you on the IP forwarding, my Cisco skills are not up to par.
    Out of curiosity, why are you against having two DNS entries?
  5. melvinlusk Member

    Also, you could run a SQL Profiler trace to see where connections are coming from. If you use the Audit Login filter, it will show you the Application Name and you can add the Host Name as well. May give you some more information, but I don't believe it will tell you if the connection is to the IP address or the DNS name.
  6. jpopp New Member

    Yeah, while those tools have been investigated, they don't solve the DNS-or-IP configuration issue.
  7. melvinlusk Member

    Any luck with this? Have you thought about installing WireShark on the SQL box to help identify other possible sources?
  8. jpopp New Member

    Wireshark won't tell us if it's coming in by a static IP entry or if the client did a DNS lookup.
    At this point, I've added a second IP to the SQL Server IP (in Cluster Administrator). The only problem is that I want it DNS-resolvable to only the new IP. That way, if someone connects via the old IP, it means they're statically configured to the IP.
    Unfortunately, I haven't figured out how to keep the cluster from DDNS-registering itself, short of disabling DDNS entirely on the DNS servers(not an option). I suppose I could create a designated DNS server running as a secondary or some other non-updatable type and point to that for DNS on the SQL box. That way it won't have any target to update to.

Share This Page