[WEB4LIB] SQL server vs. ms access

[Richard Wiggins]

Hmmm.  I don't think there are hard and fast numbers for this question --
variations in complexity of each transaction, size and structure of the
database, and horsepower of the server, would all come into play.  For
instance, the same database structure and transaction load could perform
very differently on a 200 MHz Pentium with 64M RAM and slow IDE disks versus
a 1 gigahertz P-III with 512M RAM and SCSI RAID.  :-)

Still, it is certainly very popular to develop a database in Access, and
deploy it in SQL, with the expectation that the SQL platform is better
equipped to handle a high transaction load. I think most folks who do this
expect more or less automatically to get superior performance from SQL's
ability to handle multiple outstanding requests at once.  From what I hear
from people who do this sort of thing, Access can perform really well for
fairly large databases, and even for pretty high transaction loads, as your
experience shows.  But there's a point after which you need SQL to handle
multiple transactions at once.

The big advantage of this development model is that Access makes it really
easy to change your database (ie add fields or tables on the fly
willy-nilly) and SQL gives you performance.

One thing that complicates the discussion is that with Access 2000,
Microsoft introduced MSDE as an optional replacement for the Jet database
backend that always has been behind Access.  MSDE is an SQL-ready format and
is supposed to scale to higher performance both within Access and for quick
translation to SQL.

Here's a paper from Microsoft that may help, in that it talks about the
relatively simple transaction model of Access versus the more sophisticated
features of SQL:

http://www.microsoft.com/technet/SQL/Tools/Sqldevkt/acc2sql.asp

Here's a piece that talks about the Upsizing Wizard that's intended to
automate the migration from Access to SQL:

http://www.microsoft.com/technet/SQL/Technote/access.asp

A piece on MSDE vs Jet in Access 2000:

http://www.microsoft.com/technet/office/trmsde2.asp

/rich



------Original Message------
From: "Bryan H. Davidson" <bdavidso at comp.uark.edu>
To: Multiple recipients of list <web4lib at webjunction.org>
Sent: September 9, 2000 7:02:39 PM GMT
Subject: [WEB4LIB] SQL server vs. ms access


I've got a quick general question that I'm hopping someone out there might
be able to answer.

I've done a great deal of work using MS Access as a web application back-end
and have seen dynamic / static hybrid sites run efficiently that receive up
to 40,000 + hits per month.

What I'm having a hard time finding documentation on is, at what number of
simultainous users does Access break down?

My second question is, by simply migrating an Access database to SQL server,
will a web site then automatically be able to handle high numbers of
simultainous users? Or is an overall redesign / reindexing involved in order
to truly optimize a database?

Thanks

Bryan H. Davidson
Electronic Products Librarian / Web Master
University of Arkansas Libraries

Richard Wiggins
Consulting, Writing & Training on Internet Topics
www.netfact.com/rww         wiggins at mail.com
517-349-6919 (home office)  517-353-4955 (work)  
______________________________________________
FREE Personalized Email at Mail.com
Sign up at http://www.mail.com/?sr=signup