Six free databases with commercial-quality features

How effective are free and open source data bases? For answers, we reviewed 6:: Microsoft SQL Server Express, PostgreSQL, Oracle's MySQL, MariaDB, Apache Derby and Firebird SQL.

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.

Watch a slideshow version of these products.

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.

Open source net results

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.

1 2 Page 1
Page 1 of 2
Now read: Getting grounded in IoT