Jet or SQL Server?
You can always use Microsoft Access as the visual front end to
your database. However, you have a choice when it comes to choosing
your database engine (to store the data).
There are 4 primary considerations. In order of priority, they
are:
- Simplicity
- Data integrity
- Simultaneous users
- Amount of data
| Key Points |
Jet |
SQL Server |
| Simplicity |
Easy to administer. |
More complex to administer. Uselful when scalability becomes
an issue. |
| Data integrity |
File/Server database - no logging of data. Therefore, if you
get a power failure, the Access database may become recorrupt.
You would then have to restore from a recent backup. |
Client/Server - consequently, SQL Server logs transactions.
This is useful if you get a power failure, disk error etc.
SQL Server can recover itself as transactions are logged.
Very good for mission critical data. e.g. financial systems
or if it requires 24hrs a day, 7 days a week uptime. |
| Simultaneous users |
It depends on what you are doing. The practical limit if you
are just reading data is between 50 and 250 users in Access
2000. More intensive use like frequent querying of the data
will reduce this number. |
SQL Server can scale to a much higher number. Microsoft
have done experiments with 2,000 simultaneous users!
It can certainly cope with enterprise wide database systems. |
| Amount of data |
1 gigabyte in Access 97 and 2 gigabyte in Access 2000.
Larger databases can be created by using linked tables to
several different MDB files.
Also, query processing is done on the Client, thus increasing
network traffic. |
SQL Server's limit is much higher and also has performance
advantages over Jet for large sets of data and many simultaneous
users.
Query processing is done on the Server, thus reducing the
netword traffic (but loading the Server more). |
You think Access might be suitable for your project?
Here's
how we can help 
1.
Why Choose Access?
2. Performance:
Is Access up to the job?
3. Scalability:
What are the upgrade options?
4. Which Database
Engine should I use? Jet (that comes with Access) or SQL Server?
|
|
