(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.
XSitePro Download Center: Search Engine Marketing & Web Hosting - PPC Ads and Affiliate Programs 
