On Monday, October 12th, Microsoft announced a new data engine option for its popular Microsoft Access database. With the upcoming 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.
Since the announcement, many questions have arisen regarding the differences between Jet, MSDE and SQL Server, as well as when to use which engine. We thought we might try to make the distinctions between these data sources a little easier to understand by detailing the threeoptions, offering usage scenarios, and detailing how to move data from one solution to another.
What is Microsoft Access 2000 & what are my data storage options?
Microsoft Access 2000
Microsoft Access 2000 is a powerful relational database application that efficiently creates and manipulates database systems. Access targets the desktop category and works best for individuals and workgroups managing megabytes of data, and is included in the Professional and Developer Editions of Microsoft Office. Access has two major components: the first contains an application development environment for Visual Basic for Applications programmers that includes forms technology, reports and database administration. In addition, there is the user interface common to both Access and the other Office applications.
The second component is the data engine. Before Access 2000, users and developers used the Jet data engine. In the next version, users and developers will be given a choice of data engines. They can continue with an improved version of Jet 4.0, or MSDE.
Jet 4.0
Jet 4.0 is the default data engine for Access 2000, and is much improved over Jet 3.51, the engine in Access 97. Some of the major enhancements in Jet 4.0 are full UNICODE support, row-level locking, compatibility with SQL Server, Jet/SQL Server 7.0 bi-directional replication, enhanced replication conflict resolution and a native OLE DB provider.
MSDE
MSDE — an enabling technology — provides local data storage and offers compatibility with SQL Server. It is completely compatible with the SQL Server 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: automatically chooses the optimal level of lock (row, key range page, or table) for all database operations
-
Unicode: improves multilingual support
-
Dynamic Self-Management: enables the server to manage itself for hands-off standard operations
-
Merge Replication:allows users to modify distributed copies of a database at different times online or offline, and later combine the work 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:
-
Data Transformation Services: make it easy to import data from any source and transform or export it to any other data source
-
Microsoft SQL Server OLAP Services: fast, efficient analysis of complex information thru optimizing data access, thus allowing your organization to get the specific data it needs, quickly
-
English Query: allows users to pose questions in English instead of complex SQL statements
-
Parallel Queries:allows steps in a single query to be executed in parallel, delivering optimal response time.
Jet or MSDE – which one do I choose?
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 is in the optimal position for scaling as your business needs grow.
Enterprise applications require scalability, security and robustness, which can be implemented with MSDE 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. 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.
There are four key points to consider when choosing your database engine:
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.
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. MSDE’s big advantage over Jet is that 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.
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.
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 side, which 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.
How do I move my data from Jet to MSDE or SQL Server?
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, 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, and creating Access and SQL Server applications via Access Projects (the 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.
