What are database management tools and what features are must-have?

Data is the raw material for the information age, and database management tools are the hammer, furnace, anvil, saw, lathe and paint brush you use to make data useful.

The simplest functions of database management tools let you view the contents of your database. The most advanced functions enable your entry-level users to summarize actionable insights for the C-suite. And all the functions in between.

This post will describe the Quest® approach to database management tools, refined over decades of keeping pace with the evolution of databases and the ever-changing needs of businesses large and small.

Who uses database management tools?

Database management tools incorporate functions that meet the needs of three different types of database professionals.

• Database developerswant tools that help them write high-quality code the first time and maintain it easily. They value automation and collaboration tools for programming so they can shorten development cycles without increasing risk.
• Database administratorsuse tools designed to monitor database health and performance. They handle tasks from diagnosing and resolving performance bottlenecks to implementing changes in database schema.

Database analystsfield data from multiple sources, then they integrate, clean and prepare the data for analysis. It is important to them to collaborate on queries and datasets without having to rely on IT for access.

What should I look for in database management tools?

Development

It starts with simple development tasks: adding and editing SQL, schemas and scripts. It’s the basic work of writing SQL from scratch and editing existing SQL, integrated with source control so that database developers can collaborate oncode review. Developers also use SQL to create unit tests they then run on the code they’ve written; in test-driven development, they can create the tests before writing the code.

SQL is rarely perfect the first time, so tuning (optimization) is the process of improving SQL to accelerate queries and code in the database. Developers rely on tools to profile their code and reveal bottlenecks, then to generate, analyze and compare alternatives by shouldering the burden of repetitive trial and error.

By automating tasks to the greatest extent possible, smart developers conserve their effort for improving code. When they combine automation and collaboration, development teams can use tools to enforce standards while they write code. The resulting boost in quality reduces the likelihood of defects and shortens the development cycle.

Administration

Whether an organization’s multi-tenant databases are on premises or in the cloud, database management tools have to accommodate them, moving schemas and data from local servers to cloud services. That includes controlling access to protect them from attack, executing scripts against them and managing database objects like tablespaces, rollback segments and roles.

As data privacy becomes a priority for everyone along the development chain, database management tools have a role to play in helping administrators discover sensitive data. They facilitate compliance with regulations like GDPR by automatically flagging sensitive data use in object and code editors. Admins can then use tools to apply native database features like redaction, encryption and auditing to protect personally identifiable information (PII).

The tools assess database health quickly by validating performance, configuration, and security vulnerabilities through health check reporting.

DevOps

In most organizations, application development enjoys a kind of agility that database development envies, mostly because it’s harder to undo application changes than database changes. Tools that expose their functions as programmable objects can bring the two efforts closer together by enabling execution of database development functions within a DevOps workflow. Build automation tools like Jenkins, Bamboo and Team Foundation Server call those objects, ensuring quality and maintainability through code reviews and unit tests.

Tools that bring database development, testing and deployment tasks into the continuous integration and continuous delivery (CI/CD) process make it easier to keep up with application development.

Analysis

Data resides in a growing number and range of sources: SQL-based and NoSQL databases, ODBC, business intelligence, and desktop databases and spreadsheets. Database management tools that connect to multiple sources through a single interface for basic querying and reporting empower analysts without forcing them to become database administrators. Then, integrating data across a range of sources and building queries from them makes analysis possible without the need to understand SQL programming.

Many analysts complain that they spend more of their time cleaning up data than turning it into useful insight. Tools for data transformation and cleansing help them find and eliminate duplicates, group and sort data, search and replace, and derive new columns from operations on existing ones. Finally, database management tools allow data visualization from tabular results into charts, graphs and histograms, with easy distribution to decision makers.

Data modeling

How do you accurately capture the data needs of business users and design a database that will meet those needs well into the future? Tools that provide data modeling help in defining efficient data structures before schemas are built. They produce documentation and entity relationship (ER) diagrams that developers use to understand data structures and implement them with DBAs.

Data modeling across database platforms—Oracle, SAP, MySQL, SQL Server, PostgreSQL, DB2, Microsoft Access, etc. —is common as data sources proliferate. Designers use database management tools to construct logical and physical data models and create databases and warehouses. They generate data definition language (DDL) scripts that reflect the data model and update data structures, then use those scripts to move data across platforms.

Performance Testing

Once you’ve made changes to your database or moved it to the cloud, how do you know something else won’t break under a heavy load?

Industries as diverse as ecommerce, financial trading and manufacturing have standardized benchmarks for ensuring that databases perform adequately. Near the end of the maintenance cycle, database management tools include functions for capturing and replaying transaction workloads against those benchmarks.

The tools help admins spot problems before they occur in production by finding applications and SQL statements that don’t scale well as transaction volume increases. Using what-if scenarios, they reveal potential bottlenecks and resource shortages that keep databases from performing reliably through platform migrations and application changes. Running benchmark tests against scripts, code and stored procedures reduces the risk of making changes such as patches, upgrades, operating system migrations and adjustments to virtual machines.

What is the future of database management tools?

As in most things IT, cloud performance is the dominant theme when looking to the future. The tool vendors know that and roll out products that work on premises, in the cloud and in hybrid environments. The main features and functions in database management tools apply in all environments, just as the main business drivers apply to database developers, administrators and analysts.

Share on social media

If you think your colleagues would benefit from this article, share it now on social media with the buttons located at the top of this blog post. Thanks!