©  2021  Mullins Consulting, Inc.   All Rights Reserved   Privacy Policy   Contact Us

Tools to Facilitate Efficient Database Management

by Craig S. Mullins

From Certification Magazine, November 2004.

© 2012 Craig S. Mullins,  

November 2004

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 a tool. 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. Database log analysis tools are useful for security as well because they can be used to examine log entries and format them in a readable fashion to show who did what when to specific pieces of 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. Armed with such information a concerted clean-up effort can be exacted on the 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.

Additionally, the development DBA must work with the application team to create and maintain effective database-coupled application logic – stored procedures, triggers, and user-defined functions (UDFs). These are programs that are under the control of the DBMS. The development DBA will help to build, test, and maintain stored procedures, triggers, and UDFs.

Data Movement Tools

Once data is populated in a database it is not likely to remain dormant, just sitting there. Most organizations need to move data from platform to platform, synchronize data between different DBMSs, and archive old or infrequently referenced data.

Data synchronization tools are used to keep data accurate and up-to-date across multiple servers. For example, a production DB2 database might be used as the source for synchronizing data to a target Oracle data warehouse. Once the rules for extraction, transformation, and loading are set up, the tool can keep the data synchronized across the different DBMSs and platforms.

Data archiving is another type of data movement that is conducted for entirely different reasons. Over time, databases tend to grow in size. Depending on the business requirements, older data may not be accessed as frequently as newer data – if it is accessed at all any more. To improve efficiency, infrequently accessed data can be archived. Modern data archival tools are active. This means they can be set up with rules to trigger the archival process, as well as to automatically retrieve the data if it is accessed again by a query.

Data Management Tool Requirements

Today’s data management tools provide intelligent automation to reduce the problems inherent in the tedious day-to-day tasks of database administration. Simple automation is no longer sufficient. Modern data management software must be able to intelligently monitor, analyze, and optimize applications using past, present, and future analysis of collected data. Simply stated, the software should work the way a consultant works--fulfilling the role of a trusted advisor. The end result - software that functions like a consultant - enables your precious human resources to spend time on research, strategy, planning, and implementing new and advanced features and technologies.

Furthermore, modern data management tools should provide cross-platform, heterogeneous management. For most medium-to-large IT organization it is not enough to manage just Oracle, for example. This is so because most companies have multiple DBMSs that need to be managed. When the tools can manage cross-platform the DBA learning curve is reduced and productivity is enhanced.

And while it is true that today’s DBMS products are becoming more self-managing, they do not yet provide out-of-the-box, lights-out operation, nor do they offer all of the speed, usability, and ease of use features of ISV data management tools. An organization looking to provide 24/7 data availability coupled with efficient performance will have to augment the capabilities of their DBMS software with data management and DBA tools to get the job done.

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 management software. Using intelligent, automated DBA tools will help to reduce the amount of time, effort, and human error associated with implementing and managing efficient database applications.

Most modern applications utilize database management systems (DBMS) to create, store, and manage business data. The DBMS software enables end users or application programmers to share data. It provides a systematic method of creating, updating, retrieving and storing information in a database. DBMSs also are generally responsible for data integrity, data access control, and automated rollback, restart and recovery.

When using a DBMS though, database administration is required to ensure the efficient and proper care of the data in the database. The most popular DBMS products include Oracle, IBM DB2, Microsoft SQL Server, and Sybase Adaptive Server. Each is a complete, full-function DBMS. An organization can install and use the DBMS as delivered, but the functionality needed to support large-scale database development is not provided adequately by the DBMS alone.

Fortunately, many data management tools are available that enhance the functionality of the DBMS, ease the administrative burden and reduce the possibilities of database error.

Production DBA Tools

A day in the life of a DBA is usually quite hectic. The DBA is required to maintain production and test environments while at the same time keeping an eye on active application development projects, attending strategy and design meetings, helping to select and evaluate new products, and connecting legacy systems to the Web. And that power user in Sales  just submitted another "query from hell" that is bringing the system to a halt. All of these things can occur within a single DBA work day.

When problems occur, the database environment 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. These types of tools keep databases running up to PAR. In this context, PAR has dual meaning. As in golf, it means an amount taken as an average or norm. But for DBAs PAR can also be an acronym that defines the three primary responsibilities they have for managing databases: Performance, Administration, and Recovery.

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 trace details and alert the DBA when problems occur. Advanced performance tools can take proactive measures to correct problems as they happen.

It is imperative that your performance management tools 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. To deliver consistent system performance, the DBA must have the resources to monitor, manage, and optimize the performance of these disparate pieces of hardware and software. Some of the tasks required for system tuning include the proper allocation and management of memory structures (e.g., buffer pools, program cache area), storage management, integration of the DBMS with other system software, proper usage of database logs, and coordination of the operating system resources used by the DBMS. Additionally, the DBA must control the installation, configuration, and migration of the DBMS software. If the system isn't performing properly, everything that uses the system will perform poorly. In other words, a poorly performing system impacts every database application.

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. The performance of every application that requires this data will be negatively impacted. 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. To accomplish an online reorganization, the database structures to be reorganized must be copied. Then this "shadow" copy is reorganized. When the shadow reorganization is complete, the reorg tool "catches up" by reading the log to apply any changes that were made during the online reorganization process. Some vendors offer leading-edge technology that enables the reorg to catch up without having to read the log. This is accomplished by caching data modifications as they are made. The reorg can read the cached information much quicker than trying to catch up by reading the log.

The third, and final, 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 is simple to learn and easy to start using. But SQL tuning and optimization is an art that takes years 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.

The most important administration tool is the database change manager. The DBA is the custodian of database changes – and a database, once implemented, is sure to require future changes. To effectively make those changes, the DBA needs to consider the impact of each change in terms of quick and efficient delivery. Without a robust, time-tested product that is designed to effect database changes, the DBA is posed with very difficult problem. This is so because today’s DBMS products do not support fast and efficient database structure changes. Each DBMS offers differing levels of support for making changes, but none easily supports every type of change that might be required. One quick example: most do not enable a column to be added to the middle of an existing row. To do so, the table must be dropped and recreated with the new column in the middle. This causes cascading drops and can wreck a database. A database change manager will automate the creation and execution of a correct script for implementing required changes – and will ensure that data integrity is not lost. All in all, a database change management product will improve availability, minimize errors, and speed up your time to market.

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.

If the DBA keeps the databases up to PAR regularly, it can make life a lot less hectic. A well thought out approach to PAR involves instituting a proactive approach to performance management, administration and change management, and database backup and recovery. Adopting tools with built-in intelligence and automation capabilities greatly improves data availability and performance.

http://www.certmag.com/

Table 1. Sample DBA Tools Vendors