Comparing DB Engines

Choosing which Database Engine to use in a real life project is not an easy task to achieve, you need to take into consideration factors like: 

-          Which one is the target platform ? 

-          Is going to be a web application, client / server or a box application? 

-          How much budget do I have to spend on an DB Server? You have a lot of options here from 0 € to  XXXXXXX €. 

-          How big is going to be my DB? How many concurrent users do I have to support? 

-          How easy is to use? Am I prepared to fight against black screens and scripting jargon? 

-          (…) 

We are going to check the pros and cons of the DB Engines supported by the dbschemaeditor free modeling tool: 

-          SQL  Server. 

-          Oracle. 

-          My SQL. 

-          PostGre. 

-          SQLite. 

-          Firebird. 

This article will give you some ideas, but of course once you are done ask your colleagues, google for more info and before taking a final decision  download trials of the engines that you have selected and play with them (well and of course check for prices or possible hidden policies on the free open source one’s).  

SQL Server 

If you are targeting just for the MS platform, SQL Server  is one of the most well known, powerfull and friendly engines that you can find… on the other hand be prepared to spend money in most of the cases J (http://www.microsoft.com/sqlserver/2008/en/us/pricing.aspx), you have the following options: 

-          If you are developing a web application (e.g. ASP .net), you can hire a hosting and take advantage of the hosting companies infrastructures and licenses (an ASP .net + SQL Server hosting can cost you about 20 $ per month). 

-          If your company is small or medium size, you can use the SQL Server Express version, it’s free and work fine, it has some limitation, for instance DB size is limited to 4 Gb (hehe… for my case reaching a giga I’t s a challenge but working for big enterprises you can fin that even a datamart is several TB J). 

-          For medium and big companies an SQL Server Standard license can bring you a lot of power, but cost money of course… (check Ms Site or a dealer for pricing license, you need to take into account as well Windows Server licences), it even allows some clustering. 

-          For real big projects having the enterprise edition is a must, we are talking here about handling hundreds of users concurrently, accesing terabytes of information and having a farm of servers dedicated to some very big databases. 

Another strong point is that SQL Server ships a developer version that can be installed on an XP / Vista client operating system, letting you develop on your local machine without having to mess with servers or virtual machines. 

Factor 

Score 

Comments 

Power 

 

SQL Server is a quite powerful engine (well the free express version is limited). 

Cost 

 

There are several version of SQL Servers, enterprise (quite expensive, but quite powerful), standard (has some limitations on clustering), and express (free one but limited to Db’s that are below 4 Gb’s size). 

Easy to install 

 

The SQL Server installation is wizard base, but it contains so much products that sometime stucks on the installation. Is not intended for a final user to install it. 

Easy to use 

 

SQL Server has a powerful and easy to use IDE (a good management console, management studio…), you can create databases just by dragging and dropping and using a visual interface (no need for black windows J) 

Supports relations 

 

Of course, SQL Server is quite advance, and implements ANSI SQL and powerfull T-SQL 

Ideal solution for web apps 

 

Quite good engine to support web applications (e.g. a dynamic web site). An affordable solution if you are contracting a shared hosting (a decent ASP .net + SQL Server hosting can be around 20 $ per month). 

Allows remote access 

 

You can reach a SQL Server via tcp, and use the same tools, some hosting companies offers you access to your DB from your local computer using the standard SQL Server Management studio. 

Ideal solution for Desktop apps 

 

If you are planning a client / server approach that’s ok, but if you want an independent app that installs silently on a single computer, maybe is not that straightforward (rather think of SQLite or Ms Access Jet Engine) 

Supports clustering / balancing. Can be scaled 

 

 

Additional Features 

 

With SQL Server you will get for free a reporting services portal (SSRS), an OLAP engine (SSAS), and an ETL framework (SSIS). 

Multiplatform 

 

Just Ms platforms. 

Oracle

 Oracle has been for years "the db engine"... it's the one with most reputation and well... an expensive one, you will find it in big enterprises (savings bank, government, ...). Nowdays, products like Microsoft SQL Server are a serious competitor for this product on the commercial side, and other open source engines make a good job as well.

  One of the dark sides of Oracle is that it fit in the DB Admin world, normally it's no easy to manage for a non DB guru.

Factor 

Score 

Comments 

Power 

 

Oracle... the db engine (there's a free version limited to 1 Gb DB). 

Cost 

 

 enterprise (quite expensive, but quite powerful), standard and a free one (nice to play with it but not for a serious development). 

Easy to install 

 

More oriented to db administrators. 

Easy to use 

 

Same as previous point, DBA's or Data Architects will be the ones more happy using Oracle.

Supports relations 

 

Yes

Ideal solution for web apps 

 

Yes, quite powerful. 

Allows remote access 

 

Yes. 

Ideal solution for Desktop apps 

 

If you are planning a client / server approach that’s ok, but if you want an independent app that installs silently on a single computer, maybe is not that straightforward (rather think of SQLite or Ms Access Jet Engine) 

Supports clustering / balancing. Can be scaled 

 

 

Additional Features 

 

  Contains even a web server.

Multiplatform 

 

Yes

My SQL

MySQL is well known for its use with web applications. This is due to the popularity of PHP, which is often combined with MySQL.

We can find MySQL in many high-traffic web sites like Flickr, Facebook, Wikipedia, Google, Nokia, Sourceforge ... where it is used as data storage and logging of user data.

MySQL Server is available as free software under the GNU General Public License (GPL).
If the use of MySQL is incompatible with the GPL, a proprietary version must be purchased (MySQL Enterprise). Buyers of MySQL Enterprise have access to binaries and software that is certified for their particular operating system, and access to monthly binary updates with the latest bug fixes. The MySQL Enterprise subscriptions include a GPL version of the server, with a traditional proprietary version available on request at no additional cost for cases where the intended use is incompatible with the GPL.

For medium and big projects, MySQL offers a product called MySQL Cluster that provides a real-time open source transactional database designed for fast, always-on access to data under high throughput conditions. The main features of MySQL Cluster are:

  • Instantly Responsive and Durable

  • Linearly Scalable

  • Easily Maintainable

  • Open Flexibility/Lower TCO  – Open source and dual licensing significantly lower total cost of ownership without any vendor lock-in

Factor

Score

Comments

Power


MySQL is a quite powerful engine, and is SQL ANSI compliant

Cost


MySQL is available as free software under the GPL, and as proprietary software but at limited cost

Easy to install


The MySQL Server installation is wizard base, and provides default values suitable for many purposes. Custom installations is intended only for experts

Easy to use

 


MySQL has an administration tool called "MySQL Administrator" that provides server administration through an intuitive Graphical User Interface that allows one to configure, monitor and start and stop a MySQL server, manage users and connections, perform backups, and a number of other administrative tasks.

Supports relations


MySQL supports a broad subset of ANSI SQL 99, as well as extensions. It has ACID

compliance using the InnoDB, BDB and Cluster engines.

Ideal solution for web apps


 Quite good engine to support web applications. MySQL is also an affordable solution if you are contracting a shared hosting where you can build PHP and MySQL based web sites

Allows remote access


By default, MySQL database server remote access is disabled for security reasons, but is very easy to provide the remote access from home or from web server.

Ideal solution for Desktop apps


 MySQL is a good solution for a client / server approach, but it is not very suitable for a single user environment. We can think about MySQL as a complex tool that needs a professional administration.

Supports clustering / balancing. Can be scaled


MySQL offers this features using Cluster engine

Additional Features


With MySQL you only get an administration program and a query browser as gui tools. Anyway, you can find a lot of utilities in the net; some of them are free, others shareware or commercial software.

Multiplatform


MySQL is present in a lot of platforms: Linux, Windows, Solaris, IBM AIX, IBM i5/OS, Apple Mac OS, FreeBSD, ...


 

PostgreSQL

PostgreSQL is an object-relational database management system (ORDBMS) with more than 15 years of active development. It has won  a strong reputation for reliability, data integrity, and correctness. It is extremely common for companies to report that PostgreSQL has never, ever crashed for them in several years of high activity operation.
PostgreSQL is not controlled by any single company, but has a global community of developers and companies to develop it.

PostgreSQL is really free software, as it is released under a BSD-style license; this means that you can use its source code for commercial purposes and that there is no associated licensing cost for the software.
One of the features that makes Postgres different from other Open-Source database managements system is a built-in language called PL/pgSQL that is similar to Oracle's procedural language PL/SQL and enables you to write PostgreSQL functions.
We can mention as prominent users: Yahoo, for web user behavioral analysis - Afilias, domain registries for .org .info and others - Sony online, multiplayer online games - BASF, shopping platform for their agribusiness portal - hi5.com, social networking portal - Skype VoIP application, central business databases - Sun xVM, Sun's virtualization and datacenter automation suite - NextBus, provider of arrival-time prediction and GPS tracking systems for public transportation - Snooth, the world's most comprehensive wine database, featuring millions of reviews and hundreds of thousands of wines.

 

Factor

Score

Comments

Power


PostgreSQL is a quite powerful engine 
 

Cost


PostgreSQL is free software under a BSD-style license. There is no associated licensing cost for the software.

There is an Enterprise product called "PostgreSQL plus" with additional features (commercial version)

Easy to install


The installation under Windows is Wizard-based. It can be easily installed under Linux if you download binary packages.

Easy to use

 

There is an open source administration tool for PostgreSQL called pgadmin. It is designed to answer the needs of all users, from writing simple SQL queries to developing complex databases through a graphical interface.

Supports relations


 Postgres' SQL implementation strongly conforms to the ANSI-SQL 92/99 standards, and supports most of the major features of SQL:2003 standard.

PostgreSQL is ACID-compliant

Ideal solution for web apps


Quite good engine to support web applications, but it is not as widely spread as MySQL. It is very easy to find PostgreSQL hosting at good prices too.

Allows remote access


PostgreSQL allows remote access using TCP/IP connections.

Ideal solution for Desktop apps


Good solution for a client / server approach; we can find some well-known open-source ERP's powered by PostgreSQL.

It is not very suitable for a single user environment.

Supports clustering / balancing. Can be scaled


PostgreSQL is highly scalable.The scalability of PostgreSQL is extensive and proven. PostgreSQL is capable of storing billions of records, terabytes of data, and processing thousands of transactions per second, all while keeping your data safe and secure.

Additional Features


With PostgreSQL you only get the database management system. You can download several tools like pgadmin.

Multiplatform


PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.

SQLite

SQLite is an embedded SQL database engine. It has four main features:

  • self-contained: this means that it requires very minimal support from external libraries or from the operating system.
  • serverless: Programs that use SQLite require no administrative support for setting up the database engine before they are run. Any program that is able to access the disk is able to use an SQLite database.
  • zero-configuration: There is no "setup" procedure. There is no server process that needs to be started, stopped, or configured.
  • transactional: SQLite supports transactions  but in a simple way. SQLITE locks the entire file of the database during a transaction (i.e. all other write access is simply forbidden).

SQLite is considered the most widely deployed SQL Database. We can find it in a lot of environments, like servers, desktop PC,  cellphones, PDAs, MP3-players, and set-top boxes. We can find SQLite in applications like:

  • Mozilla Firefox
  • Mac computers, each of which contains multiple copies of SQLite
  • Websites that run PHP which has SQLite built in
  • Skype client software
  • In newer versions of the SymbianOS.
  • AOL email clients
  • Solaris 10 installations, all of which require SQLite in order to boot.
  • Mc-Affee anti-virus software.
  • iPhones
  • .....

SQLite is in the public domain. Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original SQLite code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.

Factor

Score

Comments

Power


One of the main features of SQLite is its simplicity. SQLite lacks of characteristics like high concurrency, fine-grained access control, stored procedures, tera- or peta-byte scalability ...

The use SQLite is well suited in situations where simplicity of administration, implementation, and maintenance are more important.

Cost


SQLite is free

Easy to install


There is no installing process at all

Easy to use

 

actually, SQLite is a C-library, so the user is not involved in manipulating directly the database. There is not an administration tool to simplify the use of SQLite.

Supports relations


SQLite does not implement foreign keys. Anyway, this feature can be achieved using triggers.

Ideal solution for web apps


SQLite usually will work great as the database engine for low to medium traffic websites (no more than 100 hits/day)

Allows remote access


SQLite has not been designed for allowing remote access

Ideal solution for Desktop apps


SQLite has been used with great success as the on-disk file format for desktop applications.

SQLite is also a good choice for Embedded devices and for Internal or temporary databases.

SQLITE is not a good solution for a client / server approach if you have many client programs accessing a common database.

Supports clustering / balancing. Can be scaled


SQLite can't be scaled due to its simplicity

Additional Features


There are no additional features. Users can find several utilities in the net as freeware, shareware and commercial software.

Multiplatform


SQLite is Cross-platform: Unix (Linux and Mac OS X), OS/2, and Windows (Win32 and WinCE) are supported. It's easy to port to other systems

Firebird


Firebird is an open source relational database management system offering many ANSI SQL standard features that runs on Linux, Windows, and a variety of Unix platforms. It is based on Interbase 6 (commercial product of Borland), but since Firebird 1.5 the code has been largely rewritten.

Firebird is released under under an IDPL (Initial Developer's Public License); this means that any one can build a custom version of Firebird, as long as the modifications are made available, under the same IDPL licensing, for others to use and build on; and, in second place, that there are no fees for download, registration, licensing or deployment, even you distribute Firebird as part of your commercial software package.

Firebird has a large group of users: Sourceforge downloads rolled over the million a long time ago. Mainly, they are software companies or independent software developers that are using Firebird in private software products.

 

Factor

Score

Comments

Power


Firebird is a quite powerful engine

Cost


Firebird is free; there are no fees for download, registration, licensing or deployment, even if you distribute Firebird as part of your commercial software package.

Easy to install


Firebird can be installed easily under Windows through the Firebird Installer.

Installation on Linux and other Unix-like platforms can be achieved using .rpm packages or the .tar.gz kit if your distribution cannot process rpm files.

Easy to use

 

Firebird only comes with a command-line user management tool called gsec. There are

third-party GUI utilities to perform administration tasks, like ibWebAdmin (GPL) that is a web front-end for the Firebird or InterBase database server.

Supports relations


Firebird supports foreign keys, and is ACID compliant.

Ideal solution for web apps


Firebird is a good solution for Web apps.There is no limit of connections in a Firebird server - this parameter is often limited by the resources in your server. However, most operating systems have a hard-coded limit of 1024 connections per TCP/IP port. You can exceed this limit using some connection pooling technique.

Allows remote access


Firebird allows remote access through TCP/IP connections.

Ideal solution for Desktop apps


Good solution for a client / server approach, but it is not very suitable for a single user environment.

Supports clustering / balancing. Can be scaled


Currently, if server’s host has more than one IP, Firebird uses only first one. There is a patch that lets it use up to eight IP.
Version 2.5 will have a scalable multi-threaded mode with per-connection caches.

Additional Features


You don't get additional features with Firebird; anyway, there are third party tools, including GUI administrative tools and replication tools

Multiplatform


Firebird runs on Linux, Windows, and a variety of Unix platforms.