Six free databases with commercial-quality features

Free or open source databases run hundreds of millions of public-facing and private applications worldwide, but how effective is this technology and how do these products compare? For answers, we reviewed six popular free or open source database products: Microsoft SQL Server Express, PostgreSQL, Oracle’s MySQL, MariaDB, Apache Derby and Firebird SQL.

When selecting candidates for our review we chose fully featured relational database management system (RDBMS) products with features such as procedures, functions, triggers and cursors. While we did not set out to perform scientific benchmarking, we did test large tables. For the test data, we used a truncated log file from one of our Web servers containing a million-plus records. The same test data was used across all six products. We ran standard tests such as bulk inserts/imports and performed data manipulation tasks via SELECT, UPDATE AND DELETE statements.

For our test environment we used 64-bit Dell rack servers running Windows Server 2008 R2. It should be noted that with the exception of MS SQL Server Express, all database products we tested are cross-platform capable.

The runners-up in our review are Microsoft SQL Server Express and Oracle’s MySQL. From a GUI standpoint, SQL Server offers the best tools and the ability to upgrade to commercial editions should you need additional features and firepower that may be appealing to many database admins. The migration argument also applies to MySQL, as Oracle now offers a variety of commercial editions of MySQL. The top spot eluded MySQL for a couple of reasons; one is the lack of any useful native management tool. Second, although Oracle keeps assuring developers that it will support the core community edition of MySQL on an ongoing basis, Oracle has already started reserving certain features for its commercial versions.

Which brings us to our top choice, MariaDB, an open source product developed by essentially the same original team that developed MySQL before it was acquired by Sun, then Oracle. MariaDB can go everywhere MySQL can go, and when Oracle adds new closed source features to MySQL, MariaDB concomitantly adds some of the same features as open source. There is also the issue of performance and in our test we found MariaDB to perform just a notch better than all the others.

Rounding out our score card is PostgreSQL, Firebird and Apache Derby, in that order. Here are the individual reviews.

Microsoft SQL Server Express 2012

SQL Server Express is a free version of Microsoft’s flagship database, SQL Server. Targeted at smaller-scale and embedded applications, Microsoft SQL Server Express is a feature-rich product that provides many of the core features of the commercial version with some limitations on scalability.

Specifically, these include limits on CPUs (one socket with max four cores), memory (1GB utilized, server can have more installed) and size of database (10GB). Of the six products we tested, this is the only one with any meaningful limits, although for many small to midsize organizations this should not be an issue.

The installer for SQL Server Express presents an installation center from where you can read the release notes, run tools such as the Configuration Checker to make sure your server has the prerequisites installed and install the actual server, either by running an upgrade wizard or performing a fresh install.

For this review we chose the latter. SQL Server installations are notoriously time-consuming and SQL Server Express is no exception. There are several choices to be made during the install such as which security model to use (we selected mixed SQL and Windows mode).

The SQL Server Management Studio is a comprehensive management interface that provides administrators and programmers the tools they need to manage servers and databases as well as develop database business intelligence (BI). For developers, it can also be integrated with other tools such as Visual Studio and SQL Server Developer Studio.

Inserting a million-plus records took just over one minute and, for comparison, the same data inserted on a standard version of SQL Server took about 45 seconds. Retrieving the first 100,000 records took 10 seconds using SQL Server Express and three seconds on the standard version. However, when we were working with smaller datasets we found the Express version performing nearly as well as the Standard version. For instance, updating a single record took only fractions of a second longer with Express as compared to Standard using our million-row test table.

Security features include basic auditing and user-defined roles controlling access to data and objects. Support for SQL Server Express is available through the Microsoft Developer Network and a variety of online support forums and blogs.

While SQL Server Express may not have all the features — such as high availability and clustering — of its commercial siblings, it is a mature and solid database server that will deliver and perform well for a large percentage of database applications. For admins who prefer a GUI to manage their database, they’re going to be hard-pressed to find a better management interface than the one offered by Microsoft; the only one we found that came close was the pgAdmin from PostgreSQL.

MariaDB

MariaDB is essentially a binary drop replacement for MySQL created by the same developer as MySQL, Michael Widenius. With some uncertainty about the future of MySQL, now that it is owned by Oracle, there is a large group of community developers who would like to maintain an open source version with all features. The extent of the compatibility is such that MariaDB does monthly merges with the MySQL code base to make sure any features and bug fixes Oracle puts out are added to the latest version of MariaDB and vice versa. MariaDB is published under the GNU public license, and we installed Version 5.5 on Windows Server 2008 R2.

We used HeidiSQL 7.0 front-end client to connect to MariaDB. This gives you a user interface from which you can perform a variety of tasks, such as create new databases, tables and views, as well as import/export data in several formats. After setting up our test database and table we loaded our million-plus rows from a CSV file using the low-priority setting and the data was inserted in less than a minute. MariaDB returned the first 100,000 rows in less than two seconds when using a select statement. As expected, when working with smaller subsets of data we found performance well within the acceptable range. We were able to delete and update statements on single rows in fractions of a second.

Although our testing was not scientific, we did use the same test data for all database servers and overall we found MariaDB performance to be a notch faster than MySQL when working with individual rows (selects, deletes and updates).

As with MySQL, security in MariaDB is managed through Access Control Lists for access to all objects and operations. In addition, it includes several encryption functions that can be used for data storage and retrieval, along with SSL for encrypted communication between client and server.

As for support, we found the MariaDB website responsive to most questions. There are also several online forums dedicated to MariaDB along with commercial support and consulting available from Monty Program.

There is a lot to like with MariaDB; in particular we appreciate that the company includes in its open source version many of the closed source features of the MySQL commercial edition. One of these features is sub-queries, which did essentially not work well in MySQL, and MariaDB finally resolved this a while back. Also, when a major security hole was found in MySQL/MariaDB earlier this year, the MariaDB community found a solution and notified the community to this vulnerability.

MySQL

MySQL is one of the most popular open source databases. It is included as part of a variety of LAMP (Linux, Apache, MySQL, PHP) stacks, and large organizations such as Wikipedia, Twitter and Facebook use MySQL. MySQL was originally developed in Sweden in the mid-’90s, was sold to Sun in 2008, and has been owned by Oracle since it acquired Sun in 2010.

MySQL vulnerability allows attackers to bypass password verification

There has been a lot of speculation as to what Oracle’s intentions are with regard to MySQL. According to the latest indications from Oracle, it is committed to supporting and updating the core functionality of the community edition of MySQL for the foreseeable future. This obviously makes sense to Oracle considering the vast current installed base, as it gives the company a unique position to upsell several of its commercial offerings such as Oracle Linux, Oracle VM and of course its commercial versions of Oracle MySQL. However, the question still remains as to how many new features will be included in the core open source version and how many will be reserved for the commercial offering.

That being said, since acquiring Sun, Oracle has made several improvements in the way MySQL runs on Windows, and it is now truly a cross-platform database. We installed the community edition, Version 5.5.8, on Windows Server 2008 R2 as part of a WAMP installation. We decided to use both phpMyAdmin and HeidiSQL to manage our MySQL installation. There are also commercial management tools available such as the MySQL Enterprise Edition.

After importing our test data we ran through our basic tests and found most of them perform very similar to MariaDB, as expected. While working with individual rows, we found MySQL to be slightly slower than MariaDB, even if the table, data, indexes and hardware were the same. Oracle has just announced the release candidate for Version 5.6 and this is supposed to have an improved optimizer and other updates that will improve performance. It should be noted that the differences were in the hundredths of a second range.

MySQL security is managed through Access Control Lists for access to all objects and operations. It also supports SSL for encrypted communication between client and server together with data encryption functions that can be used to store and retrieve encrypted data. Support and documentation for MySQL is readily available from Oracle (both free and paid) and from a variety of third-party sources on the Web, ranging in quality from sketchy to excellent.

It is hard to not like MySQL based on its ease of use, performance and sheer ubiquity. According to Oracle, nine out of the top 10 websites in the world (we assume as measured by traffic) use some form of a MySQL database. Many of the commercial hosting services offering LAMP and WAMP stacks have built excellent Web front-ends to MySQL, making it easy to manage as part of a Web solution.

PostgreSQL

PostgreSQL has its roots in the Ingres project at UC Berkeley in the 1980s and the first version of PostgreSQL was released in 1995. It is open source and released under the PostgreSQL license. PostgreSQL is essentially an RDBMS, but with an object-oriented database model (ORDBMS).

We installed PostgreSQL Version 9.2 on Windows Server 2008 R2. The installation is very straightforward with only a few options available, such as super user password, listening port and localization. Once the core installation has completed you have the option of launching the Stack Builder that allows you to download and install additional applications, tools and drivers.

PostgreSQL includes a management interface called pgAdmin. Not unlike the MS SQL Server Management Studio, pgAdmin has a robust set of features that is easy and intuitive to navigate. The database and test table with constraints was easy to create. We imported our million-plus rows of test data using the CSV import feature in less than 30 seconds. Retrieving 100,000 rows took less than half a second and working with individual rows yielded good results; selecting, updating and deleting individual rows anywhere in the table took around half a second. Not quite as fast as MariaDB and MySQL, but certainly better than what we observed with SQL Server Express. One minor annoyance was the need to put field names in quotes if they contained uppercase characters.

PostgreSQL handles authentication through a variety of means from trust authentication, Kerberos to LDAP. Communication between client and server can be encrypted via SSL and the database itself can also be encrypted using the pgCrypto extension.

In addition to extensive documentation and FAQs, PostgreSQL has an active community that offers support to users. Commercial support and hosting is available worldwide through a long list of companies listed on the PostgreSQL website.

Of the features of PostgreSQL we found the pgAdmin management interface to be one of the biggest pluses. PostgreSQL is a feature-rich database server and it is included in most of the major Linux distributions as an available package. We also found the performance of PostgreSQL to be nearly on par with MariaDB and MySQL.

Apache Derby

Apache Derby is an open source relational database from the Apache project. Written in Java, Apache Derby will run in any Java virtual machine and has a very small footprint (less than 3MB), including an embedded JDBC driver. It is released under the Apache License Version 2.0.

Apache Derby started as Cloudscape in the mid-1990s, then it was acquired by Informix before being re-branded as IBM Cloudscape when IBM took over the assets from Informix. In 2004 IBM contributed the Cloudscape code to the Apache project and in 2005 the first Apache Derby version was released as Version 10.1.1. Apache Derby can be run in two modes, either as a fully functional embedded database engine (for instance as used in Apache Tomcat and Red Hat application server) or as a traditional client-server database using the Derby Network Server.

We installed Version 10.9.1 on a Windows Server 2008 running the Oracle Java run-time Virtual Environment (JRE) Version 1.7. It is important that the JRE is installed prior to installing Apache Derby and the install does require a few commands issued from the DOS prompt. Apache Derby ships with an interactive scripting tool named “ij.” It works with the embedded Derby JDBC driver or a client JDBC driver. Using the command prompt we were able to create a test database and empty table from the command prompt using just a few short commands.

After creating our test table, we decided to utilize the built-in bulk insert procedure to insert our million-plus test rows. The bulk insert worked fine except we ran into a similar problem we had with PostgreSQL with uppercase vs. lowercase in the execute syntax. Our table was named test1, all lower case, but the parser (and documentation, after some online research) insisted we enter it in all upper case like this:

CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,’TEST1′,’c:tempSampleData.txt’,’,’,null,null,0);

After fixing the case problem, the data import took just a few seconds and a select statement to retrieve the first 100,000 records ran in less than five seconds. Just as we observed with the other database servers, working with individual rows of data proved to be quite efficient and our select, update and delete statements completed in fractions of a second. Although Apache Derby did not deliver quite the same speed as MariaDB and MySQL in our tests, but the differences were small, ranging from one-tenth to one-hundredth of a second slower.

Security features include user authentication and authorization allowing for object-level access restrictions. Apache Derby databases can also be encrypted either when they are created or encryption can be added to existing databases.

The small footprint of Apache Derby makes it an excellent choice for embedding in Java applications such as those running on PDAs or cell phones. The Apache website provides comprehensive and current documentation and there is also a fairly active community of blogs and forums dedicated in whole or in part to Apache Derby.

Firebird

Firebird has been in existence in some form or another since 1981 and is currently based on the highly regarded InterBase code released by Borland in 2000. The current modules are released under the Initial Developer’s Public License. Firebird runs on Linux, Unix and Windows. We reviewed the latest stable release, Version 2.5.1, on a Windows Server 2008 R2. Installation is very straightforward and completed in a few minutes with few options to decide during installation. In addition we decided to install FlameRobin, a cross-platform management tool for Firebird.

FlameRobin is not as feature-rich as a pgAdmin, SQL Server Management Studio or HeidiSQL, but it does offer some basic features such as the ability to create databases, tables and other objects. Objects are created by providing sample T-SQL code as opposed to providing an interface where you can type in field names and such. However, once you have created an object such as a table, it can be edited using a GUI. As it turned out, the more we worked with FlameRobin, the more useful features we happened upon.

After completing the installation, we created a test database with a table to hold the test data. As there is no real bulk insert option nor an import feature, we ended up creating a “temporary” table to hold the external data and used this to insert into our real table. This process was not exactly elegant, but it did get the job done after some back-and-forth with permissions issues. As is often the case with free and open source software, the documentation is not always up-to-date or presented as well as with commercial software, but a well-formulated search engine phrase eventually yields results for most tasks.

Once the test data was loaded, we conducted our usual test, but ran into problems when attempting to select large numbers of rows (100,000-plus) using SELECT statements both from the Firebird ISQL tool and also using FlameRobin. The ISQL tool simply crashed whereas FlameRobin did return the records, but since these were returned just a few hundred at a time it was hard to get a feel for performance. While working with smaller subsets we found Firebird to work efficiently and we were able to select, update and delete single rows in fractions of a second.

Some of the security features of Firebird include object-level access control and the ability to remove source code for triggers, procedures and views. Firebird does not currently offer native encryption, but there are third-party tools that do. As for documentation, Firebird has an excellent FAQ website along with a variety of other documentation available on the Firebird website.

Overall we were impressed with Firebird SQL as the vendor has added additional features to an already excellent database, InterBase. For example, the most recent releases of Firebird SQL include recursive queries, procedural triggers to remote DB access and multi-threading. A new major release, Version 3.0, is in the works, with an alpha release expected to be available later this year. This will include features such as the ability to write stored procedures in Java and C++.

Conclusion

Bottom line, all the products we reviewed are very capable database servers and selecting one will depend on a variety of factors such as existing infrastructure and scripting language, size of the data to be stored/retrieved, whether the database is to be embedded, and how much vendor support is needed. Once you’ve selected a candidate, be sure to follow the best practices recommended by the vendor to secure the product, together with the host OS, before it is used in a production environment.

Perschke is CSO for Arc Seven Technology. She is also an experienced technical writer, and has written numerous white papers for a number of organizations, including Fortune 500 companies. Susan can be reached at susan@arcseven.com.