"Free" Databases: Express vs. Open-Source RDBMSs

Open-source relational database management systems (RDBMSs) are gaining IT mindshare at a rapid pace. As an example, BusinessWeek's February 6, 2006 "Taking On the Database Giants" article asks "Can open-source upstarts compete with Oracle, IBM, and Microsoft?" and then provides the answer: "It's an uphill battle, but customers are starting to look at the alternatives." There's no shortage of open-source alternatives to look at. The BusinessWeek article concentrates on MySQL, which BW says "is trying to be the Ikea of the database world: cheap, needs some assembly, but has a sleek, modern design and does the job." The article also discusses Postgre[SQL] and Ingres, as well as EnterpriseDB, an Oracle clone created from PostgreSQL code*. Sun includes PostgreSQL with Solaris 10 and, as of April 6, 2006, with Solaris Express.** *Frank Batten, Jr., the investor who originally funded Red Hat, invested a reported $16 million into Great Bridge with the hope of making a business out of providing paid support to PostgreSQL users. Great Bridge stayed in business only 18 months, having missed an opportunity to sell the business to Red Hat and finding that selling $50,000-per-year support packages for an open-source database wasn't easy. As Batten concluded, "We could not get customers to pay us big dollars for support contracts." Perhaps EnterpriseDB will be more successful with a choice of $5,000, $3,000, or $1,000 annual support subscriptions. **Interestingly, Oracle announced in November 2005 that Solaris 10 is "its preferred development and deployment platform for most x64 architectures, including x64 (x86, 64-bit) AMD Opteron and Intel Xeon processor-based systems and Sun's UltraSPARC(R)-based systems." There's a surfeit of reviews of current MySQL, PostgreSQL and—to a lesser extent—Ingres implementations. These three open-source RDBMSs come with their own or third-party management tools. These systems compete against free versions of commercial (proprietary) databases: SQL Server 2005 Express Edition (and its MSDE 2000 and 1.0 predecessors), Oracle Database 10g Express Edition, IBM DB2 Express-C, and Sybase ASE Express Edition for Linux where database size and processor count limitations aren't important. Click here for a summary of recent InfoWorld reviews of the full versions of these four databases plus MySQL, which should be valid for Express editions also. The FTPOnline Special Report article, "Microsoft SQL Server Turns 17," that contains the preceding table is here (requires registration.) May 8, 2006 update: Computerworld published "Users Buying Into Free 'Express' Databases" on March 6, 2006. The article notes that Sybase ASE Express Edition for Linux has had 45,000 downloads since September 2004, Oracle 10g Database XE has had "hundreds of thousands" of downloads, and IBM DB2 Express-C had 50,000 downloads in its first two weeks of availability. The article states that download data was "Not available" for SQL Server 2005 Express Edition. However, Microsoft's subsequent (April 6, 2006) "SQL Server 2005 Update from Paul Flessner" letter reports that "there have now also been over 2 million copies of SQL Server 2005 Express Edition downloaded". Australia's Builder AU site published "Road test: Four databases tested" on December 23, 2005. The review purported to test "free or 'light' versions" of major RDBMSs, but compared Oracle 10g Standard Edition (very far from free) with SQL Server 2005 Express (free), IBM DB2 Express (far from free) and MySQL v4.1.14 (free "[i]f you are developing and distributing open source applications under the GPL License", otherwise requires paid support). Readers point out significant errors and omissions in many negative comments on the review. (Scroll below the Related Stories section to read the comments). SQL Server 2005 Express Edition SP-1 Advanced Features

SQL Server 2005 Express Edition with Advanced Features enhances SQL Server 2005 Express Edition (SQL Express or SSX) dramatically, so it deserves special treatment here. SQL Express gains full text indexing and now supports SQL Server Reporting Services (SSRS) on the local SSX instance. The SP-1 with Advanced Features setup package, which Microsoft released on April 18, 2006, installs the release version of SQL Server Management Studio Express (SSMSE) and the full version of Business Intelligence Development Studio (BIDS) for designing and editing SSRS reports. My "Install SP-1 for SQL Server 2005 and Express" article for FTPOnline's SQL Server Special Report provides detailed, illustrated installation instructions for and related information about the release version of SP-1. SP-1 makes SSX the most capable of all currently available Express editions of commercial RDBMSs for Windows.

OpenLink Software's Virtuoso Open-Source Edition OpenLink Software announced an open-source version of it's Virtuoso Universal Server commercial DBMS on April 11, 2006. On the initial date of this post, May 2, 2006, Virtuoso Open-Source Edition (VOS) was virtually under the radar as an open-source product. According to this press release, the new edition includes:

• SPARQL compliant RDF Triple Store
• SQL-200n Object-Relational Database Engine (SQL, XML, and Free Text)
• Integrated BPEL Server and Enterprise Service Bus
• WebDAV and Native File Server
• Web Application Server that supports PHP, Perl, Python, ASP.NET, JSP, etc.
• Runtime Hosting for Microsoft .NET, Mono, and Java

VOS only lacks the virtual server and replication features that the commercial edition offers. According to Kingsley Idehen's Weblog, "The Virtuoso build scripts have been successfully tested on Mac OS X (Universal Binary Target), Linux, FreeBSD, and Solaris (AIX, HP-UX, and True64 UNIX will follow soon). A Windows Visual Studio project file is also in the works (ETA some time this week)." VOS includes a Web-based administration tool called "Viruoso Conductor." InfoWorld's Jon Udell has tracked Virtuoso's progress since 2002, with an additional article in 2003 and a May 3, 2006 column, "Accessing the web of databases," which points to a one-hour podcast with Kingsley Idehen of April 26, 2006. A major talking point for Virtuoso is its support for Atom 0.3 syndication and publication, Atom 1.0 syndication and (forthcoming) publication, and future support for Google's GData protocol, as mentioned in this Idehen post. Yahoo!'s Jeremy Zawodny points out that the "fingerprints" of Adam Bosworth, Google's VP of Engineering and the primary force behind the development of Microsoft Access, "are all over GData." Click here to display a list of all OakLeaf posts that mention Adam Bosworth. One application for the GData protocol is querying and updating the Google Base database independently of the Google Web client, as mentioned by Jeremy: "It's not about building an easier onramp to Google Base. ... Well, it is. But, again, that's the small stuff." Click here for a list of posts about my experiences with Google Base. Watch for a future OakLeaf post on the subject as the GData APIs gain ground. Open-Source and Free Embedded Database Contenders Open-source and free embedded SQL databases are gaining importance as the number and types of mobile devices and OSs proliferate. Embedded databases usually consist of Java classes or Windows DLLs that are designed to minimize file size and memory consumption. Embedded databases avoid the installation hassles, heavy resource usage and maintenance cost associated with client/server RDBMSs that run as an operating system service. Andrew Hudson's December 2005 "Open Source databases rounded up and rodeoed" review for The Enquirer provides brief descriptions of one commercial and eight open source database purveyors/products: Sleepycat, MySQL, PostgreSQL, Ingres, InnoBase, Firebird, IBM Cloudscape (a.k.a, Derby), Genezzo, and Oracle. Oracle Sleepycat* isn't an SQL Database, Oracle InnoDB* is an OEM database engine that's used by MySQL, and Genezzo is a multi-user, multi-server distributed database engine written in Perl. These special-purpose databases are beyond the scope of this post. * Oracle purchased Sleepycat Software, Inc. in February 2006 and purchased Innobase OY in October 2005. The press release states: "Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software." Derby is an open-source release by the Apache Software Foundation of the Cloudscape Java-based database that IBM acquired when it bought Informix in 2001. IBM offers a commercial release of Derby as IBM Cloudscape 10.1. Derby is a Java class library that has a relatively light footprint (2 MB), which make it suitable for client/server synchronization with the IBM DB2 Everyplace Sync Server in mobile applications. The IBM DB2 Everyplace Express Edition isn't open source or free*, so it doesn't qualify for this post. The same is true for the corresponding Sybase SQL Anywhere components.**

* IBM DB2 Everyplace Express Edition with synchronization costs $379 per server (up to two processors) and $79 per user. DB2 Everyplace Database Edition (without DB2 synchronization) is $49 per user. (Prices are based on those when IBM announced version 8 in November 2003.)

** Sybase's iAnywhere subsidiary calls SQL Anywhere "the industry's leading mobile database." A Sybase SQL Anywhere Personal DB seat license with synchronization to SQL Anywhere Server is $119; the cost without synchronization wasn't available from the Sybase Web site. Sybase SQL Anywhere and IBM DB2 Everyplace perform similar replication functions.

Java DB from Sun Microsystems, another commercial version of Derby, comes with the Solaris Enterprise Edition, which bundles Solaris 10, the Java Enterprise System, developer tools, desktop infrastructure and N1 management software. A recent Between the Lines blog entry by ZDNet's David Berlind waxes enthusiastic over the use of Java DB embedded in a browser to provide offline persistence. RedMonk analyst James Governor and eWeek's Lisa Vaas wrote about the use of Java DB as a local data store when Tim Bray announced Sun's Derby derivative and Francois Orsini demonstrated Java DB embedded in the Firefox browser at the ApacheCon 2005 conference. Firebird is derived from Borland's InterBase 6.0 code, the first commercial relational database management system (RDBMS) to be released as open source. Firebird has excellent support for SQL-92 and comes in three versions: Classic, SuperServer and Embedded for Windows, Linux, Solaris, HP-UX, FreeBSD and MacOS X. The embedded version has a 1.4-MB footprint. Release Candidate 1 for Firebird 2.0 became available on March 30, 2006 and is a major improvement over earlier versions. Borland continues to promote InterBase, now at version 7.5, as a small-footprint, embedded database with commercial Server and Client licenses. SQLite is a featherweight C library for an embedded database that implements most SQL-92 entry- and transitional-level requirements (some through the JDBC driver) and supports transactions within a tiny 250-KB code footprint. Wrappers support a multitude of languages and operating systems, including Windows CE, SmartPhone, Windows Mobile, and Win32. SQLite's primary SQL-92 limitations are lack of nested transactions, inability to alter a table design once committed (other than with RENAME TABLE and ADD COLUMN operations), and foreign-key constraints. SQLite provides read-only views, triggers, and 256-bit encryption of database files. A downside is the the entire database file is locked when while a transaction is in progress. SQLite uses file access permissions in lieu of GRANT and REVOKE commands. Using SQLite involves no license; its code is entirely in the public domain.

The Mozilla Foundation's Unified Storage wiki says this about SQLite: "SQLite will be the back end for the unified store [for Firefox]. Because it implements a SQL engine, we get querying 'for free', without having to invent our own query language or query execution system. Its code-size footprint is moderate (250k), but it will hopefully simplify much existing code so that the net code-size change should be smaller. It has exceptional performance, and supports concurrent access to the database. Finally, it is released into the public domain, meaning that we will have no licensing issues."

eSQL 2.11 from Vieka Technology, Inc. is a port of SQLite to Windows Mobile (Pocket PC and Smartphone) and Win32, and includes development tools for Windows devices and PCs, as well as a .NET native data provider. A conventional ODBC driver also is available. eSQL for Windows (Win32) is free for personal and commercial use; eSQL for Windows Mobile requires a license for commercial (for-profit or business) use.

HSQLDB isn't on most reviewers' radar, which is surprising because it's the default database for OpenOffice.org (OOo) 2.0's Base suite member. HSQLDB 1.8.0.1 is an open-source (BSD license) Java dembedded database engine based on Thomas Mueller's original Hypersonic SQL Project. Using OOo's Base feature requires installing the Java 2.0 Runtime Engine (which is not open-source) or the presence of an alternative open-source engine, such as Kaffe. My prior posts about OOo Base and HSQLDB are here, here and here.

The HSQLDB 1.8.0 documentation on SourceForge states the following regarding SQL-92 and later conformance:

HSQLDB 1.8.0 supports the dialect of SQL defined by SQL standards 92, 99 and 2003. This means where a feature of the standard is supported, e.g. left outer join, the syntax is that specified by the standard text. Many features of SQL92 and 99 up to Advanced Level are supported and here is support for most of SQL 2003 Foundation and several optional features of this standard. However, certain features of the Standards are not supported so no claim is made for full support of any level of the standards.

Other less well-known embedded databases designed for or suited to mobile deployment are Mimer SQL Mobile and VistaDB 2.1. Neither product is open-source and both require paid licensing; VistaDB requires a small up-front payment by developers but offers royalty-free distribution. Java DB, Firebird embedded, SQLite and eSQL 2.11 are contenders for lightweight PC and mobile device database projects that aren't Windows-only. SQL Server 2005 Everywhere If you're a Windows developer, SQL Server Mobile is the logical embedded database choice for mobile applications that run on Pocket PCs and Smartphones. Microsoft's April 19, 2006 press release delivered the news that SQL Server 2005 Mobile Editon (SQL Mobile or SSM) would gain a big brother—SQL Server 2005 Everywhere Edition. Currently, the SSM client is licensed (at no charge) to run in production on devices with Windows CE 5.0, Windows Mobile 2003 for Pocket PC or Windows Mobile 5.0, or on PCs with Windows XP Tablet Edition only. SSM also is licensed for development purposes on PCs running Visual Studio 2005. Smart Device replication with SQL Server 2000 SP3 and later databases has been the most common application so far for SSM. By the end of 2006, Microsoft will license SSE for use on all PCs running any Win32 version or the preceding device OSs. A version of SQL Server Management Studio Express (SSMSE)—updated to support SSE—is expected to release by the end of the year. These features will qualify SSE as the universal embedded database for Windows client and smart-device applications. For more details on SSE, read John Galloway's April 11, 2006 blog post and my "SQL Server 2005 Mobile Goes Everywhere" article for the FTPOnline Special Report on SQL Server. Technorati: Databases MySQL PostgreSQL Ingres EnterpriseDB Great Bridge Virtuoso RedHat Derby Cloudscape Java DB Firebird eSQL HSQLDB SQL Server 2005 SQL Server SQLServer MSSQL SQL Server 2005 Everywhere Edition SQL Server Everywhere SQL Server 2005 Mobile Edition SQL Server Mobile SQL Server 2005 Service Pack 1 SQL Server Express SQL Express