Access 2000 or SQL Server 2000

[Denis]

Hi

I am planning to design a database (destined for the web) that will have
between 20000 and 45000 records in it and will receive a lot of reads but
very very few writes (just from me).

Now the question is should I use:

1) The combination of Access 2000 (accessible through ASP or ASP.NET) using
OLEDB Jet

or

2) SQL Server 2000 ?

Of course I know that the SQL Server option is a better one, especially
since it's said that an Access DB can have about 9 users or so accessing it
at the same time. But is that a general rule or is it about many people
writing to the DB? In other words, if an Access DB has hardly any writes
and 99.9% reads can it be used as efficiently as it would be used on SQL
Server 2000?

 

[Nicole Calinoiu]

Denis,

For mostly read access, a properly optimized local Jet (Access) database
should offer performance similar to that of a local SQL Server database, and
better than that of a SQL Server database on another machine.

That said, the number of concurrent connections present a hard limitation
with Jet. For mostly read access, you can definitely exceed 9 concurrent
connections. The hard upper limit is 255, but it's very difficult to reach
in practice. Even with mostly read access, 50-100 concurrent connections is
a much more realistic goal. This doesn't mean that Jet would necessarily be
unsuitable, but you might want to put more weight on your answers to the
following questions than on the performance issue:

1. How many concurrent users do you expect?
2. How would you rate your skills in sufficiently optimizing a Jet database
for this type of access? (i.e.: How much time are you going to lose with
this?)
3. How difficult and/or time consuming would it be for you to convert to
SQL Server if this becomes necessary?

In particular, compare the likely costs of #2 and 3 to the cost of
immediately implementing SQL Server instead. You might find that it's just
not worth considering Jet at all...

HTH,
Nicole