Connection pooling is a great feature in the native SqlClient connector. For you who have not heard about it here's an introduction. Which you can obviously skip if you've heard about it ;-):
SQL Connections are an expensive resource
Based on your connection string SqlClient will connect to a given Microsoft SQL Server using sockets, named pipes or whatever. This is an un-managed resource which means that it must be explicitly opened and closed; this has to be done since the server has a limit for how many connections you can keep alive at once. This pretty much depends on the physical limitations of the server; memory, CPU etc.
During the lifetime of your request you might have several connections opened and closed to the server. Under the covers this takes some time. The client must establish a TCP/IP connection to the server, handshake, authenticate etc.
SqlClient keeps a "managed" pool of connections by default
When pooling is enabled your code does not immediately connect to the SQL Server instead it attempts to get a connection from a pool of connections. When you call the Close() the connection will be returned to the pool, ready to be used by the next request. The maximum number of available connections held by the pool is determined in your connection string, by default it is set to 100. This is the absolute MAXIMUM of simultaneous connections that your app can handle, if all these connections are "leased" at the moment the client has to wait (Connection Time Out, default 15 seconds) until one becomes free. When the timeout expires the client will be greeted by this nightmare exception:
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.
When this happens there is really not much more to do but wait and hope that the garbage collector will invoke Dispose on the SqlConnection object and close the connection, however this is not guaranteed and can take some time.
You must always call Close() on all your SqlConnection objects, no matter if you are using pooling or not. Always close it before the object goes out of scope, if it does happens you will have a connection leak that you cannot heal from.
So, the trick then
My experience is that once the pool is filled up, and if the timeout expires there is really nothing else to do but create a completely new non-pooled connection for the request. This approach will make sure that you will never have connection pool overflows; the next thing that can go wrong here is if the server itself denies the connection. That is a risk I am willing to take, however SQL Server 2005 has a limit of some 32,767 simultaneous connections so as long as my connections are CLOSED I will not have a problem ( wink wink ;-) ).
Firstly I have to almost identical connection strings in web.config, one default with pooling enabled and one without pooling:
<add
name="PRIMARY"
connectionString="Data Source=VIB\SQLEXPRESS;Initial Catalog=TestPoolingDB;Integrated Security=True;Connect Timeout=5; "
providerName="System.Data.SqlClient"/>
<add
name="PRIMARY_NOPOOL"
connectionString="Data Source=VIB\SQLEXPRESS;Initial Catalog=TestPoolingDB;Integrated Security=True;Pooling=False"
providerName="System.Data.SqlClient"/>
As you can see in the PRIMARY connection SqlClient is instructed to time out if a connection is not delivered within 5 seconds.
Dim connection As SqlConnection
Try
connection = New SqlConnection(System.Configuration.ConfigurationManager.
ConnectionStrings("PRIMARY").ConnectionString)
connection.Open()
Catch ex As System.InvalidOperationException
connection = New SqlConnection(System.Configuration.ConfigurationManager.
ConnectionStrings("PRIMARY_NOPOOL").ConnectionString)
connection.Open()
'Save a logentry on this since it is not normal behavior.
End
Try
Summary
Correct me If I am wrong here but this seems like a solid solution, even if you have potential connectionleaks within your code this will buy you some time in the production environment… as long as you keep a log of the situations where the non-pooled fallback is required.
Comments and feedback on this is highly welcomed.