Network problems with connecting to SQL Server can be daunting. DBAs have various levels of proficiency in networking. It ranges from deep working knowledge to very primitive. Here I outline some basic network troubleshooting techniques that will help you navigate the issue.
Read on to see how to troubleshoot basic networking issues at the command line.
DBAs need to have some working knowledge of networking. How much varies on what your job role is. Organizational size is also a factor as smaller companies often have DBAs wear multiple hats while large corporations are often very specialized and stratified.
Command Line Tools to Troubleshoot Network Connectivity Issues
Let’s explore the following 6 basic command line tools you can use to troubleshooting a network issue.
- telnet / ssh
It can help to know the IP address of your machine. IPCONFIG shows all TCP/IP values along with some information about DCHP, DNS, and gateways.
There are several flags for this command. Each can be seen via the help pages like this:
There is a lot you can learn from reading the help pages. The answers are usually there so get into the habit of looking first before searching or asking someone.
The flag that provides the most information is:
Do you see the IP address? It is 192.168.0.121 on the line with IPv4 Address under my ethernet adapter. You’ll notice other useful information like the IPv6, DHCP server, and DNS server
Let’s look at a few other common flags.
Running the following will release the IP address for the machine.
If you run “ipconfig /all” again you will notice that the IPv4 address is not there anymore. That is because it has been released to the DHCP server. If you try to ping the IPv4 address it will not get a response because we’ve released the address from this machine.
NOTE: the IPv6 address will still be in use
Running this flag re-establishes the TCP/IP connections on your network adapter. It will get a new IP address – either the same as before or different depending on DCHP.
Now you will be able to ping that IPv4 address and get a response!
Flush DNS Cache
Using this flag we purge all the cached DNS info from memory:
Possibly the most primitive method for testing network connectivity is to use ping. Let me ping my machine from another machine to demonstrate:
It works! You can see if there were any packets lost and the min, max, average round trip milliseconds. If you want to ping a server for an extended period of time – perhaps to monitor network connectivity over a few minutes – you can use the “t” flag:
It will ping until you stop it (usually Ctrl + C or Ctrl + D depending on your system)
The first ping we did was to the hostname of the machine we wanted to check. Now let’s try the IP address with the “a” flag. You can ping just fine with only the IP and no “a” flag; however, the “a” flag resolves the IP address to the hostname from DNS. This can be useful for when an unexpected hostname shows up on an IP address.
The nslookup command can check the name which an IP address will resolve to or which IP address resolves to a name (aka reverse lookup). This can be done either way as shown:
Here I am running through a VPN so you can see that is where it is looking.
Running tracert (trace route) allows us to see the path your packets are being send to their final destination. This can be useful to determine where in your network the problem may lie. Here is the route taken from my desktop to this blog:
Like the other commands, this can be called with either the host name or the IP address.
How to User TRACERT to Troubleshoot TCP/IP Problems in Windows
Netstat lists the active connections for a machine. It will go on for a while showing all the listening ports and connections (both incoming and outgoing). I like to run with the “a” and “n” flags.
Want to see what ports are listening? It is lised in the output. This is a way to tell which ports are listening on the server.
Want to know if SQL Server is listening on a port? You can get the port from the SQL Error Log
You can then run the following to see:
TELNET / SSH
Telnet or ssh can be used to form a connection and test. With Linux, ssh is built in. In Windows you need to download a program called “PuTTY“. These tools can be used to test a network connection on an IP address and port.
Let’s see if I can reach my website:
Network troubleshooting at the command line can bring you a lot of quick answers about your networking connectivity issues. Don’t be afraid to get your hands dirty with the command line.
We didn’t talk about firewalls, SQL Configuration Manager, or reading event logs but those are all useful things to consider. So the next time a firewall is blocking your port you will know how to discover it!
If you liked this post then you might also like: My Project: Wired House for Ethernet Cat 6