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?