Microst Access 2000 Data Engine Options

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 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 white paper 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, usage analysis, and 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 ever anticipate a future need for scalability. By the end of reading this short paper, you will be able to determine which data engine best meets your requirements.

What is Microsoft Access?

Microsoft Access 2000 is a powerful relational database application that a desktop user can use to efficiently create and manipulate database systems. Access targets the desktop category and works best for individuals and workgroups managing megabytes of data. For multi-user 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 ease-of-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 programming language, programmable toolbars, and the freely distributable runtime 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.

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 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 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 under 50MB in size.

Microsoft Data Engine (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:

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. By using MSDE, developers can later enable hundreds or even thousands of users to use SQL Server 7.0 features like:

Data Transformation Services. This makes it easy to import data from any source (ex: 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 multi-dimensional analysis by making the Pivot Table 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 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 these two data engines in three different ways: by enterprise requirements, usage, and feature set. After reading this section, you should have a good understanding of which data engine is right for you.

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 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 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, then 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 important issue 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 compare. 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	SMP Support Virtually unlimited number of concurrent users Terabyte levels of data Transaction Logging	No SMP support Maximum of 255 users 2 GB of data No transaction logging
Business Critical	7X24 support and QFE Point-in-time recovery Guaranteed transaction integrity Built-in fault tolerance Security integrated with Windows NT	No 7X24 support or QFE Recoverable to last backup No transaction logging No integrated security with Windows NT
Rapid Application Prototyping	Access is UI for both engines and offers WYSIWIG database tools and built-in forms generation.

Jet and MSDE Usage Asis

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 nearly zero administration. Jet is also the default database option for Access 2000. A database created using the Jet engine can always be upsized later to SQL Server using the Upsizing Wizard although some additional modification may be required.

(a) Advantage – Jet

Data Integrity. MSDE incorporates technology from SQL Server 7. 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% application compatibility. Jet does not have this type of scalability as 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 during a write to the database, such as disk error, network failure or power failure, MSDE can recover as 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 may 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, seven days a week, such as the Internet. The more important the database, the more likely you are to choose MSDE.

(b) 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 loads the server more, 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.

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 decide on 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 the Microsoft Office 97, Developer Edition)	X
Built-in security	X (file level read/write password or permissions via 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 is scheduled to ship 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 via Jet linked tables

Creating Access and SQL Server applications via 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 desktop in Access to generate a SQL Server database from their prototype. 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

Hopefully 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 premium.

Use MSDE if…

You want to develop from a single code base from 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 greater reliability, such as transaction logging.

Your system is online 24 hours a day, 7 days a week.

You need stored procedures and triggers.

For more information: http://www.microsoft.com/office/

This is a preliminary document and may be changed substantially prior to final commercial release. This document is provided for informational purposes only and Microsoft makes no warranties, either express or implied, in this document. Information in this document is subject to change without notice. The entire risk of the use or the results of the use of this document remains with the user. The example companies, organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Microsoft, the BackOffice logo, the Office logo, Pivot Table, Visual Basic, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Other trademarks and trade names mentioned herein are the property of their respective owners.

Part Number 098-81609

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.