A database management system, also called DBMS, is a program that allows admins to access, alter, and analyze a database. In this article, I’ll describe the functions of a database management system and review different types of databases. While this isn’t a comprehensive list of all DBMS types, it’s a start if you’re looking for a quick guide to common database management solutions.
What’s the Purpose of a Database Management System?
There are several administrative tasks needed within the database, like tracking changes, or backing up and recovering data, and the right software allows you to make system-level changes more easily, while pinpointing any issues. Through the DBMS, admins can manage data, the database engine, and the database schema setting the logical structure.
However, a critical piece to making sure you’re getting the most out of whatever DBMS you choose is your ability to monitor databases and tune database performance to ensure it stays efficient.
SolarWinds Database Performance Analyzer (DPA)
I recommend using a tool like SolarWinds® Database Performance Analyzer (DPA) to monitor and optimize many types of DBMS. I find this is the most robust of these relational DBMS monitoring tools, offering comprehensive yet easy-to-use functionality. Monitor databases like MySQL, Oracle, SAP ASE, DB2, and more from a single installation. Get the tools you need to optimize performance based on clear diagnostics. If you need a straightforward, streamlined way to monitor your databases and tune performance all from a well-designed interface, start with DPA.
When it comes to monitoring databases that don’t follow the relational model, a viable option is to use Database Performance Monitor (DPM), another offering by SolarWinds.
DPM is a SaaS-based monitoring solution focusing on open-source and NoSQL databases. It can monitor databases locally, in the cloud, and hybrid with 24/7 real-time monitoring. The tool can monitor a wide array of metrics and display them with its user-friendly dashboard. It allows you to zoom-in and drill-down specific points, so you can diagnose problems earlier. A list of metrics DPM can track includes:
- Deploy frequency
- Reduced failed deploys
DPM also has important security features, coming with GDPR and SOC2 compliance out of the box. The tool can also help you eliminate over-provisioning when it comes to the capabilities of your database.
It allows you to easily detect when an excess of resources is being consumed by a portion of your database’s workloads. Equipped with this information, you can improve the performance of your servers and reduce your data center footprint.
SolarWinds® SQL Sentry
SQL Sentry is designed to give DBAs wider visibility across physical, virtual, and cloud SQL Server environments. SQL Sentry includes execution plan diagrams, query history, and more, so you can more easily fix problems with and optimize the performance of database management systems.
SQL Sentry is built to provide the performance metrics you need to identify and resolve SQL Server issues faster by supporting your ability to:
- Find and fix high-impact SQL queries – Identify suspect statements running thousands of times in a short period and are subjecting your server to “death by a thousand cuts” and tune them within the same interface
- Performance baselining – Set individual baselines to compare what’s happening against expected behavior
- Historical analysis of performance counters – Go back in time or over a range to see changes in the SQL Server
You can download a free 14-day trial of SQL Sentry.
Types of DBMS Software
There are several different database schemas out there, each with their own advantages and disadvantages. Choosing a database type can be a matter of balancing factors like organization, security, storage space, and efficiency. To start, your choice of DBMS will depend on which of the major database types you use.
Relational Database Management System
This is a kind of DBMS based on the relational model of data. Relational databases are sometimes referred to as SQL databases, after the coding language (Structured Query Language) used to program most relational database management software.
A relational database essentially draws connections between tables or spreadsheets, within a database. As data is added to a database, simply adding columns to the same table would create a static and inflexible data set; over time, your database would become confusing and unhelpful. But in a relational database, tables must have a primary key column which uniquely identifies all data in the row. Tables can then use a foreign key to link to the primary key of another table.
One of the big advantages of relational databases is Atomicity, Consistency, Isolation, and Durability (ACID) compliance. The term ACID was coined by computer scientists in the 80s to describe how database transactions must proceed to preserve data integrity. ACID compliance is still considered the standard for ensuring databases are valid even when disruptions like errors and power failures happen, and is a must for databases containing sensitive or regulated information.
Popular for decades, relational databases are particularly good for major organizations that need ACID compliance and have consistently structured data. This type of database is so widespread and versatile, it’s hard to say what specific kind of task or organization it’s best for, but relational database management systems are common options for financial systems, personnel data, and logistical information. Only more recently have NoSQL databases become a feasible and even common alternative.
NoSQL Database Management System
Non-relational databases are called NoSQL. They’re much less structured than relatable databases, and are document-centered, rather than table-centered. Data in NoSQL databases doesn’t have to fit into well-defined rows and columns. Many NoSQL databases are similar to file folders. This makes it much better for large and irregular collections of data, but also demands much more time and processing power.
NoSQL databases management systems are generally classified as one of four types:
- Key-Value: As the name suggests, a key-value database stores combinations of keys and values. It might look like a spreadsheet with only two columns. Riak and Redis are two common examples.
- Wide-Column Stores: Wide-column store databases are column-oriented, as opposed to most databases (like relational databases) which are row-oriented. This has meaningful implications for the organization of data. While wide-column stores might organize data by a key like other databases, the information attached to this key doesn’t have to be as structured or regular. Examples include Cassandra and HBase.
- Graph: A graph database treats data as “nodes” and connections as “edges” between those nodes. These databases are (or can be) represented visually as graphs. Graph databases are used by most social networks and almost any website with “recommendations” based on your behavior. For example, Netflix’s recommendations come from a graph database. HyperGraphDB is one example of this type.
- Document Store: Document stores are similar to key-value because they consist of keys with each corresponding to a document. However, those documents can contain immense complexity and don’t have to correspond with the structure of data contained within any of the other documents. MongoDB is an example of this.
A type of increasingly popular database over the last decade is in-memory, abbreviated as IMDB or MMDB (for Main Memory Database System). These databases store data on RAM, providing extreme speed for compressed, unstructured data, but also somewhat less security during disruptions (although advances have addressed this). Due to their speed, MMDBs are popular for telecommunications and mobile advertising.
Other DBMS Types
Besides these systems, options like columnar or cloud-based DBMSs could be relevant for your organization. Columnar is designed to be fast, with high levels of compression, so this type of DBMS is well-suited for data storage situations where there are a large number of similar data items. But in-memory analytics have made this factor less crucial.
Meanwhile, for a cloud-based DBMS, a cloud service provider takes over the task of providing data storage and maintaining data. Incorporating some amount of cloud storage is obviously becoming an increasingly popular option. But keeping the management system in the cloud is typically relevant for companies with managed service providers.
Best Database Management Systems
This is just a short list of some popular DBMS types:
- Oracle 12c: One of the biggest and oldest names in the game, Oracle is a popular DBMS. Their latest generation is optimized for cloud computing to help clients managing many databases simultaneously. As a relational database, it has robust functionality and top-flight security (including ACID compliance). On the downside, Oracle is hugely expensive, especially at the enterprise level. They do have a tier for smaller businesses, but it may not be enough. Oracle is a good choice for huge operations with sensitive, clearly structured data.
- Microsoft SQL Server: Another famous name that offers quality, if potentially expensive, service. However, Microsoft isn’t just a name here, it’s an ecosystem. So, if your company runs on Microsoft already, SQL Server will integrate well with your relational database system and vice versa. Other standout features include dynamic data masking for extra security, temporal data support, support for Linux, and visualizations for mobile. Disadvantages include the aforementioned cost.
- MongoDB: The most recognizable name in NoSQL DBMS, MongoDB is open-source, but the commercial version has better functionality. The commercial edition includes advanced security (which is especially important in non-relational databases that are inherently less secure), administration, support, and training. These are features you might not miss until you need them when it could be critical. As mentioned previously about document-based databases, MongoDB is well-suited for complex but not standardized data.
- Neo4j: Neo4j is a graph database tool and a relatively rare NoSQL platform that supports ACID compliance. As mentioned, graph databases are perfect for connections and visual representations, making them the preferred database for recommendation engines. I would recommend getting the Enterprise Edition, which is best for management and scalability.
How to Choose a DBMS
In summary, your choice of DBMS partially depends on the type of database you need, which depends largely on what kind of data you have and how you want to use it. A good DBMS is useful because it provides a centralized, unified view of data, giving you easy insight into where data is stored, and its current status. It also allows the use of data across applications without having to create multiple versions of the data.
It’s true a DBMS will use more memory and CPU power than file storage alone. However, most businesses find a DBMS is worth it, especially for large or sensitive data collections. When creating a data management software list of potential solutions for your organization, you’ll want to consider how you’ll be organizing your database infrastructure and how databases will be used and compare this with each type of database management system software to find the best fit.