Sunday, July 23, 2006

Red vs. Blue JET Database API Confusion

It's undoubtedly safe to say that the majority of Microsoft Exchange administrators, developers, ISVs, and possibly even users are disappointed that the forthcoming Exchange Server 2007 didn't migrate its message store from the Blue JET Extensible Storage Engine (ESE) to SQL Server 2005. After all, SQL Server 2005 stole the show from Microsoft's rather dismal fourth quarter earnings report as the "Server and Tools [segment] delivered 18% revenue growth for the quarter, fueled by an increase of over 35% revenue growth for SQL Server."

One of the motivators for Exchange's adoption of SQL Server for its message store is the belief of many IT executives and even Exchange administrators that the ESE uses the same technology as Access's Jet database engine. For example, Peter Galli's July 20, 2006 eWeek article, "Exchange Data Store Change Still in the Cards," starts with a lead that refers to generic Jet:

While Exchange 2007, the upcoming e-mail, calendaring and messaging server from Microsoft, is still based on the Extensible Storage Engine, a derivative of the Jet database store, the company says it remains committed to unifying this with the SQL Server database store going forward.

They just don't know exactly when yet.

Officials such as Terry Myerson, the general manager of the Exchange Server product group, argue that there is ultimately more value for customers by staying on the Jet engine in Exchange 2007, the second, public beta of which is expected to ship as early as late July, with the final product likely in late 2006 or early 2007.

Wikipedia's entry for "Microsoft Jet Database Engine" merges the Red and Blue JET flavors (Purple JET?) into a single, generic Jet database:

JET stands for Joint Engine Technology, sometimes being referred to as Microsoft JET Engine or simply Jet. Microsoft Access, Microsoft Exchange Server and Visual Basic use or have used Jet as their underlying database engine. It has since been superseded, however, first by Microsoft Desktop Engine (MSDE), then later by SQL Server 2005 Express Edition and no longer exists as a component of Microsoft Data Access Components (MDAC).

Note: The Wikipedia entry correctly assigns Jet 4.0 as the version used by Access 2000, 2002, and 2003, but incorrectly says that Exchange 5.5 used it also. No wonder folks are confused. Remember Microsoft's convulsive architectural and product name-game of the early 21st century: Changes from Windows Distributed interNet Architecture (Windows DNA) and Digital Nervous System to the Next Generation Windows Services (NGWS), .NET Products and Services, Windows Server .NET, Visual Studio .NET, Visual Basic .NET and My Services .NET (Hailstorm), and finally to WhateverSKU 200x? The confusion between Red, Blue, and generic JET/Jet pales in comparison. JET Detectives It's not easy to find an authoritative history of Microsoft's Joint Engine Technology (JET) API. During the late 1990's, I was told by a Microsoft representative that JET (then being downsized to "Jet") was not an abbreviation or acryonym for anything. The "Notes" section of Microsoft's Extensible Storage Engine Reference, which is likely to be as close to authoritative as one can get, states:
ESE was formerly known as Joint Engine Technology (JET) Blue, and so frequently the term "JET Blue" or "JET" is used interchangeably with the term ESE outside this documentation. However, there are in fact two completely separate implementations of the JET API, called JET Blue and JET Red. The term "JET" is frequently also used to refer to JET Red, which is the database engine that is used with Microsoft Office Access. The two JET implementations are completely different, are separately maintained, have a vastly different feature set, and are not interchangeable. Within the ESE documentation, "JET" refers to the ESE or the JET API as ESE implements it. Any references to the JET Red will always explicitly be labeled "JET Red".
The ESE folks apparently didn't have the clout to establish "JET Red" as the brand for garden-variety Jet, the oft-maligned and oldest of Microsoft's freely-distributable datbase triumerverate—Jet 4.0, SQL Server 2005 Express Edition (SSX), and the forthcoming SQL Server 2005 Mobile Edition (SSM). It's clear that generic Jet is Red JET. Or is it JET Red? Confusion about JET API colors has abounded for years. Mary Jo Foley, who's well-known as a technical straight-shooter, got the Jet versions right and then wrong in this excerpt from her June 15, 2005 "Microsoft Shares More Clues About Longhorn Server" Windows Watcher article:
When asked by a participant whether Microsoft was planning to revisit Active Directory "resilience" by reworking Active Directory and other "server-intrinsic databases in Longhorn" so they don't require the Microsoft Jet database engine, Muglia noted that there are two versions of Jet: "Jet Red," which is what Microsoft Access uses, and "Jet Blue," which is "used widely within the Windows Server, including in Active Directory and Exchange Server. "The Jet Blue used by Access is transactional and in general, has had good robustness characteristics," Muglia told participants. "We will always look at where we should go with this and someday, we'll host it on SQL (Server). But that is a ways away — certainly not Longhorn."
It's not clear if Bob Muglia misspoke or Mary Jo simply transcribed his response incorrectly. Regardless, the last paragraph directly contradicts the first. Access 2007 Migrates to a New Red JET Erik Rucker's October 13, 2005 "Access 12's new data engine" post in MSDN's A discussion of what's new in Access 2007 (formerly "Access 12") blog made the first public disclosure that Microsoft Office Access 2007 (then 12) would use a special, Access-only version of Jet 4.0's .mdb file structure and database engine. The primary change to the new .accdb format was to accommodate SharePoint with Attachment and Multi-Valued Field data types. However, .accdb files don't support user-level security (with workgroup files), replication, and a few data import/export features of earlier Access versions. In a January 4, 2006 "JET History" comment to Erik's post, Microsoft's Ian Jose (ianjo) aimed to reduce readers' confusion about which JET API version was in play by providing more detail of the divergence of Red and Blue JET:

Everywhere this article refers to Jet, it is refering to JET Red. JET Red was an ISAM originally developed by Microsoft for BC7, compiled basic. JET Blue was originally developed by Microsoft as a prospective upgrade for Access, but it was never used in this role. JET Red and JET Blue began sharing the common JET moniker in the Spring of 1990 when a query technology, QJET, was developed that would host on both Red and Blue.

As others have noted, JET Blue went on to be used by Microsoft Exchange, Active Directory and many many other Windows services. JET Blue was a private API for many years, but became a published API in April of 2005 when three middle ware applications not covered by the Windows EULA chose it as their data store. Now anyone can use JET Blue.

JET Red is a file sharing techonlogy with page level locking and best effort crash recovery. JET Blue on the other hand is designed to be embedded in a server application, does not share files, has write ahead logging for guaranteed crash recovery and has record level locking. JET Blue does not ship with a query engine but instead relies on applications to write their own queries as C++ ISAM code.

Note: Red JET has had record-level locking since Access 2000's Jet 4.0 but is prone to crashes and file corruption.

In the early 1990s while busy writing Access user and developer books, I learned a bit more about Blue JET and then lost interest in the hierarchical version as Access 1.1+ and Visual Basic 3.0+ began to dominate the desktop relational database management system (RDBMS) space and book market.

Jet is Dead or Just Moribund?

Cries of "Jet is dead" accompanied the release of Access 2000, MSDE 1.0, and Access Data Projects, which abandoned the .mdb file format entirely. Access book sales fell off precipitously; migration from Access 97 to 2000 was at a trickle to be generous. Microsoft's Bill Demos wrote in a June 1999 whitepaper, "Microsoft SQL Server: Microsoft Access 2000 Data Engine Options":

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.

Obviously the rumors of Red JET's impending death in 1999 and 2000 were "greatly exaggerated." In fact, today the Access team is attempting to convince developers not only to migrate data storage from strategic SQL Server to Windows SharePoint Services (WSS) 3.0 as a data store or use non-strategic Access .accdb databases derived from Red JET. Plus (gasp!) they recommend moving from ActiveX Data Objects (ADO) and OLE DB to (double-gasp!) retro Data Access Objects (DAO) to enable programmatic access to the new data types and other Access 2007 enhancements. Not to be outdone by the storage engine group, Access 2007's programmability team is pushing formerly abandoned macros to replace VBA. Microsoft put Access macros out to pasture when they released Access 95 with "real" VBA instead of Access (or Embedded) Basic. The idea is that macros will run in secure environments where unsigned VBA code won't. I bet almost all Office 2007 users will end up with entire drives designated as trusted locations (a.k.a "safe for scripting.") Maybe I'm getting too old for this foolishness or—as my Montenegran friend Drago Vely would call it—crazinesses. It looks to me as if the next version of Visual Studio (codenamed "Orcas") alone will introduce many more cool data-related features than all Microsoft Office 2007 members together. Technorati tags: , , , , , , , , , , , , , , , ,


Joel Check said...

Do you have evidence to support that record-level locking is prone to crahes and file corruption?

"Note: Red JET has had record-level locking since Access 2000's Jet 4.0 but is prone to crashes and file corruption."

Anonymous said...
This comment has been removed by a blog administrator.