I've a few questions regarding connection pooling.
I currently have an SQL Server 2000 backend (using five databases), and a VB exe frontend. I'm looking to distribute the application to small usergroups of between 5-15 people, using MSDE in place of the full blown SQL server (for obvious cost reasons).
MSDE has a concurrency manager that limits the number of user connections (e.g. SPID's over 50) to 5, before performance starts degrading.
I've (sort of) come across connection pooling; really there is not a huge amount of information on the 'net to research this topic, but what I have discovered is that multiple users can use the 'same' connection to access data from source.
In my scanario, I'm looking to implement restrictions on the connection, so that a user will have to wait for the connection to become available before executing his/her request.
For example Joe says "Can I have all records from Employees", Mike says "Can I have all records from Sales" and Dame Wantalot Mountsarehuge III says "I want all records from Orders".
Because all three requests need to use the one connection, if the requests were submitted in the order as per the above, Joe would use the connection first, have his results returned, Mike would use the connection second, and the Dame would have to sit and want, till Mike had finished.
I'm thinking of doing the following to achieve this:
- set up a module in my project that opens and shuts one connection to my SQL sever at a time (well, one connection for each database)
- set up all functions that use ado connections to request one from the above module.. if it's in use, wait until it's available.
1) Does connection pooling acheive what I want to do, as in the scenario above?
2) Am I even thinking along the right sort of lines?
3) What methodology would you suggest to acheive my aim?
3) Am I trying to reinvent the wheel?
4) Does my hair really look bad when dipped in peroxide?
I look forward to your comments / discussions..
Start Free Trial
