Concurrent User limitation?

Hi folks -
I have an application that runs a SQL database. I am using the MSDE to
access it. Client machine has the proprietary software and MDAC v 2.8
installed on a Win2KPro workstation that is fully patched. The support team
for my proprietary application is telling me that using MSDE instead of SQL
Server will give me a concurrent user limit of around 10 or 11.
I've been running this application for almost a year and have not run into
this until I recently instituted Group Policy settings (although I don't see
where the changes to Group Policy would have a bearing).
Sometimes the workstations can access the application, other times within
the same day, they receive an error stating that "the server does not exist
or access is denied", yet they can restart and get right in.
Does anyone know if there IS a concurrent user limitation using MSDE to
access a SQL application?
Thanks.
Deb Landrum, AAM, AIT
Insurance Alliance, Houston TX
HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
On Tue, 6 Sep 2005 14:29:13 -0500, Deb Landrum wrote:
(snip)
>Does anyone know if there IS a concurrent user limitation using MSDE to
>access a SQL application?
Hi Deb,
Well - sort of.
There is no real limit to the concurrent user connections, but there is
a limit to the number of concurrent workloads. If a connected user
spends most time entering data on the form, and only a fraction of the
total time actually executing queries, you can serve quite a few users
without ever running into problems. On the other hand, one single user
can spawn multiple concurrent workloads at once (though it's not
trivial).
The limit on concurrent workloads is set to 8 workloads, but since SQL
Server needs 3 workloads for it's internal processes, there are 5 left
for the users. Once you exceed the workload limit, you won;t get any
errors, but the overall performance of all workloads will be reduced.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi,
Deb Landrum wrote:
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE
> to access it. Client machine has the proprietary software and MDAC v
> 2.8 installed on a Win2KPro workstation that is fully patched. The
> support team for my proprietary application is telling me that using
> MSDE instead of SQL Server will give me a concurrent user limit of
> around 10 or 11.
> I've been running this application for almost a year and have not run
> into this until I recently instituted Group Policy settings (although
> I don't see where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times
> within the same day, they receive an error stating that "the server
> does not exist or access is denied", yet they can restart and get
> right in.
> Does anyone know if there IS a concurrent user limitation using MSDE
> to access a SQL application?
there's no upper limit but 32767 connections to an MSDE instance but, a
built-in Workload Governor will kick in when 8 concurrent workloads of a
certain type are running... this will result in a linear delay applied to
all concurrent batches in the I/O activities.. when the number decreases
under the 8 limit, the governor will go sleeping again, waiting for next
"overflow" to kick in again... that does not mean 8 concurrent users is the
maximum allowed without the governor to kick in, as usually lot of
connections will be "sleeping", just waiting for user intervention to do
something... Microsoft released a paper "estimating" a magic number of 25
concurrent users before the overhead will be noticeable to end users, but
this just is a "magic number"... it really depend on the application type,
access mode, activities against the data, data load and so on... as you may
understand, there's no actual documentation about this governor but the
papers as
http://msdn.microsoft.com/library/?u...asp?frame=true
...
but I think this is another story, as the exception you get is not related
to all this matter...
this error is a generic MDAC related exception, as you can see reading
http://support.microsoft.com/default...06&Product=sql ,
and probably depending on problem in your LAN...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi
To add what Hugo and Andrea have commented on, there is a limitation at OS
level. You are running w2k Professional which is a workstation product.
Currently the workstation products have a limitation of 10 simultaneous
connections from other machines. In effect, on your platform, you are
limited to 10 connections to MSDE. This limitation is in place to ensure
that workstation products, which are not optimized to be application or
file share servers, are not used as servers.
If you install MSDE on a server product, this limitation goes away.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
> team
> for my proprietary application is telling me that using MSDE instead of
> SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
> see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
> exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||Thanks to all for your input - the MSDE is on a server product, and, the
resolution was actually an issue within the SQL application that we are
running. For whatever reason, although the server name was properly and
correctly shown for the workstations to access it, the problematic ones were
receiving the error. However, by substituting the IP address of the server
for the actual server name, those same workstations were once again able to
access the application.
I don't know if this would be a particular weakness in the proprietary
application that we're using or not, but, again, Thank You to all for
replying.
Deb
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
team
> for my proprietary application is telling me that using MSDE instead of
SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||hi Deb,
Deb Landrum wrote:
> Thanks to all for your input - the MSDE is on a server product, and,
> the resolution was actually an issue within the SQL application that
> we are running. For whatever reason, although the server name was
> properly and correctly shown for the workstations to access it, the
> problematic ones were receiving the error. However, by substituting
> the IP address of the server for the actual server name, those same
> workstations were once again able to access the application.
> I don't know if this would be a particular weakness in the proprietary
> application that we're using or not, but, again, Thank You to all for
> replying.
you probably have a Name resolution problem in your LAN... let your Net
admin gain his money :D:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply