SQL Server Name Resolution Troubleshooting

By: Tim Cullen   |   Comments (5)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > SQL Server Configurations

Problem

A colleague recently said, "With all the legacy systems, modern systems, and 'spokes in the wheel' in this environment, we should celebrate whenever we actually get data to the client." No doubt there are many pieces to the puzzle that comprises data delivery to internal and external clients, but knowing about every facet of an N-tier architecture application can be challenging. So what are the truly important things a DBA should know about to ensure data gets delivered? Sure, there are the usual technologies like replication and database mirroring, but one of the most important systems that make enterprise applications work is server name resolution.

Solution

So what exactly should a DBA know about name resolution? In a nutshell, a DBA and others responsible for applications should know how to troubleshoot it. Most application programmers use host names in connection strings, since the IP address can be modified easier in the DNS record than it can on the host itself. Also, many companies use a DNS alias for a connection string to either refer applications to a server farm or as an "abstraction layer" for disaster preparedness. In any case, name resolution plays a vital role in the application serving the client.

Name resolution is the association between an Internet Protocol (IP) address and a user-friendly computer name. The most familiar form of naming is called the Domain Name (Naming) System, or DNS. If it weren't for DNS, we would have to remember the IP address of every website we visit. Remembering http://www.microsoft.com/ or http://www.mssqltips.com/ is much easier than 10.72.49.210 or 172.16.198.4. With the impending implementation of IP Version 6, IP addresses will be even harder to remember.

When looking at host names, there are at least two names associated with a host: the host name and the Fully Qualified Domain Name, or FQDN. The Fully Qualified Domain Name is created by attaching a DNS suffix to the host name. Let's say we have a SQL Server or server farm at MSSQLTIPS with a host name Support. The Fully Qualified Domain Name of that SQL Server would be support.MSSQLTIPS.com. A host can have a number of Fully Qualified Domain Names in DNS. This is one of the methods used to resolve names on hosts that host numerous websites.

Name Resolution and The Host

1. The host first determines whether the name in question is its own name. In addition to the host name given to the host, two other identifications are associated with the host - the name LocalHost and the IP address 127.0.0.1. The latter is considered a loopback network address used to troubleshoot the network interface card (NIC).
2. A Hosts file, located on each host, is queried. In the early days of networking, and to a lesser extent now, the file (located in %SystemRoot%\System32\Drivers\Etc folder) was used as the primary method for name resolution. As enterprises grew and more machines used, the task of manually updating the file on each host became unruly. This lead to the development and implementation of other technologies such as Windows Internet Naming Service and DNS. These files are mainly used for Disaster Recovery and fault tolerance in today's environments.
3. DNS Servers are queried. Whether through the Dynamic Host Configuration Protocol or through static entry, most hosts are assigned DNS Servers that can be queried. If the DNS server does not have an immediate answer to the query it can send the request to another DNS server for resolution.
4. As a last result, a host can use NetBIOS to try and find a host. This is the least efficient methods that requires the equivalent of a broadcast.

Tools A DBA Can Use To Troubleshoot Name Resolution

Since the DBA group is one of the first groups to be contacted when an application fails, name resolution can be quickly eliminated as a possible cause of application failure. The first and easiest way to check name resolution is to PING the host using only the hostname if you're local to the host (instead of the Fully Qualified Domain Name). This will test how easily the name can be resolved from your workstation. On occasions IP addresses are changed on the host, but the DNS entry is not updated either because of latency or because the host was not able to update its own DNS record (certain types of DNS providers do not allow a record to be automatically updated).

Another method for checking name resolution, at least from the domain perspective, is NSLOOKUP. NSLOOKUP is a command-line utility that allows, among other things, lookup of a fully qualified domain name based on either the host name or IP Address. Open a command prompt and type NSLOOKUP:

In this case NSLOOKUP does not have an authoritative DNS server (the workstation is not in a domain); in most cases the default name server assigned to your host will appear. Once the server information is received there are many options available, but since we are discussing name resolution, the only thing you need to do from here is type either the IP address or hostname of the server you wish to check. In this case we are checking the DNS information for "support.microsoft.com":

If you suspect that name resolution is causing issues, you can look at a few DNS settings, depending on the method of IP addressing your company uses. There are companies out there that use DHCP for server IP assignment, so to check the DNS information of the server open a command prompt and type "IPCONFIG /ALL":

You can see that DHCP is enabled and that the DNS Server is 192.168.0.254. It could be that the host is directed to an incorrect DNS server. Unfortunately, if DHCP is enabled you cannot make changes to this setting. If the DNS server has changed since the address was obtained (as evidenced by the "Lease Obtained" area), you can attempt to obtain updated information by running "IPCONFIG/ RELEASE" and "IPCONFIG /RENEW" from the command window. This should only be done when working from the host's console, otherwise you will lose connectivity with the host.

If your server is assigned a static address, you can view and potentially modify the properties assigned to the Local Area Network (LAN) properties. To get to the properties of the LAN connection:

Right-click My Network Places and choose Properties on your desktop:

"My Network Places" does not automatically appear on the desktop. If it is not present, open the Control Panel and double-click "Network Connections"

Right-click the LAN connection in question and choose Properties:

Navigate to "Internet Protocol (TCP/IP)" and click Properties:

This screen gives you information on the current IP address settings, as well as the DNS Servers the host uses. An additional DNS server can also be added here:

Click on the "Advanced" button and then the "DNS" tab to view additional information on the LAN connection:

Here you can specify additional/different DNS servers, add additional DNS suffixes that can be appended to the host name, and a static DNS suffix for that connection. Multiple DNS suffixes can be used in instances where a company has multiple domains.

Next Steps

• IP configuration changes should be made at the host console, not remotely. This is so you can maintain communication with the server in the event the configuration information is mistyped or otherwise incorrect. In addition, most companies have individuals or teams that handle network configuration. Changes should not be made to the IP configuration unless authorized or approved by the network team (they may choose to make the changes themselves)
• DHCP configuration can be assigned via Active Directory Group Policy as well, so you may have to contact the Active Directory team for IP configuration changes. In addition, any manual entries in the LAN properties may be ignored if DHCP information is obtained via Group Policy
• Read about the Domain Name System and the Windows Internet Naming Service as implemented in a Microsoft environment
• Review the sequence in which Microsoft Windows clients resolve host names
• Learn how to use NSLOOKUP to troubleshoot name resolution
• Use Fully Qualified Domain Names when referring to servers or server farms

About the author

Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips