|
(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.
SQL Server executes the
filtering on the server (if properly
designed) and only 1 record is
transmitted over the network.
This can affect performance in two ways.
Firstly SQL Server is highly optimized and can
usually perform the required filtering much
more quickly than the client machine, and
secondly the amount of data sent across the
network link is vastly reduced, with major
performance gains.
An overall speed improvement of over 100% can
be expected, and may often be much
more.
|
