How to limit concurrent users when using pooled connections?

Hi,
I've been out of the loop for a while but when I learnt about db access from
VB etc we were told to use the same connection credentials such as username
and password in order to speed up access by recycling connections. Nowadays
I want to host a web site with some sort of database at the back end and I
would like to use MSDE but there is a 25 concurrent users restraint on the
license.
How would I limit the users if I'm using connection pooling? Is this
something I'd have to do in code?
Thanks in advance.
hi,
meadensi wrote:
> Hi,
> I've been out of the loop for a while but when I learnt about db
> access from VB etc we were told to use the same connection
> credentials such as username and password in order to speed up access
> by recycling connections. Nowadays I want to host a web site with
> some sort of database at the back end and I would like to use MSDE
> but there is a 25 concurrent users restraint on the license.
there is no restrincions on licences at all..this 25 magic number is just a
general "guess" about the "potential" limit of MSDE, as it allows up to
32765 theoretical concurrent connections (as all SQL Server editions) but it
has a built in Query Governor ( more at
http://msdn.microsoft.com/library/?u...asp?frame=true )
that kicks in when 8 concurrent workloads (AKA batches and not connections)
are executing, slowing down all active workloads...

> How would I limit the users if I'm using connection pooling? Is this
> something I'd have to do in code?
as you are writing a web base application, probably built on IIS, you can
for sure use the connection pooling features, as the application server
probably uses the very same connection string each time (and this is enougth
for reusing pooled connections), but disabling the pooled connections
(providing the "OLE DB Services = -2" parameter in the connection string,
which disables connection pooling only...) could not increase your
performance/access strategy, as the wall still is at 8 concurrent workloads,
with no regard to the actual connection owner... and querying your
master..sysprocesses table for the actual current count only adds additional
workloads to your (scarce) resources...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea. Very good and helpful answer which has reminded me of why the
MVP programme is a good idea (I once pondered trying to earn MVP status
myself, in Excel!)
Cheers,
meadensi
"Andrea Montanari" wrote:

> hi,
> meadensi wrote:
> there is no restrincions on licences at all..this 25 magic number is just a
> general "guess" about the "potential" limit of MSDE, as it allows up to
> 32765 theoretical concurrent connections (as all SQL Server editions) but it
> has a built in Query Governor ( more at
> http://msdn.microsoft.com/library/?u...asp?frame=true )
> that kicks in when 8 concurrent workloads (AKA batches and not connections)
> are executing, slowing down all active workloads...
>
> as you are writing a web base application, probably built on IIS, you can
> for sure use the connection pooling features, as the application server
> probably uses the very same connection string each time (and this is enougth
> for reusing pooled connections), but disabling the pooled connections
> (providing the "OLE DB Services = -2" parameter in the connection string,
> which disables connection pooling only...) could not increase your
> performance/access strategy, as the wall still is at 8 concurrent workloads,
> with no regard to the actual connection owner... and querying your
> master..sysprocesses table for the actual current count only adds additional
> workloads to your (scarce) resources...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>