Choosing a DBMS

One of the questions I get on a regular basis is some variation of “how can I choose what DBMS is the best”… so I’ll try to offer some high-level guidance here.

When mainframes dominated enterprise computing, the DBMS architecture was a simple concern. Today, the IT infrastructure is distributed and heterogeneous. Even for a modern mainframe DBMS implementation, the overall architecture will likely consist of multiple platforms and inter-operating pieces of system software. The final architecture must be based on the business needs of the organization. It should be chosen by a team consisting of business experts and IT experts. Business experts should include representatives from various lines of business as well as accounting and legal for software contract issues. IT experts should include database resource management representatives including data administrators, database administrators, and system administrators. But the selection process should also include other IT professionals, such as members of the networking group, operating system experts, operations control personnel, programming experts, and any other interested parties.

But let’s consider the nuances of the question. The problem — and the answers to it — take on many different forms. For example, should I answer it based on you having no existing DBMS and you’re just looking to buy one for the first time? That is a valid, though not too common, situation. On the other hand, you are more likely asking something like “Which of the several different DBMS platforms that we already have should we use for Project X?” This is also a valid question. So, let me try to answer both — at least in some fashion.

First of all, if you are brand new to DBMS it would be a very wise course of action to hire a database consultant (or two) to help you with your selection process. There are several very good choices out there. My preference is that new users should generally choose from the market leaders, and that means one of the big three: IBM DB2, Oracle, or Microsoft SQL Server. Of course, you also have other options such as an open source DBMS like MySQL or PostgreSQL. These can be used for some types of production work (mostly lower-end or perhaps web-based development projects). For high-end mission-critical applications, stick with the Big Three.

Another recent phenomenon is Big Data and NoSQL. There are a variety of options to choose from in this camp, with most of the leading candidates based on some for of Hadoop. For projects requiring large amounts of data and high availability “big data” solutions such as the CouchDB and Hbase NoSQL DBMS offerings may be ideal. For the most part, the NoSQL DBMS offerings are not for supporting typical, enterprise applications. Again, I would direct you to one of the Big Three for most typical enterprise usages.

OK, which of the big three? Well, if you are a large organization with a mainframe and want to run your DBMS on that mainframe, you really should go with IBM DB2. Although Oracle has a mainframe presence, IBM is far and away the market leader here. For Unix and Linux installations, your choices are Oracle and DB2. Oracle is the market leader on those platforms, though IBM has a nice presence there, too. For Windows development, all three are viable options, but Microsoft is the leader on Microsoft’s OS.

What about other options? Well, Sybase, Informix, and Teradata are the next biggest players in the market. Sybase has lost ground in the market, but their DBMS is still solid and they are firmly entrenched in the financial market. Informix, now owned by IBM is still a top notch DBMS with great performance and is still being marketed and improved by IBM. Teradata is a high-speed DBMS that is geared for data warehousing and OLAP work and you might want to choose it for those types of projects.

And what if you have several DBMSs installed and need to choose one for a new project? In that case, it is best to base your decision on internal company issues. Consider the existing support and expertise that you have in-house for each DBMS. If the project is highly visible it makes sense to go with the DBMS that is best supported by your in-house experts because they can give it the care and feeding it needs to perform optimally. Also, think about the hardware platform. For your very high availability needs go with the mainframe if you have one. After that, it is Linux and Unix… then Windows.

I haven’t talked about cost yet, but it would be an incomplete answer without at least touching on that subject. When you are examining the cost of the DBMS software do not limit the analysis to just the initial cost and on-going maintenance cost that must be paid to the DBMS vendor. Look at the total cost of ownership of the DBMS. TCO should be calculated as a combination of the license cost of the DBMS, the license cost of any required supporting software, the cost of database professionals to program, support and administer the DBMS, and the cost of the computing resources required to operate the DBMS. Also, try to factor in the reliability of the total package in terms of downtime – and factor in expected losses due to downtime if at all possible. Determining the TCO for a DBMS can be a difficult and time-consuming task without the assistance of a consultant well-versed in server and DBMS performance and operations.

You will also need to pay some attention to the various “flavors” of DBMS offerings “out there” from all of the major vendors. The DBMS you select must be appropriate for the nature and type of processing you plan to implement. Four levels of DBMS architecture can be selected: enterprise, departmental, personal, and mobile.

An enterprise DBMS is designed for scalability and high performance. It must be capable of supporting very large databases, a large number of concurrent users, and multiple types of applications. The enterprise DBMS will run on a large-scale machine, typically a mainframe or a high-end Unix, Linux, or Windows machine. Furthermore, an enterprise DBMS offers all of the “bells and whistles” available from the DBMS vendor.

A departmental DBMS, or workgroup DBMS, supports small to medium sized workgroups within an organization, and typically runs on a Unix, Linux, or Windows server. Hardware and software upgrades often can allow a departmental DBMS to tackle tasks that previously could only be performed by an enterprise DBMS. The steadily falling cost of departmental hardware and software components further contributes to lowering TCO and helping to enable a workgroup environment to scale up to serve the enterprise.

A personal DBMS is designed to be used by a single user, typically on a low- to medium-powered PC platform. Microsoft Access and Filemaker are examples of personal database software. Sometimes the low cost of a personal DBMS causes misguided attempts to choose a personal DBMS for a departmental or enterprise solution. A personal DBMS product is suited only for small scale projects and should not be used to deploy multi-user applications.

Of course, the major DBMS vendors also market free versions of their more high-powered solutions, such as DB2 Express-C and Oracle Database Express Edition. These offerings are targeted at building support for the DBMS among developers and against open source offerings. And they can be a practical alternative to a personal DBMS in that they can be deployed individually and then expand to a multi-user license as usage demands.

And the mobile DBMS is a specialized version of a departmental or enterprise DBMS. It is designed to be used by remote users who are not usually connected to the network. The mobile DBMS enables local database access and modification on a laptop or handheld device, such as a smartphone. Furthermore, the mobile DBMS provides a mechanism for synchronizing remote database changes to a centralized, enterprise or departmental database server.

A DBMS designed for one type of processing will likely be ill-suited for other uses. If your organization requires DBMS solutions at different levels, whenever possible favor the selection of a group of DBMS solutions from the same vendor. Doing so will minimize differences in access, development, and administration.