Access vs SQL Compare Performance & Size

Access is a flat file database system and as such is very poor at performance as each time the database is accessed the entire database needs to be loaded into the web servers memory.

SQL Server is a database server and as such you usually have SQL Server on it's own dedicated server so it doesn't effect the web servers performance. Also it doesn't use a flat file so doesn't require the entire database loaded into memory.

Access is fine for very small projects but as a flat file suffers from lots of draw backs, including, size limit, connection limits (usually a pool of 20 per server with all sites sharing this pool!!), often becomes corrupted, etc, etc.

SQL Server on the other hand, doesn't have any of these limitations, it's much more robust, doesn't have size or connection limits and is a much better database system.

A bank may use a farm of MS SQL Servers to store all it's bank records on, but you wouldn't even dream of placing this data in an Access database, even if it where possible.

Basically Access is a cheap desktop flat file database system for home use, and MS SQL Server is a $19,000 professional Relational Database Management System used by the big boys.

The very very large company that I work in uses Access to do its wage /overtime payments.  Its got 8 databases about 120mb each and its always becoming corrupted.  Theyre just too tight to upgrade it to SQL.  Access, in the words of Microsoft, "should not be used in a corporate environment or where stability is paramount"

Dont forget that you can get a free copy of MS SQL in its MSDE version.  Its a cut down version of the full SQL2000 but doesnt come with the Enterprise Manager (but you can get free tools that do the same) and has a concurrent connection limit.  I run my forum development on MSDE on my XP laptop, works fantasic!

These questions have been asked/answered a zillion times.

Like everyone has stated MS SQL Server is the way to go for the best performance. However, I'd like to share some stats (again) for one of my forums which is a credit to WWG. Please note that I have purged this database at least 3 times for old members and posts. It has peaked at 180 users showing active and maintained 120 active users for over 5 hours before.

Running Access at ~14MB

Forum Statistics
Launch Date of Site: February 4th, 2004 (393 days of operation) We have 4735 Posts in 828 Topics in 21 Forum(s) Avg Posts Per Day: 12.05 Last Post on 04 March 2005 at 2:33pm By MrDoh	We have 15526 Forum Members Avg New Members per day: 39.49 (excluding today) New Members today (so far): 8 | Yesterday: 17 # of Members Visiting Site Today: 331 (excluding new members) The Newest Forum Member is Zagroseckt

This page was generated in 0.7031 seconds.

There are currently 29 Active Users online, 17 Guest(s) and 12 Member(s)