Linux Server Setup: Part 6 - Database Management

The web has graduated from static websites consisting of individual HTML pages into a world of dynamic web applications, with pages created on the fly.  This concept is essentially why some refer to the new web as "Web 2.0".

Because web pages are now created dynamically, at the very moment a user accesses a URL, the information for those pages must be stored elsewhere.  Rather than having HTML code stored in documents, dynamic sites rely on databases.  A database can take many forms.  It can be a single XML file, a series of flat files, an SQL file, or an RDBMS (relational database management system), often taking the form of a database server.

What all of this means is that it is extremely important for any dedicated server that hosts websites to offer as many database options as possible or, at a minimum, at least one.

A dynamic website functions using a scripting language, such as Perl or PHP.  The developer of the site writes a call function into the script that will log into the database, select the appropriate data, and then process the results, either by printing the data as-is, or manipulating it according to the script's specifications.  Those specifications may include the creation of a new page to display the content. 

Thus, a dynamic website, powered by a database, can create an infinite number of web pages, and with server-side scripting, the information is translated into HTML code and sent to the user's browser, requiring no additional software or processing on the user's end of the transaction.

Database Management Systems

A server uses a database management system to determine the method of data storage, access, and retrieval.  The following are some common database systems.  Some require a database server (system service or daemon) to run at all times, in order to function.  Others require only the data files themselves.

MySQL is one of the most popular relational database management systems and is open source and cross-platform.   All server Linux distributions offer binary packages for MySQL, and there are numerous MySQL graphical frontend tools for the desktop and the web.  Ownership of the MySQL trademark has changed hands more than once, and the future of its open source license is uncertain. There are also other forks with similar goals in mind.  MySQL is part of the famous LAMP stack, and is used by small and large organizations.

PostgreSQL is an object-relational database management system, which is free and open source.  Many Linux distributions distribute binary packages for it, and most dynamic web applications that support MySQL also have support for it.  PostgreSQL, like MySQL, has many graphical frontends.  It has won numerous awards for its performance and is used by many large websites.

SQLite is an embedded database management system that is housed inside a set of system libraries, rather than a database server.  The code for SQLite has been released into the public domain.  The SQLite library must be linked into the web application running it, which differs considerably from database servers, which run independent processes.  Because it is small and lighter in terms of power required to run it, SQLite is used on mobile platforms and other embedded systems, but not frequently used on web servers.

Cassandra is described by its developers as a "highly-scalable second-generation distributed database."  It was originally developed by Facebook and then released as open source in 2008.  It is now maintained by Apache developers.  Many prominent social media websites, such as Digg, Twitter, and Reddit have recently migrated to Cassandra.  It is designed to answer some of the scalability problems large websites had with traditional RDMS.

NoSQL refers to any database that serves as an alternative to the traditional relational databases.  Cassandra is the latest headline-making example, while Google's BigTable and Amazon's Dynamo are other popular examples.  Because the term is broad and there are far more than a handful of databases in this category, it truly a category for a broad spectrum of non-relational databases, rather than specifically non-SQL.

Oracle Database is a proprietary relational database system produced by Oracle Corporation.  Although the license is proprietary, Oracle does distribute a no-cost version on its website called Oracle Database Express Edition.  It is available for most major Linux distributions.

The actual procedure for setting up a database will vary considerably, depending on which database system you choose.  There are, however, some universal concerns you should address, regardless of your choice.

For small to medium web servers, running a database system on the same machine may be the only economical solution, and most of the time, this scenario will function sufficiently.  As the number of website visitors and demand on web applications increases, however, you may be faced with a situation where the database processes are putting too much stress on the server.  To avoid this eventual problem, it is advisable to run the database from a separate machine, when it is economically feasible.

You should also consider overall scalability and if the database system you intend to choose will fit in your technology plan and your business plan. 

Finally, compatibility is always factor.  Just because a database system supports Linux in general does not mean it will run well on your server with your Linux distribution.  Some database systems will also require extensive reconfiguration of the server.  Moreover, if you already have web applications in place or plan to download or purchase them, you must make sure that the applications are compatible and can be optimized for your database system.