Today it is generally accepted that database administration is required to ensure the efficient and proper design and care of the data in production databases. But today’s DBMS does not deliver 100% of the functionality needed to support large-scale database development. Fortunately, many data management tools are available that enhance the functionality of the DBMS.

Production DBA Tools

When problems occur, the database is frequently the first thing blamed. The database is "guilty until proven innocent" and the DBA is on the hot seat to fix the problem quickly. As such, the DBA must have strong skills and even stronger tools at his disposal. Tools to manage the production environment are the first types of tool required because production systems run your business – and when they are down so is your business.

Performance tools help the DBA to gauge the responsiveness and efficiency of SQL queries, database structures, and system parameters. Such tools run in the background and capture database performance statistics and alert the DBA when problems occur. Advanced performance tools can take proactive measures to correct problems as they happen.

Your performance management tools should examine and improve each of the three components of a database application: the system, the database structures, and the application. The system consists of the system software and hardware required for the application to provide service. This includes the computer itself, its disk subsystems, network connections and all peripherals. From a software perspective, the system includes the operating system, the file system, the DBMS itself, networking protocols and any related middleware such as transaction processors or message queues. Some of the tasks required for system tuning include the proper allocation and management of memory structures, storage management, integration of the DBMS with other system software, proper usage of transaction logs, and coordination of the operating system resources used by the DBMS.

The second component of database performance tuning is making sure the database objects are optimally created and maintained. The database stores the data that is used by the application. When the application needs to access data, it does so through the DBMS to the specific underlying tables of choice. If the database is not optimally organized or stored, the data it contains will be difficult or slow to access. Key aspects of database performance include organization statistics collection, and database reorganization. Modern reorganization tools enable database structures to be reorganized while the data is up and available.

The third component of database performance is the application itself. Indeed, as much as 80 percent of all database performance problems are caused by inefficient application code. The application code consists of two parts: the SQL code and the host language code in which the SQL is embedded. SQL tuning and optimization is an art that can takes year to master. A good SQL performance tool will capture the SQL as it runs, sort statements in order of resource consumption, create a SQL history database, and offer guidance on how to re-write SQL to be more efficient.

Administration tools help the DBA to perform the day-to-day functionality of his job. This category of tool simplifies tasks such as creating database objects, examining existing structures, loading and unloading data, and making changes to databases. Without an administration tool these tasks require intricate, complex scripts to be developed and run. One of the most important administration tools is the database change manager. Without a robust, time-tested product that is designed to effect database changes, database changes can be quite time-consuming and error prone. A database change manager automates the creation and execution of scripts designed to implement required changes – and will ensure that data integrity is not lost.

Recovery tools simplify the process of creating backups and recovering from those backup copies. Most DBMS products provide basic backup and recovery utilities, but recovery tools help by automating complex processes, simulating recovery, and implementing disaster recovery procedures. Additionally, some recovery tools can examine database logs to perform online SQL-based recoveries.

Database Security and Integrity

The security and integrity of data in production databases is also a prime concern that can be addressed with data management tools. Knowing what, exactly, is being done to data and who is doing it is next to impossible without proper tools. Implementing auditing and database usage reporting software can help to track down changes made to database privileges, changes to database structures, changes to data, and access to data.

Another consideration for database security is the ability to monitor and report on user behavior. Studies show that internal users are a bigger threat to security than external hackers. Database security tools that can maintain a baseline of standard user behavior and report on anomalies are quite useful for exposing potential internal threats. Of course, database security is a much deeper topic and tools can help to thwart SQL injection attacks and expose and patch potential DBMS vulnerabilities.

Another useful category of data management tool provides data profiling capabilties. Profiling your data is a methodology for gaining insight into your business data and refining your processes for improving data quality. A data profiling tool is used to discover structure, data, and relationship anomalies in your data. Profiling techniques help you to uncover instances of improper data where the data does not match the metadata definition, patterns do not match, values are inaccurate, and occurences of redundant data.

Development DBA Tools

Development DBAs support the application development lifecycle. Although there is no immediate impact to business (because the application/database is not yet operational), the development DBA focuses on building an effective, usable database environment to support business applications. As programs and systems are built, the development DBA lends assistance and support – which includes building and maintaining the proper database structures required by applications.

A development DBA requires skill in data modeling and normalization to ensure that databases are designed to promote data integrity. A database design tool that supports E/R diagramming and the translation of a logical model into a physical implementation should be the first tool in the development DBA’s arsenal.

Once the test database is created, the development DBA must assist developers in providing and editing test data. Database application development tools to generation test data, edit the data, and move the data around are needed to create and maintain test beds for development.

Summary

As data management tasks get more complex and DBAs become harder to find and retain, more and more DBA duties should be automated using intelligent DBA tools. Using such and approach can help to reduce the amount of time, effort, and human error associated with implementing and managing efficient database applications.

Furthermore keep in mind that this column has just skimmed the surface of data management and DBA tools. There are many other categories and types of tools, such as data synchronization and movement tools, data archiving, OLAP and query tools, trigger and procedure management tools, and so on.