I'm wondering about the best approach to designing a DB connection manager for a load-balanced n-tier system.
Classic n-tier looks like this:
Client -> BusinessServer -> DBServer
A load-balancing solution as I see it would then look like this:
+--> ... +--+
+--> BusinessServer +--+--> SessionServer --+
Client -> Gateway --+--> BusinessServer +--| +--> DBServer
+--> BusinessServer +--+--------------------+
+--> ... +--+
As pictured, the business server component is being load-balanced via multiple instances, and a hardware gateway is distributing the load among them.
Session server probably needs to be situated outside the load-balancing array, because it manages state, which mustn't be duplicated.
Barring any major errors in design so far, what is the best way to implement DB connection management?
I've come up with a couple of options, but there may be others I'm not aware of:
Introduce a new Broker component between the DBServer and the other components and let it handle the DB connections.
The upside is that all the connections can be managed from a single point, which is very convenient.
The downside is that now there is an additional "single point of failure" in the system. Other components must go through it for every request that involves DB in some way, which also makes this a bottleneck.
Move the DB connection management into BusinessServer and SessionServer components and let each handle its own DB connections.
The upside is that there is no additional "single point of failure" or bottleneck components.
The downside is that there is also no control over possible conflicts and deadlocks apart from what DBServer itself can provide.
What else can be done?
FWIW: Technology is .NET, but none of the vendor-specific stacks are used (e.g. no WCF, MSMQ or the like).
