4

Background

For years I hosted all of my server functionality, including IIS, Apache, MySql, and MSSQL on a single server instance, yikes! As my client base grew this led to persistent SQL Timeouts when the server got busy. Over the past year I've reorganized this into three servers, one Windows Server 2012 for IIS + .Net 4, one CentOS instance for Apache, and one Windows Server 2012 for only MSSQL 2012. These servers are all virtual instances in the RackSpace cloud, and appear to be running smoothly except for one thing...

In the old days I got used to seeing database communication errors like this...

SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

or

System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

I understand these and have dealt with them, I generally don't get them anymore.

The Problem

What I do get is a whole different kind of Timeout errors, that appear to be network related, such as...

[SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)]

and

[Win32Exception: The specified network name is no longer available] 
[SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)]

and

System.ComponentModel.Win32Exception (0x80004005): The semaphore timeout period has expired
System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) 

and

System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

and

System.ComponentModel.Win32Exception (0x80004005): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

Perhaps part of the difference is that IIS8 is giving me more specific error messages than previous versions (is it?)... but several of these error seem to be basic communication issues between these two windows server 2012 instances. I have also noticed some anecdotal evidence, that my FTP client periodically fails to connect to the web server, and windows network shares seem to disconnect intermittently.

So I'm stuck in a place where I have a well oiled machine that will inexplicably fail me several times per day. Since I'm more of a developer than a DBA or server admin... I'm baffled.

My connection strings are always something like this...

SERVER=LiveDatabase;DATABASE=DataBaseName;UID=userid;PWD=pwd;Pooling=true;Min Pool Size=10;Max Pool Size=500;Connect Timeout=10;

where LiveDatabase is an Alias setup in the machines SQL Server Native Client Configuration. The Alias then points to a local IP Address in a RackSpace private network 192.168.x.x, and a non-standard port where the server is expecting connections.

These "timeouts" are happening on seemingly random stored procedures, including procedures that have little or no overhead for the database.

0

All the issues seem to be pointing to network connectivity issues but checking the following should reveal some clues.

Check and confirm the following:

  • Verify If correct ports are open (1433)
  • Check database user permissions
  • Try a continuous ping to/from the SQL server
  • check Virtual Nic's power/sleep options
  • See if RackSpace can provide you with any logs for NIC's, network communications between servers[Ports, subnet etc]
  • Check Windows logs and SQL server logs
  • Turn on activity monitor on the SQL MGMT Studio then try to reproduce the error (running application?) and monitor the "Recent Expensive Queries" in the activity Monitor

Not the answer you're looking for? Browse other questions tagged or ask your own question.