Need help finding limit of clients per server sharing Access database

[David]

Hello,

I maintain an application that shares an Access database.
The server generally writes data to the database and interested
clients pick up records of interest. Generally we have a small
number of clients (2-15). A few customers have used up to 40
clients. My sales manager asked today if we could handle 110
clients.

I know that Access has a limit of 255 connections. I'd like
to check the number of connections Access has open to make sure
that something dumb like a client owning a couple connections
doesn't happen. My lab currently handles about 6 clients.
The (client/server) applications are VC++ v6 and use ODBC
to access the database on a shared drive on the server.
I'd like to ask the Access Database or ODBC or something
to see how many connections are actually open for the six
clients. I expect a number like 7 (6 clients plus 1 server).
This would give me a good idea of what number of clients
Access could actually handle for this application.

I've also been unable to locate the capacity planning
information for Access. I'd read some guidelines before
but can't recall where. Our current server is W2K Server
P-III 850Mhz w/512MB RAM. I'd expect 110 clients (share
users) to perhaps need a bit more horsepower. Do you
recall a figure that mentioned a guideline for how much
memory a server should have if it is to support X share
users? Obviously we'll need to add CALs to the server
as well.

Thank you in advance,

David

 

[Larry Linson]

If you are using C of any flavor and ODBC to access the database, then you
are actually using a Jet database (that's the default database engine used
with Access), although even Microsoft sometimes calls that "an Access
database" and Access, itself, is nowhere involved. A newsgroup about C/C++
and databases would likely be a better place to get the answer you are
looking for.

You see, from Access itself, you cannot use ODBC to access a Jet database,
so Access developers aren't necessarily familiar with the performance
parameters you need.

I can say that with Access front ends using linked tables in a Jet back end
database (with no ODBC involved) there are many factors that affect
performance... requirements, design, and implementation of the database
application and the software, hardware, and network environments. In such a
configuration, we routinely see reports of 30 - 70 users even if not all
factors are near-perfect, and we have reliable reports of some cases of 100+
concurrent users, in which we assume all factors _are_ near-perfect. In that
configuration, because Jet is a "file-server" database, all database
extraction and manipulation is done on each user's machine, with the tables
database in a folder on the server used just as if it were on the local hard
drive. That is, not everything is brought across the network, but far more
than would be with a true server database.

I would be highly reluctant to give management or a client any kind of
encouragement, much less assurance, of 110 or more concurrent users on an
Access-Jet multiuser database. But, with ODBC between your C/C++ application
and the Jet database, I don't have a feel for how many users it might
handle.

In one case, a colleague has something over 200 users, but they are using a
VB app to communicate via Winsock with another VB app on the server that is
actually accessing the Jet database. And, as it is used for system error
recording / reporting, there are few _concurrent_ users. In fact, he
essentially created a true server database with VB and Jet... if there is
compelling need to use the Jet database rather than, say, SQL Server, you
might be able to do something similar.

Larry Linson
Microsoft Access MVP

 

[David]

Larry,

Thank you for the information.

I was able to find a link this morning that included a description of
JetUtils.exe produced in 1997. Its a set of tools to monitor a Jet
database users (MDB/LDB files) and includes a DLL that I may be able to
use for further investigation.

There was a monitoring tool included that lists all users. It appears
that my clients start with two database connections and then back off
to one. We do some up front tests to perhaps this is just a collision
that looks like two connections. In either case, I was able to determine
that the clients are essentially using one connection per machine, rather
than more than one. Thus I can say that perhaps 200+ clients could be
supported if the server is capable of supporting the load. It probably
can, but I'll want to rework the application to scale for larger client
loads. Just one more improvement to add to the next release or two.

Thank you,

David