Choosing a DBMS

I thought I’d post some information about database management systems for those who are a little less familiar with them than your typical DBA or developer. Maybe you’re a new IT professional, maybe you’ve been tasked with making a decision in your company on which DBMS to use for a new project, or maybe you’re just trying to see what’s out there in the DBMS world.

I want to focus on some of the relevant enterprise DBMS….Oracle, IBM DB2, and Microsoft SQL Server. We’ll leave MySQL and some of the others for future discussions. I work with these three systems on a regular basis, and to no surprise…they all have their ups and downs. I’ll share some pros/cons on each one.  There are lots and lots of lower level features and topics we could discuss about each DBMS, but this is just meant to be a quick overview from the average users perspective.

All in all…Oracle is great for the very advanced user/DBA, but very friendly for those not very familiar with database management systems.

Oracle Pros:

• Some great bundled tools for quick deployment
• Very fast/reliable/stable
• Lots of flexibility for the advanced user (troubleshooting/tracing/configuration)

Oracle Cons:

• Extremely resource intensive.  An Oracle DB will consume most of a systems resources if not deployed on a real server.
• Difficult to troubleshoot
• No more thick client for Enterprise Manager after the 9i release.  DBA has to rely on the web version of EM for performing maintenance.  (The real Oracle DBA will seldom/never use EM though, however it is good to have for the Oracle newbie)
• Backup/restore is not simplified
• Requires a client install in order for a workstation to be able to connect to a database
• Documentation is not organized very well, difficult to find relevant information when having problems

SQL Server is great for those that love Microsoft products (which a lot of people do).  For those that do not like relying on Microsoft updates, and regular GUI changes, SQL Server probably is not for you.  This is probably the quickest DBMS to get up and running, but seems to have problems with SPID (system process identifier) blocking issues which cause the database to lock up until locks are manually released.

SQL Server Pros:

• Very simple backup/restore
• Configuration is simple
• No client required.  Connectivity is built in to the OS
• Ability to authenticate using Windows credentials, or pre-defined SQL Server credentials
• Easy to troubleshoot basic issues (SQL Profiler)
• Great for .NET developers sandbox environment

SQL Server Cons:

• No *NIX support (Windows only)
• Not as customizable as Oracle/DB2.  Some would argue that SQL Server is barely an enterprise DBMS
• New versions (2005, 2008) are like changing from Office 2003 to Office 2007.
• SPID Blocking
• Not a very knowledgeable user community
• Microsoft Support…say no more

I’ve saved the best for last.  I recognize benefits in all of the DBMS listed on this page, but DB2 is simply my favorite.

DB2 Pros:

• Quick deployment
• Not terribly resource intensive
• Easy command line interface for quick maintenance
• Native client app (Control Center (db2cc)) provides a simple front end for maintenance
• Some great tools for configuration
• Supports a wide range of OS’s (Windows, Linux, UNIX, AIX, and more).
• Improved backup and restore capabilities in 9.5+ releases
• Very easy to troubleshoot
• Documentation is very simple to locate when having problems that require research
• World class support

DB2 Cons:

• It doesn’t come with any games….(I had to put something here).

Hopefully this was helpful to some of you who are doing some initial research on these systems.  If you have any problems and you end up here…feel free to reach out to me.  I’ve seen most problems with all of these systems somewhere along the way, and usually have a pretty good memory when it comes to these things.

Here are some links to get you started with the Express (free) versions offered by each vendor:

IBM DB2

Oracle

SQL Server