What to Consider When Choosing A Database Solution

What to Consider When Choosing A Database Solution – When is “free” the right price?

Manufacturing Execution Systems (MES) and control systems engineers sometimes find free express editions of popular database engines installed as the data backbone of an existing system. Other times they’ll be asked if an express edition can be used for a new or replacement installation, mainly because the software is free to use. While it is certainly possible to use an express edition for production purposes, it isn’t always a good idea. To determine if the free version is appropriate for the job, it is important to analyze each option for its potential limitations around key factors such as data management, advanced analytics, and availability and encryption.

What Options Are Available?

Matrix Technologies primarily uses Microsoft Windows installations that include integration with pre-packaged software, including database engines offered by Microsoft, Oracle, or both. Built-in connectivity with popular open-source database solutions, such as PostgreSQL and MySQL, are not yet prevalent in the industrial space even though these database products provide many similar capabilities for free. A system might appear to be rather expensive when choices are limited to purchased Microsoft or Oracle products, but other options exist.

Microsoft and Oracle each offer express editions of their database engines free of charge, an option that is quite attractive to budget-conscious managers as the licensing fees for the “full blown” versions can reach several thousand dollars. The zero-dollar price tag, however, does come with limitations.

Limitations and Considerations

Microsoft distributes several versions of their SQL Server database engine, the most limited of which is “SQL Server Express.” Oracle Corporation also offers a limited version of their popular database engine called “Express Edition,” or “XE.” This software is free to download, install, and use, but the trade-off is restrictions on the application’s capabilities. Some of the key factors for a data backbone solution that Matrix Technologies reviews are:

Processing throughput, memory, and storage needs

How much data is going in and out of the database, and how quickly is it moving? How complex are the rules for storing and retrieving the information?

SQL Server Express supports up to four CPU cores, a maximum of 1.4 gigabytes of memory, and a maximum database size of ten gigabytes. In addition, the Reporting Services component is limited to four gigabytes of RAM. Oracle XE imposes similar limits: processing resources equivalent to one CPU, one gigabyte of RAM, and four gigabytes of storage.

On top of the physical constraints, the user-friendly software tools for managing database performance are not available for the lightweight installations. Microsoft’s Tuning Advisor and Oracle’s Tuning Pack are excluded from their respective express editions. Microsoft also limits the ability to easily schedule database jobs by excluding Agent, so automating backups and other data clean-up tasks is a bit more challenging.

The free versions still offer a lot of “horsepower” and functionality, but the resulting system offers nothing near server-class performance. Often, these restrictions alone don’t offer enough to handle a production workload. A system moving and manipulating large amounts of data, especially very quickly and with many inserts, deletes, and updates, would quickly outgrow the restraints of the free database distributions.

Availability and Encryption

Does the database need to serve up accurate information at any time, even if there are network or hardware problems? Is the data highly sensitive? Will information be exposed outside of the corporate firewall?

If so, express editions are not appropriate for the job. Neither Microsoft nor Oracle express editions support “high availability” features that hide the effects of hardware or software failures to avoid downtime. Likewise, advanced security options such as file encryption are not part of the free offering. Websites, even those available only to a small number of people, should never rely on the security provided by express edition databases because the necessary protection is not built in.

Advanced Analytics

What types of reports are needed? What kind of information are you trying to extract from the data?

If the answer is straight-forward data relationships and simple reports, then the express editions are still an option. If a system needs online analytical processing (OLAP), data cubes, full-text searching, or data mining, only full editions should be considered since they offer this level of service. In general, advanced features are not included in free versions of SQL Server or Oracle.

Remember: the express editions that Microsoft and Oracle offer are meant for situations that call for limited processing power, memory, storage space, and functionality. This means basic input and output operations without the “bells and whistles” that make the additional features worth paying for.

When Can I Expect to Use Express Editions?

With all these limitations and more, are the free database engines offered by Microsoft and Oracle even worth considering at all?

The answer is yes. There are many practical uses for these products that are suitable for production settings, and Matrix has often been able to leverage the advantages offered by the no-cost express editions.

For example, Matrix has successfully demonstrated the use of express edition databases in these situations:

• As a reliable data store to pass data between disparate servers;
• To save and forward data from an independent device that’s not always connected to a network;
• To reliably perform steady data I/O on a “mini” system;
• For testing database functionality in a closed environment;
• To maintain configuration and processing information for multiple systems.

In other cases, Matrix has decided to use the paid licenses for additional functionality:

• When providing data storage and processing for factory control systems that operate continuously;
• To store a large volume of device readings over a long period of time;
• For generating complex reports over a broad range of information;
• To distribute large amounts of data reliably and efficiently across different systems;
• To protect against data loss in the event of an unexpected outage;
• For automatically purging older records and distributing reports.

Plant personnel often want to save money by using a free distribution of a popular database engine. It is no secret that the basic functionality and dependability expected of a Microsoft or Oracle database system is provided in the express versions these companies offer. Sometimes this functionality is adequate; other times it is not. The key to selecting the database suitable for a factory-based application is to ask the right questions about the system that the database will support. Only then is it possible to determine if the limitations of a no-cost database can provide appropriate long-term support for the installation’s information backbone.

Matrix Technologies is one of the largest independent process design, industrial automation engineering, and manufacturing operations management companies in North America. To learn more about our manufacturing operations management capabilities and manufacturing process control solutions, contact David Copi, Senior Consultant, Manufacturing Systems and Solutions.

© Matrix Technologies, Inc.