 |
(see Access hosting for general Access database characteristics)
(see SQL hosting for general SQL Server characteristics)
Database Architecture:
Access is fundamentally different in structure to SQL Server, and the
difference can be seen in terms of reliability and performance. Access
uses a file server system approach, while SQL uses a client/server
design.
Each client in Access reads and writes directly to the raw data tables. If
a client machine fails while writing data, the back-end database will also
usually crash and become corrupt. Network failures will also cause this
problem to occur. As the amount of data and the number of users
increases, the problem becomes worse.
A file server system such as Access is designed for smaller workgroups and is
scalable to perhaps 10 concurrent clients, and performance starts to degrade rapidly as more users are added.
The SQL Server client/server architecture can be scaled to support many hundreds, or even thousands
of concurrent users without noticeable performance degradation.
Clients using SQL Server do not communicate directly with the tables -
an intelligent data manager on the server handles this task. It manages
the data read/write function of the tables and, if a client machine crashes, or the network
fails, the tables in question will not be affected. The data manager realizes that the transaction
is incomplete and does not commit the interrupted data to the database.
This allows the database to continue to operate as normal.
The SQL client/server system also maintains
a transaction log, so that a backup can restore all completed transactions up to the time of the
failure.
Database Performance:
With a File Server design, such as Access, all tables involved in a form, report or a query are copied across the network (internet) from the server to the client's machine. The tables are then processed and filtered to generate the required
recordset. If looking up details for one particular order from an orders table
containing 20,000 records, then all 20,000 records are moved over the network and
subsequently 19,999 of these records are thrown away. |
