connection pool from multiple desktop apps.

cwteoh
cwteoh used Ask the Experts™
on
I have a SQL Server 2000 installed in a P4 2.4GHz server with 20 users connected into it. It there any solution that creating a connection pool for those 20 users connect to SQL server with only 1 connection. Meaning to say, I will build up a agent to get a single connection to the SQL Server and multiple connection from 20 users to the agent.
using this method, how many SQL Server Connectivity licenses required? One or still 20?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
If all of the 20 users share one connection via a pool of one, then you will only need one license. The 20 users will of course have to connect to the agent not to SQL server. SQL Server is only concerned with concurrent connections from a licensing point of view.

Author

Commented:
but I don't know how to build an agent. Will you give a sugession?

Commented:
Well, it's a complex peice of code, not fot the faint-hearted or novice. The best choice would be to use C# or VB.NET as these languages have the class libraries for most of the code that you would have to write. The design is basic multi-threaded server with a socket listener (use .NET remoting) with each thread serializing the use of a single SQL Server connection. Use mutexes for this.
Most of this type of code is available on the microsoft web-site (msdn.microsoft.com).
The tricky parts of the code are in the error-handling, time-outs and thread-synchronisation. Very complex stuff. You would be better off buying the extra licenses.

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- Points for mikemudd

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Venabili
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial