“Please take a shower before entering the connection pool” !

Sometimes pools that we should pay attention to their "hygiene" are not only swimming pools. We should also be careful about our database "connection pool"s.

Here comes the story behind this "philosophical" entrance  ...

One of our customers was reporting an issue when they were about to put their web server farm on production.  Strangely they were receiving an error message about the exhaustion of connections in the connection "pools" even though there was no traffic coming out of the internet yet. The error messages were coming into EventLog with a call stack like that :

"An unhanled exception has occurred"

Exception information:

    Exception type: InvalidOperationException

    Exception message: 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 .

Request information:

    Request URL: https://private.somedomain.com/somefolder/Default.aspx

    Request path: /somefolder/Default.aspx

    User host address: 192.168.1.11

    User: 

    Is authenticated: False

    Authentication Type: 

    Thread account name: NT AUTHORITY\NETWORK SERVICE

 

Thread information:

    Thread ID:  666

    Thread account name: NT AUTHORITY\NETWORK SERVICE

    Is impersonating: False

    Stack trace:    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

   at System.Data.SqlClient.SqlConnection.Open()

   at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)

By looking at the very bottom line "System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)" of the call stack, we can clearly see that the web application is storing the ASP.NET Session State information into a SQL Server. Storing the ASP.Net Sessions into a SQL Server and sharing the session info on all the web servers in a web farm is a very common scenario.

After talking to our customer, I learned that they've some number of hardware "load balancers" in front of the web servers. Customer had already checked the IIS logs and noted that all the web requests were "heartbeat HTTP requests" coming out of the load balancers.

They had defined this heartbeat URL as https://private.somedomain.com/somefolder/Default.aspx in the load balancers.

If you carefully look at the URL, the load balancers had been pointed to an .ASPX page. Since it's an APS.Net page, the web servers were trying to create a ASP.Net Session and store the session info in SQL Server for those "heartbeat" requests.

Since the load balancer's HTTP client implementation (think about like there's a web browser in the load balancer firmware) was not accepting HTTP Cookies ,  all the requests coming from the load balancers had been thought a "new session" by the web servers.

Additionally, load balancers had been configured to check the web servers in 2 second periods. Of course as the number of such web requests arise in the time, the connection pool was getting "full".

IMHO, it's not the fault of the load balancer HTTP client implementation. I don't think that a load balancer HTTP client implementation has to implement all of the HTTP protocol . They might not accept cookies,  they don't have to be as powerful as Microsoft Internet Explorer. HTTP code in the load balancer firmware is only there for just a simple "heartbeat" HTTP check. All other additional features would be an overhead for a load balancer.

Long story short, we needed to find a way for web servers to not to create an ASP.NET Session for the requests coming out of the load balancers.

By relying on that idea, we've put a static HTML file (loadbalancertest.html) to somewhere in the web servers and configured the load balancers to check the URL pointing to this new static HTML file : https://private.somedomain.com/somefolder/loadbalancertest.html and this resolved the issue.

Now the connection pool created for ASP.Net Session Storing mechanism is dealing with only the correct connections coming from correct web requests.

Happy swimming :)