Connection pooling for a rich client accessing a database directly

Question

I have a legacy application WinForms that connect directly to a SQL Server 2005 database.

There are many client applications open at the same time (several hundreds), so I want to minimize the number of connections to the database.

I can release connections early and often, and keep the timeout value low.

Are there other things I need to consider?

Answer 1

Try to use the same connection string when you create a new connection, so .Net will use one connection pool.

Dispose your connection as soon as possible.

You can set max pool size in the connection string itself to determine the max number of active connections.

Answer 2

You should consider introducing a connection pool. In the Java world you usually get this "for free" with an application server. However this would be oversized anyway if everything you care for is the database connection pooling.

The general idea is to have one process (on the server) open a limited number of parallel connections to the database. You would do this in some sort of "proxy" application (a mini application server of sorts) and re-use the expensive to create database connections for incoming connections to your app that are cheaper to create and throw away.

Of course this require some changes to the client side as well, so maybe it is not the ideal solution if you cannot accept that as a precondition.