.net Connection Pooling and General Network Errors
We had an interesting situation today, where we were trying to fail over a clustered SQL Server and whenever we failed over the first few data connections would return a “General Network” Error. We tried reducing the Connection Timeout setting to have them time out in the time between the failover. No good. Even a one second timeout (the minimum) would still return a connection that was a dud.
As we learned, the framework does not validate connections when they are returned from the pool. Presumably this would be because validating the connections would be an expensive operation to perform. Also, the timeout value is only inspected when you close a connection and it gets sent to the pool.
To reproduce this scenario you don’t actually need to have a clustered SQL server. The same issue can be reproduced as follows:
Write some code that does the following:
1. Open a connection
2. Do an ExecuteReader
3. Close the connection
Next, stop and start sql server. I use a local server so I find net stop mssqlserver and net start mssqlserver the quickest way to do this.
Then execute the code above.
The fix is amazingly simple. All you need to do is the following:
Try
sdrData = cmCommand.ExecuteReaderCatch ex As SqlException
cnAdo.ConnectionString = String.Format(“{0};pooling=false”, Connectionstring))
cnAdo.Open()
sdrData = cmCommand.ExecuteReaderEnd Try
Note: the fix above is simplistic, I haven’t studied the exception yet to see what more specific catch could be done.At the least, I would expect the final version to have a check for the general network error rather than catching the blanket SqlException.
The code asked to open a connection, the framework said “yup, here’s a good one” so the code didn’t throw an error. The error comes in when you try and actually use the connection. so what we do is we append “pooling=false” to the connection string and try again. If that works, your code will function, only slightly slower. If it still doesn’t work then the error will get caught in the calling method.
If you have used the Microsoft Data Access Application Blocks, this error will still occur (at leats that’s true of the version of the DAAB I am using). It’s a nice one-point fix to apply this to the DAAB.