Server 1: SQL Server 2008 Standard Edition Server 2: Windows 2008 Server R2, IIS 7.0
A web-site on Server 2 requires data in SQL Server on Server 1.
Everything works fine for a while (weeks sometimes). Then, under heavy load, Server 2 reports it cannot connect SQL Server on Server 1. Once IIS on Server 2 reports it cannot connect to SQL on Server 1, it does not get better until IIS on Server 2 is restarted. Perhaps restarting the application pool would work as well as a full IIS reset. I'm not certain.
I've tried changing the connection string to increase the connection pool size to unreasonably large values (1,000). The failures still happen.
The web-site is written in C#. The data access layer is NHibernate.
Here is the start of the exception:
[SqlException (0x80131904): 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)] System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +428 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +65 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +117 System.Data.SqlClient.SqlConnection.Open() +122 NHibernate.Connection.DriverConnectionProvider.GetConnection() +60 NHibernate.Impl.SessionFactoryImpl.OpenConnection() +39
Any ideas why this is happening? How to diagnose/fix it? I'm frustrated and considering ripping out NHibernate, which will take months and probably lead to many other kinds of problems.
The fix for me was to configure SQL server to allow an unlimited number of connections (set to value of "0"). I still had a few connection issues as described in the original post, but no where near as much. So I also setup a direct connection to the SQL server via the secondary Ethernet port on each server (Webserver to SQL Server),and kept the connection private using 196.168.1.x between the servers, and using it's own Ethernet switch not connected to the public switch.
User contributions licensed under CC BY-SA 3.0