[WEB4LIB] SQL server vs. ms access

[Bryan Davidson]

All,

Thank you, everyone, for taking the time to send so much usefull
information.
It has been very helpful.

~~~~~~~~~~~~~~~~~~~~~
Bryan H. Davidson
Electronic Products Librarian / Webmaster
University of Arkansas Libraries, Fayetteville
Ph. 501-575-4665




> 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
>