Load Balancer and Connection Pooling

Guys,

We have 2 main database servers, with our clients accessing either one DB or another via a website click. Our application, in the background, chooses randomly, which server to connect to. This approach achieves simulated load balancing.

For efficiency purposes, we also have the application manage connection pooling.

Recently, we realized that our current "simulated load balancing" is not good enough, as it blindly chooses which server to connect to, without considering the current workload of that server. We are now considering to acquire a load balancer [i.e. hardware piece] so that the load is balanced more wisely, by it. Our only concern is connection pooling. Will connection pooling still work with the new, proposed topology [i.e. applications sit to the left of the load balancer and the db servers to the right of it, with the load balancer in the middle].

If anyone has had any experience with this and could share or give advice, it would be appreciated.

Thanks a lot!Each connection will be bound to one specific server. What you can do however is having a timeout value associated with each connection, letting it live for some 30 seconds or so. In this way you can ensure that the load is balanced, while still limiting the number of connections made to the SQL Server. Please remember to include logic to reconect if you don't get an answer on an active connection :)|||Thanks for the suggestion roac!