Sporatic Connection issues connecting to SQL 2005 - Named Pipes vs TCP/IP issue?

Question

We have a production website that is experiencing periodic connection errors to our SQL 2005 server, several times a day during peak usage:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

We're certainly investigating other avenues, but so far we haven't seen anything out of the ordinary on the SQL side. We're wondering whether this might be a Named Pipes issue, or whether we'd see the same thing if we forced the web server to use TCP/IP instead. So my questions are:

• Anyone seen a problem like this? Most of the searching I do on this error is for people who can't talk to their SQL server at all because the surface area configuration is messed up. That's not our situation.
• What's the difference between the two? Do they do name resolution differently? These servers are not domain members, they are isolated in their own DMZs, if that changes anything.
• The internet team set up a SQL alias on the web server: "mySQLserverName - tcp/ip - xxx.xx.x.xx, 1433". Would this only be used for TCP/IP resolution, and not named pipes? Could this be part of the problem?
• If I do want to force TCP/IP instead of named pipes, what's the recommended way to do it? This Microsoft KB says I can do it by modifying the connection string. This MSDN forum thread says I can modify the "preferred order" of the native client configuration protocols. I suppose I could also disable Named Pipes on the SQL server altogether, but that seems a bit drastic, and probably not something to try on a production box.

Answer 1

Typically we only use TCP/IP in our connection strings (with a non default port number) and have no scalability issues, for both local and wan based servers.

You can FORCE the use connection to use TCP/IP by using the server=dbservername,1433 in the connection string (i.e. specifying the port number).

We usually leave named pipes open as it allows you to see the "Green" / "Red" indicator on SQL Management Studio for the server status.

I would look at the general health of the server, especially network load (connections / collisions) and RAM usage (SQL Server PerfMon counters). Sometimes you can get problems with connection pooling / release on very busy servers (typically when there is 1,000's of connections per second).

Answer 2

If this is only happening during peak times and other connection attempts from the same app are working fine then you may be running into memory contention on the SQL server or a timeout issue.

Typically Named Pipes is good for LAN communications or other fast, stable networks but it's a bit chattier (ie- more overhead) than TCIP/IP sockets so is not necessarily a good idea for slower connections such as over a WAN.

When you say that each server is isolated in their own DMZ, do you mean that you have multiple DMZ networks and each server is in a different one? If so, have your Internet team check the firewall logs for dropped / refused / failed connections. If your servers are on different subnets you might want to set TCP/IP as the preferred protocol.

Here are a couple of articles that talk about differences in the protocols:

• Choosing a Network Protocol (Technet)
• Named Pipes Provider, error: 40 - Could not open a connection to SQL Server (MSDN blogs)