Access Database Engine
The Microsoft Jet Database Engine is a database engine on which several Microsoft products were built. JET stands for "Joint Engine Technology", and the technology is sometimes referred to as "Microsoft JET Engine" or just "Jet". A database engine is the underlying component that a Relational Database Management System (RDBMS) uses to create, retrieve, update and delete (CRUD) data from a database, and is accessed by the user interface part of the RDBMS (an RDBMS generally consists of a component that manages the data itself and a component that allows a user to manipulate the data that resides in the database). Microsoft Access, Microsoft Exchange Server (up to Exchange 2003) and Visual Basic used Jet as their underlying database engine; however, it has since been surpassed by Microsoft Desktop Engine (MSDE) and no longer exists as a component of Microsoft Data Access Components (MDAC). Jet databases can be upgraded (or in Microsoft parlance, "up-sized") to an MSDE database.
Originally starting in 1992 from a Microsoft internal database product development project, Jet consisted of three modules that programmers and others could manipulate the database through. Jet also had an architecture that offered a locking mechanism with the ability to allow multiple users to access the database at the same time, transaction processing for processing more than one operation at a time, and a means to enforce data integrity in the database. There were several versions of Jet, and later versions eventually had Unicode character storage support, among other features.
Architecture
Jet was part of an RDBMS. It offered a single interface that other software could use to access Microsoft databases, and provided support for security, referential integrity, transaction processing, indexing, record and page locking and also data replication. In later versions of Jet the engine was extended to be able to run SQL queries, store character data in Unicode format, create views, and allowed bi-directional replication with Microsoft SQL Server.
There were three modules to Jet. One was the Native Jet ISAM Driver, a Jet dynamic link library (DLL) that could directly manipulate Microsoft Access database files (MDB), which was a modified form of an Indexed Sequential Access Method (ISAM) database. Another one of the modules were the ISAM Drivers, DLLs that allowed access to ISAM databases, among them being Xbase, Paradox, Btrieve and FoxPro files. The final module was the Data Access Objects (DAO) DLL, DAO allowed programmers access to the Jet engine. It was basically an object-oriented data language used by Access Basic and Visual Basic application developers to access Jet.
Locking
Jet allowed multiple users to access the database concurrently. To prevent that data from being corrupted or invalidated when multiple users tried to write to the database, Jet employed a data write locking policy. Any single user could only modify those database records (that is, items in the database) to which they had applied a lock that gave them exclusive access to the record until the lock was released. Up to Jet 4, a page locking model was used, and in Jet 4 a record locking model was employed. Microsoft databases are organised into data "pages", which are fixed length (2 kB) data structures that divide up the database. Data is stored in "records", but these are of variable length and so may take up less or more than one page. The page locking model worked by locking the pages, instead of individual records, which though less resource intensive also meant that more than one record might be locked at any one time. Record locking was introduced in Jet 4.
There were two mechanisms that Microsoft used for locking: pessimistic locking, and optimistic locking. Pessimistic locking is where a record or page is locked immediately the lock is requested, while an optimistic lock delays the synchronization for transactions until the operations are actually performed. Conflicts are less likely but won't be known until they happen.. In this situation there is less chance of someone needing to access the record while it is locked; however, it cannot be certain that the update will succeed because the attempt to update the record fails if another user updates the record first. With pessimistic locking it is guaranteed that the record will be updated if the user obtained the lock, however there may be more lock conflicts with this policy.
Transaction processing
Jet supported transaction processing for database systems that had this capability (ODBC systems had one level transaction processing, while several ISAM systems like Paradox did not have transaction processing capability). Transactions are a series of operations performed on a database that must be done together — this is known as atomicity and is a part of ACID (Atomicity, Consistency, Isolation, and Durability), concepts considered to be the key transaction processing features of a database management system. For transaction processing to work (until Jet 3.0), the programmer needed to begin the transaction manually, perform the operations needed to be performed in the transaction, and then commit (save) the transaction. Until the transaction is committed, the only changes that are made are in memory and not actually done on disk [1]. Transaction processing has a number of advantages over normal database writing, the main advantage being that transactions can be abandoned if a problem occurs during the transaction; this is called rolling back the transaction, or just rollback. It also means that if one record must be updated in a loop numerous times that it can all be done in memory and only one write will be done, instead of many time-expensive writes to the database. However, it also means that writes do not happen immediately, which can also impact on performance, depending on the environment.
Implicit transactions were supported in Jet 3.0 onwards. These are transactions that are started automatically after the last transaction was committed to the database. Implicit transactions in Jet occurred when an SQL DML statement was issued. However, it was found that this had a negative performance impact, so in Jet 3.5 Microsoft removed implicit transactions when SQL DML statements were made. [2]
Data integrity
Jet enforces entity integrity and referential integrity. Entity integrity is one of the key concepts of relational databases, and ensures that no record is able to be duplicated and also ensures that no field (or group of fields) that identify the record (the primary key) are NULL. Thus, Jet supports primary keys. Referential integrity is where the fields that identify data that exists in a database tables (the foreign key) must correspond with an existing primary key in that database. If a foreign key value exists that does not have a corresponding primary key in the referenced table, then the referential integrity is broken and the data between tables will no longer be synchronised. For instance, a music lover may have a database that stores information about his record collection, and need to store data about an artist and his/her music. In this example, the artist can record many albums, but the album is only recorded by one artist, so two database tables are created: Artist and Album. The artist table uses the field artist_id as it's primary key, and the album table uses this as it's foreign key, with the primary key for this table being album_id. If, for some reason, an artist is deleted and there is an album in the system that contains a reference to that artist then the referential integrity of this record would become broken. Jet is capable of doing cascading updates and deletes, if required. For instance, if the artist in the previous example was deleted and cascading updates and deletes are declared, then all the artists' albums would also be deleted.
Jet also supports "business rules" (also known as "constraints"), or rules that apply to any column to enforce what data might be placed into the table or column. For example, a rule might be applied that does not allow a date to be entered into a date_logged column that is earlier than the current date and time, or a rule might be applied that forces people to enter a positive value into a numeric only field.
Security
Access to Jet databases is done on a per user-level. The user information is kept in a separate system database, and access is controlled on each object in the system (for instance by table or by query). In Jet 4, Microsoft implemented functionality that allowed database administrators to set security via the SQL commands CREATE, ADD, ALTER, DROP USER and DROP GROUP. These commands were a subset of ANSI SQL 92 standard, and they also applied to the GRANT/REVOKE commands. [3] When Jet 2 was released, security could also be set programmatically through DAO.
Queries
Queries are the mechanisms that Jet uses to retrieve data from the database. They can be defined in Microsoft QBE (Query By Example), through the Microsoft Access SQL Window or through Access Basic's Data Access Objects (DAO) language. These are then converted to an SQL SELECT statement. The query is then compiled — this involves parsing the query (involves syntax checking and determining the columns to query in the database table), then converted into an internal Jet query object format, which is then tokenized and organised into a tree like structure. In Jet 3.0 onwards these were then optimised using the Microsoft Rushmore query optimisation technology. The query is then executed and the results passed back to the application or user who requested the data.
Jet passes the data retrieved for the query in a dynaset. This is a set of data that is dynamically linked back to the database. Instead of having the query result stored in a temporary table, where the data cannot be updated directly by the user, the dynaset allows the user to view and update the data contained in the dynaset. Thus, if a university lecturer queried all students who received a distinction in their assignment and found an error in that student's record, they would only need to update the data in the dynaset, which would automatically update the student's database record without the need for them to send a specific update query after storing the query results in a temporary table.
History
| Jet DLLs | ||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||
Jet originally started in 1992 as an underlying data access technology that came from a Microsoft internal database product development project, code named Cirrus. Cirrus was developed from a pre-release version of Visual Basic code and was used as the database engine of Microsoft Access. Tony Goodhew, who worked for Microsoft at the time, says that "It would be reasonably accurate to say that up until that stage Jet was more the name of the team that was assigned to work on the DB engine modules of Access rather than a component team. For VB [Visual Basic] 3.0 they basically had to tear it out of Access and graft it onto VB. That's why they've had all those Jet/ODBC problems in VB 3.0." Jet became more componentised when Access 2.0 was released because the Access ODBC developers used parts of the Jet code to produce the ODBC driver. A retrofit was provided that allowed Visual Basic 3.0 users to use the updated Jet issued in Access 2.0. [4]
Jet 2.0 was released as several dynamic linked libraries (DLLs) that were utilised by application software, such as Microsoft's Access database. DLLs in Windows are "libraries" of common code that can be used by more than one application — by keeping code that more than one application uses under a common library which each of these applications can use independently code maintenance is reduced and the functionality of applications increases, with less development effort. The three dlls that comprised Jet 2.0 were the Jet DLL, the Data Access Objects (DAO) DLL and several external ISAM DLLs. The Jet DLL determined what sort of database it was accessing, and how to perform what was requested of it. If the data source was an MDB file (a Microsoft Access format) then it would directly read and write the data to the file. If the data source was external, then it would call on the correct ODBC driver to perform its request. The DAO DLL was a component that programmers could use to interface with the Jet engine, and was mainly used by Visual Basic and Access Basic programmers. The ISAM DLLs were a set of modules that allowed Jet to access three ISAM based databases: Xbase, Paradox and Btrieve. [5]
Jet 3.0 included many enhancements, including a new index structure that reduced storage size and the time that was taken to create indices that are highly duplicated, the removal of read locks on index pages, a new mechanism for page reuse, a new compacting method whereby compacting the database resulted in the indices being stored in a clustered-index format, a new page allocation mechanism to improve Jet's read-ahead capabilities, improved delete operations that speeded processing, multithreading (three threads were used to perform read ahead, write behind, and cache maintenance), implicit transactions (whereby users did not have to instruct the engine to start manually and commit transactions to the database), a new sort engine, long values (such as memos or binary data types) were stored in separate tables, and dynamic buffering (whereby Jet's cache was dynamically allocated at start up and had no limit and which changed from a first in, first out (FIFO) buffer replacement policy to a least recently used (LRU) buffer replacement policy). [6] Jet 3.0 also allowed for database replication.
Jet 4.0 was the last version produced by Microsoft. It had numerous additional features and enhancements [7]:
- Unicode character storage support, along with an NT sorting method that was also implemented in the Windows 95 version;
- Changes to data types to be more like SQL Server's (LongText or Memo; Binary; LongBinary; Date/Time; Real; Float4; IEEESingle; Double; Byte or Tinyint; Integer or Interger synonyms Smallint, Integer2, and Short; LongInteger or LongInteger synonyms Int, Integer, and Counter; Auto-Increment support was dropped; Currency or Money; Boolean and GUID); a new decimal data type
- Memo files could be indexed
- Compressible data types
- SQL enhancements to make Jet conform more closely to ANSI SQL 92
- Finer grained security; views support; procedure support
- Invocation and termination (committing or rolling back) of transactions
- Enhanced table creation and modification
- Referential integrity support
- Connection control (connected users remain connected, but once disconnected they cannot reconnect, and new connections cannot be made. This was useful for database administrators to gain control of the database)
- A user list, which allows administrators to determine who is connected to the database
- Record-level locking (previous versions only supported page-locking)
- Bi-directional replication with Microsoft SQL Server.
Jet 4 databases can be "upsized" (upgraded) to "an equivalent database on SQL Server with the same table structure, data, and many other attributes of the original database" if the developer has a copy of Microsoft Office 2000 Professional Addition via a Microsoft Access Upsizing Wizard utility. Reports, queries, macros and security is not handled by this tool, meaning that some manual modifications may need to be done if the developer has been heavily reliant on these Jet features. [8]
Notes
- ^ Microsoft Jet Database Engine 2.0: A User's Overview (see "Jet Transactions" section), retrieved August 2, 2005
- ^ Kevin Collins (Microsoft Jet Program Management), "Microsoft Jet 3.5 Performance Overview and Optimization Techniques", MSDN. Retrieved July 19, 2005.
- ^ Microsoft, MS KB article 275561: "Description of the new features that are included in Microsoft Jet 4.0"
- ^ Tony Goodhew, "Jet Engine: History". Accessed July 14, 2005.
- ^ Paul Litwin (1994), Microsoft Jet Database Engine 2.0: A User's Overview, retrieved July 14, 2005.
- ^ Microsoft, MS KB article 137039 New Features in Microsoft Jet Version 3.0, last updated December 9, 2003, retrieved July 14, 2005.
- ^ Microsoft, MS KB article Q275561 Description of the new features that are included in Microsoft Jet 4.0
- ^ Microsoft, "Microsoft® Access 2000 Data Engine Options", white paper.
References
- Haught, Dan; & Ferguson, Jim. (1997) "Microsoft Jet Database Engine Programmer's Guide". Microsoft Press. ISBN 1572313420.
- Microsoft (October 1998). "Microsoft Access 2000 Data Engine Options". White paper. Retrieved from companion CD from "Inside Microsoft SQL Server 2000" (2001), Kalen Delaney, Microsoft Press.