Microsoft SQL Server: Microsoft Access 2000 Data Engine Options
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Bill Demas
Microsoft Corporation
June 1999
Applies To: Microsoft Access 97 and Access 2000; Microsoft Data Engine (MSDE); Microsoft Jet Database Engine 3.51 and 4.0; Microsoft SQL Server 7.0
Summary: The purpose of this article is to help you determine which data engine—Jet or MSDE—to use with Microsoft Access 2000. (9 printed pages)
Contents
Introduction What Is Microsoft Access 2000? Quick Synopsis of Jet 4.0 and MSDE Microsoft Access Upsizing Wizard Conclusion
Introduction
With the release of Microsoft® Access 2000, users and developers will have the choice of two data engines in the product: an improved version of the existing Access engine, called Microsoft Jet, or the Microsoft Data Engine (MSDE), which is compatible with Microsoft SQL Server™ 7.0.
MSDE is an enabling technology that provides local data storage and offers compatibility with SQL Server. This is similar to Microsoft Jet, the data engine that currently exists in Microsoft Access. Although Access 2000 uses and installs the Jet data engine by default, developers who want to develop a single application that is also compatible with Microsoft SQL Server will want to use MSDE.
The specific purpose of this article is to help you determine which data engine, Jet or MSDE, to use with Access 2000. The data engines are compared three ways: by enterprise requirements, by usage analysis, and by feature analysis. In short, Jet works best if you want the highest compatibility with Access 97 or earlier versions; MSDE works best if you want to develop from a single code base for a single user to thousands of users or if you anticipate ever having a future need for scalability. When you have finished reading this short article, you will be able to determine which data engine best meets your requirements.
What Is Microsoft Access 2000?
Microsoft Access 2000 is a powerful relational database application with which a desktop user can efficiently create and manipulate database systems. Access targets the desktop category and works best for individuals and workgroups managing megabytes of data. For multiuser access to the same database, Access uses file-server architecture, rather than client-server architecture. Access is included in the Professional and Developer Editions of Microsoft Office.
As a leader in the desktop database category, Microsoft Access makes it easy for users to find and manage their data to make better business decisions. With strong integration with Microsoft Office, Access offers a similar appearance and functionality to that found in the popular Microsoft Word and Excel applications. For general business users, Access provides easy-to-use wizards throughout, such as the Database Wizard for getting up and running quickly, and the Simple Query Wizard for easily finding information from the data. More advanced users appreciate the power behind the Microsoft Visual Basic® for Applications (VBA) programming language, programmable toolbars, and the freely distributable run-time version of Access available with the Office Developer Edition. The combination of ease of use and power in Access makes it the top choice among developers who frequently use Access as a front end to SQL Server in a client-server scenario.
Access has two major components. The first contains an application development environment for Visual Basic for Applications programmers that include forms technology, reports, and database administration. In addition, as mentioned earlier, there is also the user interface (UI) common to both Access and the other Office applications.
The second component in Access, and the main topic of this paper, is the data engine. Before Access 2000, users and developers were using the Jet data engine, whether they knew it or not. In the next version, users and developers will be given a choice of data engines. They can continue with an improved version of the default Access data engine (Jet 4.0), or MSDE, a new data engine option in Access 2000.
Quick Synopsis of Jet 4.0 and MSDE
In this section, we will quickly review the enhancements made to the Access default database engine, Jet, and introduce the functionality of MSDE.
This section includes the following topics:
- Jet
- Microsoft Data Engine (MSDE)
- Comparing Jet and MSDE
Jet
Jet 4.0 is the default data engine for Access 2000. It is a new and improved version of Jet 3.51, the data engine in Access 97. Jet 4.0 contains the following enhancements:
- Full UNICODE support.
- Sorting is compatible with the Windows NT® operating system on Windows® 95 and 98 and Windows NT.
- Row-level locking.
- Enhanced support for ANSI SQL92 and compatibility with SQL Server (examples include GRANT/REVOKE, DECIMAL type data type, and Declarative Referential Integrity).
- Jet/SQL Server 7.0 bi-directional replication.
- Enhanced replication conflict resolution.
- Native OLE DB provider.
Although many users of Access are individuals, there is a wide spectrum of uses. However, most users have databases less than 50 megabytes (MB) in size.
Microsoft Data Engine (MSDE)
MSDE is the new data engine for Microsoft and is our strategic direction. MSDE is completely compatible with the SQL Server version 7.0 code base, enabling customers to write one application that scales from a PC running the Windows 95 operating system to multiprocessor clusters running Windows NT Server, Enterprise Edition.
Some of the technologies included in MSDE are as follows:
- Dynamic Locking—This automatically chooses the optimal level of lock (row, key range page, or table) for all database operations. It maximizes the trade-off between concurrency and performance, resulting in optimal usage. No tuning is required.
- Unicode—This improves multilingual support.
- Dynamic Self-Management—This enables the server to monitor and manage itself, allowing for hands-off standard operations.
- Merge Replication—This allows users to modify distributed copies of a database at different times, online or offline, and the work is later combined into a single uniform result.
MSDE incorporates technology from SQL Server 7.0. By using MSDE, developers can later enable hundreds or even thousands of users to use such SQL Server 7.0 features as the following:
- Data Transformation Services—This makes it easy to import data from any source (for example, OLE databases, Windows NT Directory Services, and spreadsheets) and transform or export it to any other data source.
- Microsoft SQL Server OLAP Services—This enables fast, efficient analysis of complex information by optimizing data access to enable your organization to get the specific data it needs quickly. The Pivot-Table Services run on client workstations and provide desktop multidimensional analysis by making the PivotTable® dynamic views more intuitive and putting all of the PivotTable options on the screen for your use. For the first time, Excel users can analyze gigabytes and even terabytes of data by using Microsoft SQL Server 7.0.
- English Query—This allows users to pose questions in English instead of forming queries with complex SQL statements.
- Parallel Queries—This allows steps in a single query to be executed in parallel, delivering optimal response time.
Comparing Jet and MSDE
This section compares the two data engines in three different ways: by enterprise requirements, by usage, and by feature set. After reading this section, you should have a good understanding of which data engine is right for you.
This section includes the following topics:
- Enterprise Requirements
- Jet and MSDE Usage Analysis
- Jet and MSDE Feature Analysis
Enterprise requirements
If you are developing or using Access in an enterprise environment, MSDE is the recommended data engine. Even if your current needs are not at the enterprise level, using the Access front end with the MSDE back end will help ensure that your database will be in the optimal position for scaling as your business needs grow.
Enterprise applications require scalability, security, and robustness, which can all be implemented with MSDE or SQL Server but not with Jet. For example, if your application needs transaction support, even in the event of a network, server, client computer, or client application crash, you will want to use MSDE or SQL Server. Conversely, the Jet engine does not support atomic transactions: It does not guarantee that all changes performed within a transaction boundary are committed or rolled back. Another issue important to corporate environments is security. MSDE and SQL Server are integrated with Windows NT security; Jet security is not. This makes administering Jet more expensive than administering MSDE or SQL Server 7.0.
The chart below shows these two potential enterprise requirements and how each of the data engines compares. If your business has any of the needs listed in the left column, you will want to implement MSDE; if not, you will need to continue on to the next section to determine which is right for you.
Requirement
|
SQL Server (use MSDE if these are future requirements) |
Microsoft Access (Jet) |
| Scalability | ||
|
|
|
|
|
|
|
|
|
|
|
|
| Business Critical | ||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Rapid Application Prototyping |
|
|
Jet and MSDE usage analysis
There are four key usage criteria to consider when choosing your database engine. Let's review them in priority order.
Simplicity—Jet 4.0 clearly has the highest compatibility with Access 97 and earlier versions. If you have existing applications developed for Access, Jet is your easiest and probably best option, given its compatibility with Access 97 and earlier.
Jet is easier to use and administer than MSDE, making it a good choice for new and relatively simple database applications that do not have compatibility concerns with SQL Server. It has low resources for memory and disk and requires almost no administration. Jet is also the default database option for Access 2000. A database created by using the Jet engine can always be upsized later to SQL Server using the Upsizing Wizard, although some additional modification may be required.
Advantage: Jet
Data Integrity—MSDE incorporates technology from SQL Server 7.0. SQL Server delivers a single code base, which scales from a PC running Windows 95 to multiprocessor clusters running Windows NT Server, Enterprise Edition, offering 100 percent application compatibility. Jet does not have this type of scalability because it is confined to the Access product.
MSDE is a client-server data engine; Jet is a file-server data engine. The big advantage that MSDE has over Jet is that MSDE is a process that runs queries and logs transactions. If anything should go wrong while writing to the database, such as disk error, network failure, or power failure, MSDE can recover because it logs transactions. After the system comes back up, MSDE will revert back to the last consistent state. This gives MSDE greater reliability than Jet. If the system were to go down with Jet, the database could be corrupt and you might need to revert back to your last backup copy.
MSDE is the right engine for systems that involve important transactions, such as financial applications, or for mission-critical applications that need to be up 24 hours per day, 7 days a week, such as the Internet. The more important the database, the more likely you are to choose MSDE.
Advantage: MSDE
Number of Simultaneous Users (Performance)—SQL Server 7.0, the basis for MSDE technology, can handle a very large number of simultaneous users. Jet and MSDE are optimized for individual or small workgroup solutions.
MSDE also has a performance advantage over Jet for large sets of data and many simultaneous users. Because Jet is a file-server system, the query processing must happen on the client. This involves moving a lot of data over the network for large databases. MSDE runs that same query on the server. This puts a larger load on the server, but can reduce network traffic substantially, especially if the users are selecting a small subset of the data.
If you are creating a new application for a small group of users, MSDE or SQL Server will help your application scale in the future.
Advantage: MSDE and SQL Server for scalability
Amount of Data—Jet can handle up to 2 GB of data per .mdb file. MSDE also supports 2 GB of data.
Jet and MSDE feature analysis
The third and final way to determine which engine is best for you to use is by analyzing features of both engines. The partial feature listing below is intended to assist Microsoft Access users in deciding between the Jet engine and the MSDB engine.
| Features | Jet | MSDE |
|---|---|---|
| Heterogeneous joins | X | X |
| Top n and top n% queries | X | X |
| Validation rules | X | X |
| Default values | X | X |
| Triggers and stored procedures | X | |
| Referential integrity through triggers | X | |
| Declarative referential integrity | X | X |
| Engine-level cascading updates and deletes | X | |
| Basic locking unit | Row | Row |
| Row locking on insert | X | X |
| Field-level replication | X | X |
| Custom code for replication conflict resolution | X | X |
| Scheduled replication | X (Requires Microsoft Office 97, Developer Edition) | X |
| Built-in security | X (File level read/write password or permissions through OS) | X |
| Built-in encryption | X | X |
| Distributed transactions | X | |
| Dynamic backup and restore | X | |
| Transaction log backups | X | |
| Automatic Recovery | X | |
| 32-bit engine | X | X |
| Data capacity | 2 GB per database | 2 GB per database. SQL Server supports TBs per database |
Microsoft Access Upsizing Wizard
The Microsoft Access Upsizing Wizard utility takes a Jet database and creates an equivalent database on SQL Server with the same table structure, data, and many other attributes of the original database. It will recreate table structures, indexes, validation rules, defaults, autonumbers, and relationships, and takes advantage of the latest SQL Server functionality wherever possible. However, there are no modifications made to reports, queries, macros, or security. This Upsizing Wizard is a core wizard that ships with Office 2000 Professional Edition.
The Upsizing Wizard has three main functions:
- Migrating databases from Jet to SQL Server.
- Creating Access and SQL Server applications through Jet linked tables.
- Creating Access and SQL Server applications through Access Projects (new development model that uses an Access project file connected directly to a SQL Server database).
This tool allows developers who design client-server applications on their desktops in Access to generate a SQL Server database from their prototypes. Also, developers who have existing Jet-based applications will be able to grow those applications to SQL Server and take advantage of its technology.
The wizard will run from a read-only share and use a temporary database to store information about the upsizing process. All user preferences will be stored in the registry on a per-user basis.
Conclusion
We hope that by now you understand which database engine in Access 2000 best meets your requirements. Below is a capsule summary.
Use Jet if:
- You want the highest compatibility with Access 97 or earlier.
- Your environment has a small number of simultaneous users.
- You have very low resources, such as memory or disk.
- Ease of use is a primary concern.
Use MSDE if:
- You want to develop from a single code base, from a single user to thousands of users.
- You expect a future need for greater scalability.
- You require easy merge replication with the central server.
- You need the best security.
- You need great reliability, such as transaction logging.
- Your system is online 24 hours a day, 7 days a week.
- You need stored procedures and triggers.