Access database: How big is too big?

  • Thread starter Thread starter Clayton Sutton
  • Start date Start date
C

Clayton Sutton

Can someone point me to some docs or a white paper on the max size of an
Access DB before you should move to SQL? I know that there is no "Hard"
number, but I need to know about the max size.


TIA,

Clayton
 
Physically the max size of an access database is 2GB, but I don't think that
is what
you are after(you could always get aroud that by spliting your database
across
multiple files). As for a practicle maximum size, I would say that has alot
to do
with your application, available hardware and the like. I don't think that
there is
any definitive set of rules. I have never seen Microsoft publish anything
on this.
If this is important, especially for a new system, you might dummy up a set
of data
about the size of your application and do some performance tests with typical
operations to see how well it does.

Brian Scanlon
 
Can someone point me to some docs or a white paper on the max size of an
Access DB before you should move to SQL? I know that there is no "Hard"
number, but I need to know about the max size.
Click to expand...

I've known of users who migrated databases to SQL when they had 10,000
or so rows; I know of others who are happily using Access with
20,000,000 rows in the largest table. There IS no "size cutoff"; in
general, this isn't a decision which should be driven by data size.
Instead, you need to look at security, fault tolerance, mission
criticality, user expectations, network speed and stability, user
hardware, number of concurrent users, and a host of other factors,
many of which are much more important to the decision than just size.


John W. Vinson[MVP]
 
You mean the product "SQL Server", not the data access language "SQL".
Access/Jet already uses SQL.

As I recall, Access/Jet has a 2gb limit for Jet 3.x and a 4gb limit for
Jet 4.x. What this means in "number of records", depends on the
structure & content of the tables.

Remember that Access/Jet works amazingly well, even with enourmous
tables, as long as everything is indexed correctly. By far the most
common cause of poor performance, is incorrectly indexed tables, or
badly written SQL.

The reasons for upsizing to heavier-duty back-end databases, such as
SQL Server, Oracle, etc., are IMHO more to do with reliability, hot
backups, true atomic transactions, and so on - not because the tables
have got "too big".

HTH,
TC
 
Back
Top