Connection Pooling Logic

We have our own home grown connection pooling method that is not too
resilient.
I want to know how you handle connections to a database i.e how may
connections do you open as your Web/App tier starts up to the database, what
is the max that you allow ?
Also in the event SQL Server is slow, how do you ensure that the Web/App
tier does not open more connections ?
We have been struggling with this today as our Web/App tier does not know
better when to back off or just open new connections if there is more load.
ThanksHi
You connection pool should open a new connection when there are no free
connections, therefore if the server is not responding well connections are
going to remain in use longer! If you define a maximum pool size this will
give you an upper limit rather than creating additional connections and
potentially exacerbating the problems. The application should handle the
error generated when new connections can not be created.
Alerting when the pool is at it's upper limit should inform you when the
system is having problems although other alerts on CPU/Memory used may be
triggered first.
John
"Hassan" wrote:
> We have our own home grown connection pooling method that is not too
> resilient.
> I want to know how you handle connections to a database i.e how may
> connections do you open as your Web/App tier starts up to the database, what
> is the max that you allow ?
> Also in the event SQL Server is slow, how do you ensure that the Web/App
> tier does not open more connections ?
> We have been struggling with this today as our Web/App tier does not know
> better when to back off or just open new connections if there is more load.
> Thanks
>
>

Connection and SERVER

Hello Gurus out there!

I want to know what would be the best method in opening a connection to server:

1. a connection (declared globally) that is open once during Login and access thru all forms and be closed only when application is terminated
What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

2. a connection is opened only when needed but everytime i execute a query against the database i have to open also that connection and terminate when it is not used...What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

Secondly, how can we know the resources used by the users that are connected to my SQL SERVER in terms of memory usage and CPU?

Im using VB/FOxPro and I want to know the best practice in terms of opening a connection to the database coz Im expecting to have 20 or more users online simultanously and hook to my server as soon as we are finished with our system.

I hope you can light up our minds with these concerns.

Thanks,

bernieIt is most common to maintain a connection for the life of a traditional application. This is because "connecting" takes time, and keeping a session alive (that isn't doing anything at the moment) usually doesn't.

I'm not saying that this is the only way nor the right way but it is commonly done, at least for non-web-based apps.|||Originally posted by sundialsvcs
It is most common to maintain a connection for the life of a traditional application. This is because "connecting" takes time, and keeping a session alive (that isn't doing anything at the moment) usually doesn't.

I'm not saying that this is the only way nor the right way but it is commonly done, at least for non-web-based apps.

Are you trying to say then that I would keep a live connection as long as application is running? Yes, im aware the connection to server takes a quite time thats why i hesitate using On/Off connection because im expecting to use queries frequently in our app esp we are developing an integrated accounting system...

So i would pressume that the SQL Server can accomodate that 30 connections live simultaneously and can execute the same speed to all users...Am I right with my presumption?

THANKS Sundial!!!