What is causing intermittent connections problems from web server to sql server 2008 r2?

Question

We have 4 API servers and 2 web servers that connect to a SQL 2008 R2 server. Recently we've noticed that the web servers sometimes can't find the SQL server. This doesn't happen for every connection, but it does happen several times per minute. All the servers are on Windows 2008 R2, and connect to the DB via TCP. We've verified that the DB is reachable, and in fact, the API servers don't have any problems connecting to the DB.

Because the connection problems are intermittent it is proving difficult to troubleshoot. We've verified that the firewall is not blocking the connection, and that the maximum number of connections is not being reached (max is the default of 32767, and our average connection count at any given time is around 1000).

What might be causing these irregular connection problems?

Answer 1

So these DB servers are on another network (via the firewall)? If so take a look at the ARP cache on the web and DB servers at the time the problem occurs and look at the ARP table on the firewall when the problem occurs. The web servers should have the MAC address of the firewall for the DB server's ip address in their cache, the DB servers should have the MAC address of the firewall for the web server's ip address in their cache, and the firewall should have the MAC address for the DB server's ip address and the MAC address for the web server's ip address in it's cache. Make sure that the firewall ARP table shows the MAC adresses for the servers listed on the appropriate firewall interface that each server is "connected" to. If that gets you no where then check the ARP table on the switches between the web and DB servers.

You can also put a network sniffer on the web and DB servers and start a packet capture. When the problem occurs, look at the capture on both ends and see what's happening. You should see traffic from the web servers toward the DB servers (toward the firewall) and from the DB servers to the web servers (toward the firewall). If you do then it's a network problem, if you don't then it's a server problem.

---

EDIT

Also, you can look for any RST packets coming from either end of the connection in your network capture that might be coming from the firewall due to connection timeout settings on the firewall.

Answer 2

I'd suggest looking at the NIC statistics -- mainly error counts, whether there's any offloading to a TOE module happening, and whether the web servers' link to the switch (or another net device) is getting saturated. Also the output of 'netstat -ano' -- it's possible you'll get errors if the entire ephemeral port range (49152-65535 by default on Windows 2008) is in use on the web servers.

Being intermittently unable to connect to SQL can have myriad causes from hardware to connection pooling problems, so if you could post the error message that's being logged or displayed (assuming there is one), that might give people a better idea of what's wrong.