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: Databases, JET, Microsoft JET, Exchange, Microsoft Exchange, Exchange Server, Microsoft Access, Access 2007, Microsoft Office, Office 2007, Visual Studio, Orcas, SQL Server 2005, SQL Express, Windows SharePoint Services, WSS, WSS 3.0

Windows Vista Won't Support MSDE 1.0 and 2000

According to Microsoft UK's Eric Nelson, MSDE will not be supported on Windows Vista. Although Eric claims that his July 12, 2006 post wasn't the first to report this potentially calamitous news, he didn't provide links to any predecessor posts. Apparently, the "lack of support" extends to SQL Server 7.0 and 2000, but it's unlikely that anyone would run full server versions under a client OS. There has been remarkably little reaction to Eric's or predecessor posts, if there are any earlier references. Google blog search returns a few post-July 12 hits on the subject; Technorati returned only old (200+ days) entries for a July 21 search on 'MSDE'—before uploading this post. As sharp-eyed as always, Mike Gunderloy picked up the story for "The Daily Grind" on July 14, 2006. ISVs whose commercial applications depend on MSDE 1.0 or 2000 will be most affected by Microsoft having deprecated these two products to "downlevel purgatory." Users who attempt to install either product under Windows Vista Beta 2 appear to receive a warning message but can continue installation. It's not yet clear what will occur in the release version when users attempt to upgrade to Vista a Windows 2000 or XP client with MSDE installed. Eric also mentioned in passing that the "Visual Basic 6 Development Environment will not be supported on Windows Vista," which Jacqueline Emigh reported in her February 21, 2006 article, "Microsoft's Upcoming Vista To Support Legacy VB6 Apps" for Microsoft's DevSource. This article quotes Jay Roxe, Microsoft's lead product manager for Visual Studio, as stating that Microsoft will extend mainstream support for the VB6 runtime ... by another six or seven years, through the end of the Vista lifecycle. Microsoft TechNet published "Upgrading MSDE 2000 to SQL Server 2005 Express" (SP-1) on April 7, 2006. If you don't mind a cumbersome login process, you can watch "MSDN Webcast: Upgrading from MSDE to SQL Server 2005 Express Edition (Level 100)" by the white paper's author, Michael Otey. Euan Garden addresses issues with SQL Server 7.0 and 2000 versions of the Data Transformation Service (DTS) Import/Export Wizard for copying tables from one server and database to another. Euan also offers recommendations for importing and exporting data with SQL Express, which doesn't include the DTS (now SQL Server Integration Services or SSIS) runtime. Euan's "SQL MythBusters – MSDE/SQL Express has a 5 concurrent user limit" post includes brief histories of MSDE 1.0 and 2000, plus SQL Express. MSDE 2000 users can copy their .mdf and .ldf files to a new \Data folder and attach them to an SQL Express instance. It's ironic that Microsoft has been promising for years to move Exchange Server's data store from the (Blue) Jet-based Extensible Storage Engine (ESE) to SQL Server. According to eWeek's "Exchange Data Store Change Still in the Cards" article by Peter Galli, Microsoft "says it remains committed to unifying this with the SQL Server database store going forward. They just don't know exactly when yet." Exchange 2007 will continue to use Jet—a technology that Microsoft deprecated as of MSDE 2000's release—not SQL Server as its data store.*

* Note: [Updated 7/23/2006] On July 22, 2006 Euan Garden let me know in no uncertain terms that I was wrong to associate Exchange's Blue JET (Joint Engine Technology) engine with Access's and VB's Red JET engine. See my July 23, 2006 "Red vs. Blue JET Database API Confusion" post.

Microsoft officially released SQL Express (SQLX) on November 7, 2005 and Windows Vista might release in January or February 2007. This means that Vista early adopters' window to convert from MSDE to SQLX would have been about a year, had Microsoft publicized future lack of MSDE support when releasing SQLX. However, the window now is reduced to about six months as a result of July's "stealth-mode" announcement. Not nice. Technorati: Databases, Windows Vista, MSDE, MSDE 1.0, MSDE 2000, MSSQL, SQL Server, SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server Express, SQLX, Exchange, Exchange Server, JET

Wufoo Challenges InfoPath for Form-Based Web Data Entry

Wufoo is a new form-based data-entry application for collecting and tracking data via the Web. A unique feature of the application is the ease with which the forms you design can be embedded in an IFrame of a Web page or blog entry by copying the IFrame's HTML from a window that opens when you click the Code link (below) to your page. (Click the image to display the full-size version.)

You also can invite individuals to fill out the form by creating a list of email recipients:

As an example, the following simple form collects new customer information in fields similar to those for the Northwind sample database's Customers table. The form validates fields marked with a red asterisk as required. Controls with missing required values turn red when the user tries to submit the form. (The extra space at the bottom of the form accommodates the depth added by error messages.)

Note: While writing this post, the Wufoo server died temporarily; however, the site appears to have been reliable for the past few days. TechCrunch's Marshall Kirkpatrick had a similar 404 experience but gave Wufoo high marks for usability.

When the user complete the entries and clicks Submit, Wufoo acknowledges the submission with a message box. If you're using a free account, which limits you to three forms, you're then hit with barrage of Wufoo advertising pages. If you mark the E-Mail New Entries checkbox and supply an e-mail address, you recieve a UTF-8 e-mail message similar to that below for each entry.

Users can enter and edit data at the Wufoo site with a form similar to that shown here, which includes a Yahoo! map mashup for the location specified in the Address control. If you want to restrict data entry to designated users, you must have a paid Wufoo account. The free account enables everyone or just the administrative user to enter or edit form data.

The Wufoo design canvas lets you choose from a variety of controls, as shown here for the New Customer form. Special-purpose controls—such as Date, Time, Phone, Address, and Website—appear to be unique to Wufoo and a few competitors' forms. You can rearrange the vertical sequence of controls by dragging, but a control occupies the entire width of the form, regardless of the width of its text box or other widget.

You also can create a basic report based on the "dataset" for form entries. Reports that involve numeric values can display basic bar and pie charts. The Report Manager lets you export reports to local files in Excel or CSV format.

Wufoo has four levels of paid services ranging from US$9 for 500 entries to US$199 for unlimited entries per month. Paid plans also provide access to customer support, the capability to upload files together with form data, an option to redirect to a designated URL after submission, and password protection of forms and reports.

Caution: Wufoo invokes a severe penalty—more akin to a fine—if you don't pay upfront for a sufficient number of form entries per month. Wufoo charges a fee of $0.05 per entry ($50.00/M) for exceeding the number of entries allowed by the plan. The ordinary cost per thousand entries (CPM) ranges from $4.60 for the $69/month to $18.00 for the $9/month plan.

Competiton from InfoPath 2007 and Access 2007

InfoPath provides very sophisticated data validation and verification features but isn't easily or cheaply adapted to hosted systems for extranets or the Internet. You can add InfoPath 2007 form rendering capability to Web pages by the techniques described in MSDN's "Hosting the InfoPath 2007 Form Editing Environment in a Custom Web Form" technical paper. However, you'll need InfoPath Forms Services running on the Web server, which requires a license for either Microsoft Office SharePoint Server (MOSS) 2007 or Office Forms Server (MOFS) 2007. (MOSS and MOFS provide InfoPath Forms Services to render the form in a browser with the XmlFormView control.) You also need to program the XmlFormView control to handle data transfer, which isn't a walk in the park. The InfoPath 2007 client is integrated into the Outlook 2007 e-mail environment. Wufoo doesn't require software licenses but submission costs can become substantial for a large organization unless you opt for the unlimited $199.00/month option.

Note: InfoJetSoft offers InfoJet Service—a .NET class library that enables publishing InfoPath 2003 SP1 forms to Web sites for user data entry in IE 6.0 or Firefox. InfoJet Server is an ASP.NET Web application that delivers InfoPath forms to users for data entry, local storage, or e-mail transmission. InfoJet EditPart is a set of Web Parts for SharePoint data entry. The license fees for InfoJet Service is $500 for 10 forms (XSNs), $1,500 for 50 forms, and $5,000 for an unlimited number of forms. There's no charge for submissions.

Microsoft Access 2007 offers an Outlook 2007-based e-mail data-entry system that uses HTML or InfoPath 2007 forms to automatically add to or edit tables of an Access 2007 (.accdb) or SQL Server 2005 [Express] database, or a SharePoint list. Users can complete HTML forms in any forms-enabled e-mail application but HTML e-mail must be enabled, which some organizations prohibit. Filling out InfoPath forms requires the InfoPath 2007 client application to be installed on the user's computer. You must send update or insert forms to specific users for completion. Outlook 2007 is required to export the returned form's data to Access 2007, so Microsoft Office 2007 Professional Edition will be required for HTML forms and Professional Plus Edition for InfoPath forms.

AJAXian and Other Web Competitors

The online forms business—if this crowded category can truly be called a business—got a lift in early 2006 from AJAX technologies. Recent AJAXian entrants include:

• Formspring offers substantially lower pricing ($50.00/month for unlimited responses) but fewer features than Wufoo.
• Form Assembly's Formbuilder has a $9.00 per month charge for unlimited responses. Formbuilder has posted a preview of a new version that offers predefined fields, more than one control per line, and table layout.
• JotForm, a script.aculo.us-based free (at least for now) form generator that lets you edit the form's HTML and CSS, as well as paste it to a Web page.
• Theblueform is a combinaton Web form and workflow (approval) designer.
• Sidewalk delivers a basic form-generation and response-storage service for $3.00 per month.
• Wyanet's WyaCracker offers free processing and features similar to JotForm, plus reports in RSS, XML, or HTML format, but the site's garish UI is likely to put off most users.
• Starterbase is a forms-based startup that offers a $49.99 per year unlimited forms and users account with CSV data import and export, Excel import, and XML export. Free accounts allow five applications and public access or three designated users.

Note: My March, 2006 "Dabble DB: The New Look in Web Databases" post describes many of the Web-based data-entry services that compete with Wufoo: DabbleDB, QuickBase, eUnify DB, Caspio Express DB, eCriteria, HTML DB, Google Base, Lazybase and Zoho Creator. The June 27, 2006 "Generate Data-Based Web Sites With Blinq" post describes a preview version of a forthcoming ASP.NET 3.0 tool for Visual Studio "Orcas" that generates in less than a minute a complete data-entry and reporting Web site for multiple relational tables in SQL Server 2005 [Express] databases.

Please leave a comment if I've missed any recently-released AJAXian form builders.

Conclusion

I agree with Marshall Kirkpatrick, Derek Punsalan, Jason Bagley, and others that Wufoo does Web data-entry forms right. Users can create and test their own custom form much more quickly than a corresponding InfoPath form, and the data is far more accessible to most information workers as an Excel or CSV file than an XML document or Access 2007 table. However, the Wufoo folks should rethink the draconian monthly fine for users who exceed their payment plan's maximum entries.

Wufoo might consider a more enterprise-scale name for their service ... but who anticipated that a company named Google would end up with a greater market cap than General Motors?

[Updated 7/11/2006 and 7/12/2006]

Technorati Tags: Wufoo, Data Entry, Forms, Web Forms, Web Data Entry Forms, QuickBase, DabbleDB, InfoPath, InfoPath 2007, InfoJet, Microsoft Access, Access 2007, SharePoint, SharePoint 2007, WSS, WSS 3.0, Office Forms Server, XmlFormView, AJAX, Formspring, Formbuilder, JotForm, theblueform, Sidewalk, Starterbase, Wyacracker, Blinq