max # of concurrent users: access backend versus sqlserver

[Mike]

I'm considering upsizing my small access backend to sqlserver and continuing
to use the same access front end. How many more concurrent users can I
expect to
support by using SqlServer?
I really wouldn't expect a big gain in the number of users that can be
supported.
Currently we support about 20 users on the access back end.

Mike

 

[Tom van Stiphout]

The theoretical maximums are MUCH higher than 20. The practical
maximums depend on your application architecture, network
infrastructure, and your programming skills/experience. It is
certainly possible to support 20 users on a pure Access FE/BE.

Upsizing to SQL Server does not necessarily increase performance, but
a well programmed Access FE, SQL BE can accommodate more users and
attain higher levels of performance.

What specific problems are you having?

-Tom.
Microsoft Access MVP

 

[Mike]

My recommendation is to stay with the access backend. I'm a .net programmer
that may soon own this application. The complaint is that data has become
corrupt a couple times a year (they seek reliability because they don't want
to reenter a days work). I question the back up plan; turns it is backed up
hourly so this should have never happened. I suspect the application needs
some tuning; there are table design issues as well. To rephrase my question:
what would be the real world expectation for the gain on concurrent users by
moving to SqlServer? If I can run 20 against Access, can I run 40 against
SqlServer? I'm skeptical

 

[Bob Larson]

You can run way more concurrent users than 40 against SQL Server. In fact
thousands. What the limiting factor appears to be is that the frontend
seems to be shared, does it not? It really needs to be set up so that EACH
user has a copy of the frontend on THEIR machine. Sharing a single
MDB/ACCDB file from the server is NOT the way to go in a multi-user
environment.

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[Norman Yuan]

Access (Jet DB): as low as 1 user, and as high as 255;
SQL Server 1 to thousands (if not hundreds of thousands);

It all depnds on how the db is structured, how the data is accessed and how
the hardware conditions is. The only thing you can safely to say is Access
DB can only allow 255 concurrent users theoretically, and it is usually far
less than that number.

Even with small number of users, bad DB design, bad data access design could
still bring down SQL Server to its knee.

 

[Albert D. Kallal]

I'm considering upsizing my small access backend to sqlserver and
continuing
to use the same access front end. How many more concurrent users can I
expect to
support by using SqlServer?
I really wouldn't expect a big gain in the number of users that can be
supported.

You can scale to 1000's of users. There are access applications with 1000
uses at the same time. The limitations becomes that of sql server, and
pretty much comes down to how much money you willing to spend on the size of
the server farm you setup....

 

[Mike]

So I can run thousands of concurrent users, each with a local copy of the
front end while maintaining a direct connection to the backend from the
access front end?
This is where the .net data set typically shines

Aren't we still using the Jet engine in this scenario as the data still
needs to be pulled to the client? Is the LDB file and issue in this scenaro?
Often, that is what corrupts when using the access back end. I'm not using
an ADP either

 

[Arvin Meyer [MVP]]

I'm considering upsizing my small access backend to sqlserver and
continuing
to use the same access front end. How many more concurrent users can I
expect to
support by using SqlServer?
I really wouldn't expect a big gain in the number of users that can be
supported.
Currently we support about 20 users on the access back end.

The last major system I wrote, had 53 concurrent users, using 75 total
instances of 12 different database front-ends against 1 set of data tables
the size of which was approximately 110 MB. Of the 53 users, 8 accessed via
an asp application, and 6 accessed 15 instances over a terminal server. The
last corruption was more than 5 years ago when a bad air card corrupted the
database 5 times in 5 consecutive weeks.

Successful databases have good relational design, good indexing, and only
pull the records one needs for the operation at hand. Indexes make the
database. A well-designed query brings the entire primary key index over the
network, then goes back and fetches only those records which are requested.
That is significantly faster than dragging all the records over the network
(like many think is done) or doing a table scan in SQL-Server. Indexes are
the key to great performance. Lack of well-conceived indexes can destroy
performance on any database.

Others have mentioned the statistics for both engines. My experience is that
one could probably support up to 100 users and between 500 and 800 MB of
data. But before I reached those numbers, I'd look to using a SQL-Server
back-end. I saw you mention that you were a .NET programmer. Database
structure is a whole different beast than most programming and if you
understand programming concepts, that's where I would concentrate my energy.
Access is considerably more efficient at front-end design than .NET for the
simple reason that it was designed to do databases. It has the events and
properties needed to make database programming sing. If you are considering
..NET, it should be because you plan on using a browser as a front-end on the
Internet, not if you are supporting a LAN. Browsers are thin clients. Access
is a very rich database client.

 

[Tony Toews [MVP]]

So I can run thousands of concurrent users, each with a local copy of the
front end while maintaining a direct connection to the backend from the
access front end?
This is where the .net data set typically shines

With SQL Server, yes you can have thousands of users world wide.

Is the LDB file and issue in this scenaro?

No, the LDB file is not used when the BE is SQL Server.

Often, that is what corrupts when using the access back end.

How have you determined that? The LDB file is just used to record
system names and for phantom locking. It shouldn't ever be corrupt.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

The LDB file is just used to record
system names and for phantom locking. It shouldn't ever be
corrupt.

The workgroup file can corrupt, though, as it's only a special kind
of MDB file (you can open them in the Access UI if they are not in
use).

 

[Sylvain Lafontaine]

You have two things to look at here: first, the speed of your application
when there is only a single user and second, the speed of your application
when you are adding more and more users.

There is a much greater independancy between the speed of your application
and the number of users with SQL-Server than with Access. If your
application is fast enough for one user on SQL-Server, then probably it will
be fast enough for 20, 200 or 2000 users and if it's no longer fast enough
at some point, you have the possibility of increasing the power of the
backend machine to increase it again.

This is not the same thing with Access: if your application begins to slow
down at say 20 or 40 users, the adding more users will rapidly make it more
and more sluggish and increasing the power of the backend machine will
practically change nothing to that.

With SQL-Server, if you double the power of the server than you can
practically double the number of users running your application. With
Access, doubling the power of the file server will practically change
nothing to the maximum of users who can run your application at a decent
speed.

Of course, there are things like the speed of the local network and the
power of each user machines and the possibility of record lockings that we
should take into account but you get the idea.

 

[Arvin Meyer [MVP]]

While everything you say is absolutely true, Access databases tend to be
less well managed. I've found apps with data more than 3 to 5 years old
which will never be used again. For instance sales records and warranty, and
service records which are years' old. Old costing and purchasing records
which could be summarized with a single records replacing thousands of them.
Then there's the records which were marked for deletion years ago. If the
database is archived and a new one created and the truncated tables
imported, as much as 2/3rds of the space can be reclaimed, and the database
will be peppy once again. This is true of every database file even server
based databases.

When it isn't true, and the number of concurrent users is slowing down the
database, the only good choice at that point is to move the back-end to
SQL-Server, and move the intensive processing of large numbers of records to
Stored Procedures.