The heart of any website or application today is its database. With many different options available for today’s web developers and designers, it can be difficult to decide which is best suited for a specific project.
This month’s article covers some of the currently available database technologies, included some exciting new possibilities. We’ll also look at a few useful tools to help with database administration and functionality.
SQL Database Solutions
SQL (Structured Query Language) is the language used for many popular relational database management systems. SQL statements perform tasks such as updating or retrieving data on a database. As the base in a variety of systems, such as Oracle, MS SQL Server, Sybase, etc., SQL is often augmented with proprietary extensions/apps to move beyond basic database operations. Here are five popular SQL-based database solutions:
This extremely popular database platform is one that many developers cut their teeth on. It has a free community version and wide variety of available tutorials, blogs devoted solely to MySQL development, and support forums. For larger-scale applications, you may need a commercial version. The MySQL Workbench tool is particularly useful for beginners with its GUI interface and syntax creation assistance – be sure to download it as well. MySQL can run on Windows, Mac, and Linux and is most commonly used by PHP developers.
If you have Microsoft Office, you may already have this database. Often a good choice for those new to database development, its visual design interface helps you learn what syntax is needed to make specific changes and/or create specific tables. A one-month trial gives you time to check it out completely if you don’t already own MS Office. It’s most often used for ASP/ASP.net sites and doesn’t have complete flexibility insofar as the operating systems it can be installed on. Unless you expect light traffic for the site you are developing, you may see poor performance of a lot of simultaneous connections to tables in the database. Consequently, Access is often judged as best for beginners and small site applications.
BM’s DB2 comes in a number of different configurations, and features SAP workload configuration and 98% compatibility with Oracle. Their BLU acceleration reduces the need for indexing, tuning or aggregates, letting you get the analytics and reports you need quickly. It also has built-in disaster recovery, clustering technology, Time Travel Query, and mobile database sync/support. A free community version called DB2 Express-C is available, along with online tutorials, articles and examples to assist. IBM offers a 90-day free trial of the other versions of DB2 that are available.
Like IBM’s DB2, Oracle’s full-featured database solution that was originally designed for enterprise systems comes in a free community version called Oracle Express. This allows developers to train, develop applications, and deploy them in test environments before upgrading to a paid version — all with an easy migration. It is most often used by developers working with PHP, Java, .NET, XML, and Open Source applications. It will store up to 11 GB of data and uses 1 GB of memory and 1 CPU on the host.
SQLite is different from other solutions in that it is an in-process library with an embedded SQL database engine. Simply written to a disk file, the database file format is platform-independent with no separate server process. This makes it quite popular, especially given that the code is in the public domain and the compact library is usually less than 500KiB (and can be as low as 300KiB). Running in minimal stack space, it has good performance in cellphones and similarly restricted-memory devices.
No SQL Database Solutions
As the cost of online storage decreased dramatically, developers began to use No SQL database environments. Taking advantage of the ability to use JSON led to greater flexibility in data modeling and increased scalability and speed, given that applications use a single distributed database. Auto-sharding capability can also support distributed queries and integrated data caching. Four examples of No SQL databases include:
Taking its name from the word “humongous,” this No SQL database also uses JSON-style documents with dynamic schemas. It features auto-sharding to enhance horizontal scaling, built-in replication, and GridFS for large file storage. If you are looking for an enterprise solution instead, related support and consulting is available.
Although similar to other No SQL offerings, Cassandra differs in the breadth of its architectural features, being fully distributed across all nodes. It is particularly noteworthy for its support for replicating across multiple datacenters, which can be useful during regional outages. Its storage engine only appends updated data, ensuring quick updates to the dataset, and has locally managed storage. All of these combine to decrease potential problems. Java 7 is required.
OrientDB is open-source with features of both document and graph database management systems, making it very fast. Written totally in Java (but with connections to other programming languages built in), it can run on any platform, and comes in a free community version and an enterprise edition. Its inclusion of a SQL layer makes it an easy transition for those whose only prior database experience is with relational databases. It also supports schema-less, schema-full, and schema-mixed modes.
Cloud Database Solutions
As implementation of cloud computing environments increases, it’s not surprising that cloud-based databases are becoming increasingly popular. Here are six examples:
This simple, yet flexible, non-relational database lets you locate database administration functionality to the cloud via web service requests. It creates and manages multiple, geographically distributed replicas for security and quick access, and automatically indexes and covers other admin functionality. SimpleDB is scalable and designed to work seamlessly with AWS services like S3 and EC2 (even RDS). This is still in beta and is mostly free for use (you only pay for resources you actually consume – more details on pricing are on the site).
This Amazon database product is well-suited to gaming, ad technology, mobile, and similar applications due to its guaranteed throughput and single-digit millisecond latency. Fast and fully-managed, it’s simple, cost-effective, and highly scalable (up or down). The tables do not have fixed schema, items can have multiple attributes, and secondary indices add query flexibility. SSD storage and three-way replication is provided, together with cryptographic security features. A free starter tier includes more than 40 million database operations per month.
Google offers a fully-managed, relational MySQL database via their cloud service. Redundancy and reliable storage are ensured with instances up to 16 GB RAM and 100 GB storage available. You can pay only for the time you actually access your data and Google manages backups for you.
Microsoft’s Azure cloud offering is a favorite of large enterprise operations, with 57% of the Fortune 500 already signed on. The goal is integrated development and operation tools and technologies in the cloud, using Microsoft-managed data centers worldwide. Development can be done via .NET, Java, PHP, Node.js, Python, or Ruby with Integrated Visual Studio tools. Billing is per-minute and a free trial if offered. BizSpark members are eligible for significant discounts.
Salesforce has been online for 15 years, developing a nice collection of different database-centered applications, and their move to a cloud platform offering is no surprise. The Salesforce1 platform currently runs more than 4 million applications, with subspecialty clouds for customer service, sales, marketing, and others. Anything built on Salesforce can be instantly made mobile-capable. A free trial is available.
ClearDB’s focus is on geo-distributed MySQL database configurations to ensure excellent performance and availability, not to mention survival of data access in case of network or disk failure. Using multi-master and multi-master with multi-replica MySQL configurations, ClearDB claims true 100% uptime, with a combination of 256 bit SSL encryption and client certificates directly in MySQL to secure connections. Shared services start from as little as $0.
Other Database Solutions
Using HTML5, web pages can be stored on a user’s browser, providing an option to cookies that is more secure and faster to access. Large amounts of data can be stored without affecting site performance — at least 5 MB — and the information is never sent to the server. This system is supported by IE 8+, Firefox, Opera, Chrome, and Safari. It requires browser support of two new objects – localStorage and sessionStorage.
Restoring a large MySQL database can be a problem if your server has a restriction on hard memory runtime limits. This tool lets you accomplish the restore into the existing or a new database by staggering the import of large dumps like those created by phpMyAdmin, even if your server is in safe mode. It accomplishes this by carrying out a small part of the dump and then restarting to complete the next part. The tool is free and has a good set of support/FAQs.
SQLBuddy is a web-based MySQL administration tool. It requires PHP 4.3+ and MySQL 4+, and works with Chrome, Firefox, Safari, Opera, and IE 7+. It’s extremely easy to use, with no setup needed (just unzip the files to your server). The current edition comes with instructions in 47 languages.
A tool for both MySQL and Microsoft SQL databases, HeidiSQL lets you browse and edit data and tables, views, procedures, triggers, and scheduled events. Data export and structure export are possible to a file, clipboard, or other servers. Batch optimization and repair of tables and other processes are possible. It is free and open-source, with connections to multiple servers in one window.
Sponsored by Dell, ToadWorld is a forum type of community with a collection of expert-written blogs and a variety of other information on existing database tools and resources, as well as emerging data technology. Training courses and videos are also offered, together with a wiki on IBM’s DB2, MySQL, No SQL and Cloud, Oracle, and SQL Server platforms. A good place to go for up-to-date information on the latest in database technology.
This fast and affordable database administration tool has an intuitive GUI for managing, designing, and manipulating data in MySQL, MariaDB, SQL Server, SQLite, Oracle, and PostgreSQL installations. Navicat uses a spreadsheet-type of data editor and a code completion feature to reduce the need to write complex SQL commands. Its SQL Beautifier mode formats queries appropriately for you. There is a 30-day trial for all versions with free email support, and the premium version can work for all six databases.