Database Buyers Guide

 By Kay Ewbank

Selecting a database package is possibly the most important IT decision you will make. Kay Ewbank checks out your options.

HardCopy Issue: 48 | Found In: Database | Published: 01/05/2010 | Last Revision: 07/07/2010

Databases are probably the most complex applications in use. They combine the means to store and retrieve data, to ask questions, to produce reports, and are designed to make their data available to a wide range of other business applications. Indeed you may well be buying a database because you are looking to develop an application around it, in which case your choice cannot be made in isolation. The database market is dominated by big names such as Microsoft SQL Server and Oracle, with MySQL, SQL Server Express and Oracle Express offering free or low cost alternatives. The big names are very aware of what the competition is doing, so a new feature introduced in one will probably appear in its rivals within a couple of versions. Earlier versions of database servers were very different from each other. IBM’s DB2, for example, started life on mainframes and didn’t really sparkle on smaller systems. Oracle was very fast but tough to use and extremely expensive. Microsoft SQL Server was by far the cheapest and easiest to use, but was a lot slower and early versions weren’t particularly reliable. Each of the products still suffers somewhat from these early reputations, even though none of the original criticisms hold true any longer. So given that the basics of performance, cost, scalability and reliability are similar across the products, we need to look at where their manufacturers have concentrated on particular uses if we are to differentiate their products.

Choosing a platform

If you’re running a version of Windows, this isn’t an issue. However, as part of the Windows family, Microsoft SQL Server is only available on 32 and 64 bit versions of Windows, so it won’t run on a Linux or UNIX box. By contrast, Oracle can run under Linux and UNIX as well as on 32 and 64 bit Windows systems.

Levels of expertise

If you have previous experience of any of the databases, that’s a strong reason for choosing the most recent version of the same product. It is possible to use your experience of Microsoft SQL Server to work out how to do similar things in Oracle, or vice versa, but learning a new database system is costly in terms of time and could leave you not using the product to the full. Although the latest versions of Oracle and SQL Server have automated performance tuning options that will take an SQL query and ensure it runs efficiently, it’s a lot better to make use of your own expertise to write queries that take account of any idiosyncrasies in the way the queries are run.

The extra costs

Another point to check is just what you get for your money. Oracle’s approach is that you pay for the central server, and then pay extra for options. Oracle Reports is a great enterprise reporting tool, for example, but doesn’t come as part of the database server. Instead it’s part of Oracle Fusion Middleware. By contrast, Microsoft SQL Server tends either to bundle its elements, or at least make them available for download at no further cost. Report Builder 2.0 for Microsoft SQL Server 2008, for example, can be downloaded to give you an Office-like report designer suitable for use by business users. It’s nowhere near as sophisticated as Oracle Reports, but you don’t have to buy it as a separate product. The built-in reporting option is Reporting Services, which can be used to create and manage both paper-based and Web reports.

Managing your database

Administering a database server with any level of proficiency is not easy, so the level of help you get from the database product matters. Some, such as SQL Server, have management interfaces that are designed to be comprehensible even if you only rarely carry out admin tasks. For the part time or non-specialist administrator, this is a great help. Other products such as Oracle do less handholding of the inexperienced, with fewer wizards and default settings. Move to alternatives such as MySQL and you are very much left on your own. There’s a configuration wizard to help you set up the server, then you are left with a command line interface. Microsoft’s management interfaces are very graphical with multiple pane windows where you can point and click, and wizards and guides to help you through many operations. By comparison, some operations in Oracle expect you to know what you’re doing and get on with it in a plain white screen with no easy start help at all. However, many aspects of Oracle’s management are strong. Its Enterprise Manager, for example, uses ‘management by exception’ which means the database server is expected to run unattended unless some problem is about to occur. In this situation the database administrator is alerted to the potential problem so it can be fixed before it has repercussions. If a database is running particularly slowly, for example, you’ll be told.

Oracle Database 11g

n the past, Oracle was the database expert’s database. If your company could afford it, and you were good enough at SQL, Oracle would give you the best performance. If you weren’t an expert, though, you’d be sitting staring at a blank screen wondering what the heck to do. Oracle is still extremely powerful and costly, but is a lot easier to use than it used to be. There are good management tools that have been improved from release 9i onwards, with Management Server’s Enterprise Manager giving you a graphical way to manage all aspects of your database via wizards and assistants if you need them. For example, Oracle’s Change Manager monitors changes you are making to a database schema and what effect those changes will have on your database, without altering the real production system. Performance Manager provides details of the way your CPUs, disks and queries are running, with options including ‘top sessions’ to show you which users and which jobs are taking all the resources. Oracle has done a lot of work on supporting grid computing to ensure good performance for business applications, and 11g continues on these lines. Oracle Real Application Clusters (RAC) are new to this release, and let you spread workloads across a set of consolidated servers. This obviously improves scalability and makes your database more reliable. This release also includes the ability to use lower cost storage partitions by compressing data onto them. The latest version, 11g R2, was released in Autumn 2009 and allows you to specify which server nodes are to be used for particular application workloads. For example, you could set up a ‘front office’ resource pool that is used to power your Web sites and CRM (customer relationship management). If a resource pool needs more resources then it can automatically take the power from unassigned server nodes, or from pools that are currently underused.

Oracle Real Application Clusters shows you what is happening under the hood.

To spot problems, managers can define both email and pager alerts for common database events or threshold conditions, such as running low on table space. You can also turn off problem alerts for defined blocks of time, avoiding needless alerts during scheduled downtime. Perhaps the most appealing new feature in 11g is Real Application Testing. The problem with developing databases containing a lot of SQL code for stored procedures and business logic is that its very difficult to test whether your changes work; and if they do work, whether they make the performance better or worse. Real Application Testing lets you take a sample database workload from the database as it is being used for real. This workload is captured along with its performance statistics, and you can then replay the workload on the same server or on a development server, and see how the performance statistics change. Another improvement in 11g is Active Data Guard, which lets you make use of standby servers. It allows you to move activities such as queries, reporting and Web access to a read-only copy of the database on a physical standby machine. Changes from the production database are then applied as they occur. Another way to get faster answers for users is provided by Result Cache. This lets you take a query result and save it in a memory buffer so that when the same query is re-used, there’s no need to redo a search on disk. You can cache queries, subqueries and PL/SQL functions. Used carefully, this is a feature that can be fantastic: used badly, you can end up caching lots of queries that don’t ever get run again – or rather, that get run but with slightly different parameters, so the results are of no use. Oracle’s automatic health monitoring and data corruption checks have also been improved in this release, providing excellent early warning alerts before problems get out of hand. One point to note is that, as always with Oracle, you pay for specific features. If you buy Oracle 11g Enterprise Edition then you’ll still be expected to pay significant additional charges for features such as Real Application Clusters and Active Data Guard.

SQL Server 2008 R2

Early versions of Microsoft SQL Server were not the greatest databases, and manufacturers of rival database servers were able to dismiss it out of hand as slow, insecure and not scalable. These criticisms were valid at the time, but have not held for several releases now. SQL Server 2008 is fast, secure and scales well - so long as you’re working in Windows environments. It is also the easiest database server to manage and has great Business Intelligence facilities. SQL Server does everything it can to make it easy to manage databases, with a graphical interface and wizards and guides to help you through tasks such as creating databases, managing users and carrying out administrative actions such as setting and managing backup schedules. The 2008 release added policy-based management so that administrators can create a policy on what can be done to a database, then apply that policy to multiple SQL Server instances.

SQL Server Management Studio gives you a visual display of database usage.

Setting up an analysis cube in SQL Server Analysis Services.

Features such as the Resource Governor let you allocate server memory or processor time to particular databases. There’s a query analyser that helps you tune SQL queries so they run optimally: you can view the results as a grid or text, and you’re shown execution plans for SQL queries. There’s also an index-tuning wizard that will suggest how to make the query run better.

For many customers, having the reporting and analytics options built in to the standard product are the best thing about SQL Server. There’s a choice of tools for creating reports including a built in report builder for IT users, and Report Designer which is essentially a customised version of Visual Studio for use by more serious database developers. 2008 also includes the downloadable 2008 Report Builder which is a standalone product with sophisticated design options and an Office 12 ribbon interface. Reports can be created using sophisticated controls such as gauges and sliders, and you can make use of charts including polar, shape and range. SQL Server also has strong links to Microsoft PowerPivot, a data analysis solution that uses Microsoft SharePoint 2010 and Excel 2010. PowerPivot for Excel combines an Excel add-in that displays data, and the VertiPaq data engine that compresses and manages millions of rows of data so it can be analysed in memory. PowerPivot for SharePoint can then be used to share the data analyses among colleagues. SQL Server 2008 R2 adds the means to manage data from multiple sources, and the ability to work with real-time data. Master Data Services are new in SQL Server 2008 R2, and provide a way to consolidate data from multiple sources so as to ensure anomalies between different data sets are avoided. Data is managed by the database users from a portal, with integrity rules ensuring data quality. If someone changes data, the people who own the data are informed so they can approve the change. An audit trail showing who made the change and when it was stored, along with the type of change and the previous data value. SQL Server 2008 R2 has also added a feature for managing continuously streaming data, such as financial feeds. In earlier versions such information involved custom pre-processing techniques, but you can now use Microsoft’s StreamInsight data engine to do the pre-processing so that SQL Server is passed the aggregate or average values. Overall, SQL Server is a great choice if you know you are going to stay in the Windows environment.