Craig S. Mullins
              
Database Performance Management

Return to Home Page

August 2000

 

Intelligent Database Management
By Craig S. Mullins

It is hard to get good help these days. There are more job openings for qualified, skilled IT professionals, than there are individuals to fill the jobs. And one of the most difficult IT positions to fill is the DBA. DBAs are specifically hard to recruit because the skills required to be a good DBA span multiple disciplines. These skills are difficult to obtain, and to make matters more difficult, the required skill set of a DBA is constantly changing.

To effectively manage enterprise databases, a DBA must understand both the business reasons for storing the data in the database and the technical details of how the data is structured and stored. 

The DBA must understand the business purpose for the data to ensure that it is used appropriately and is accessible when the business requires it to be available. Appropriate usage involves data security rules, user authorization, and ensuring data integrity. Availability involves database tuning, efficient application design, and performance monitoring and tuning. These are difficult and complicated topics. Indeed, entire books have been dedicated to each of these topics.

Duties of the DBA 

The technical duties of the DBA are numerous. These duties span the realm of IT disciplines from logical modeling to physical implementation. The following list comprises most of the duties performed by DBAs: 

  1. Data modeling and database design: DBAs must possess the abilities to create, interpret, and communicate a logical data model and to create an efficient physical database design from a logical data model and application specifications.
  2. Metadata management and repository usage: DBAs need to collect, store, manage, and query data about the data (metadata) in the database and disseminate it to developers that need the information to create effective application systems.
  3. Database schema creation and management: After a physical database design has been created the DBA must be able to translate that physical design into an actual physical database implementation using DDL and to manage that database once it has been implemented.
  4. Procedural skills: A DBA must be able to design, debug, implement, and maintain stored procedures, triggers, and user-defined functions that are stored in the DBMS.
  5. Capacity planning: Because systems are not static, the DBA must be able to predict growth based on application and data usage patterns and implement the necessary database changes to accommodate the growth.
  6. Performance management and tuning: A proactive monitoring system is essential to ensure efficient database access. The DBA must be able to utilize the monitoring environment, interpret its statistics, and make changes to data structures, SQL, application logic, and the DBMS subsystem
    to optimize performance.
  7. Application SQL design reviews: A DBA must understand SQL, the standard relational database access language. Furthermore, the DBA must be able to review SQL and host language programs and to recommend changes for optimization.
  8. Backup and recovery: Data in the database must be protected from hardware, software, system, and human failures. The ability to implement an appropriate database backup and recovery strategy based on data volatility and application availability requirements is required of DBAs.
  9. Ensuring data integrity: DBAs must be able to design a database so that only accurate and appropriate data is entered and maintained.
  10. General database management: The DBA must possess knowledge of the rules of relational database management and the ability to accurately communicate them to others.
  11. Data security: DBAs are required to implement rigorous security schemes for production and test databases to ensure that only authorized users have access to data.
  12. General systems management and networking skills: The database does not exist in a vacuum. It must interact with other components of the IT infrastructure. As such, the DBA must be able to integrate database administration requirements and tasks with general systems management requirements and tasks such as network management, production control and scheduling, and problem resolution, to name just a few systems management disciplines.
  13. ERP and business knowledge: A DBA must be able to understand the requirements of the application users and to administer their databases to avoid interruption of business. This includes understanding how any ERP packages impact the business and how the databases used by those packages differ from traditional relational databases.
  14. Extensible data type administration: Most relational database products provide the ability to extend the data types being managed to include complex, unstructured multimedia data. DBAs need to understand how to implement and administer any extended data types implemented in the DBMS. 

A Lot of Effort

Implementing, managing and maintaining complex database applications spread throughout the world is a difficult task. To support modern applications a vast IT infrastructure is required that encompasses all of the physical things needed to support your applications. This includes your databases, desktops, networks, and servers, as well as any networks and servers outside of your environment that you rely upon for e-business. These things, operating together, create your IT infrastructure. These disparate elements are required to function together efficiently for your applications to deliver service to their users. 

But these things were not originally designed to work together. So not only is the environment increasingly complex, it is inter-related. But it is not necessarily designed to be inter-related. When you change one thing, it usually impacts others. What is the impact of this situation on DBAs?

Well, for starters, DBAs are working overtime just to support the current applications and relational features. But new RDBMS releases are being made available faster than ever before. Just recently Microsoft has announced SQL Server 2000, right on the heels of the very new SQL Server 7. And IBM has announced DB2 Version 7, even though Version 6 was just released last year and most users have not yet migrated to V6.

So, the job of database administration is getting increasingly more
difficult as database technology rapidly advances adding new functionality, more options, and more complex and complicated capabilities. But DBAs are overworked, under-appreciated, and lack the time to gain the essential skills required to support and administer the latest features of the RDBMS they support. What can be done?

Intelligent Automation

One of the ways to reduce these problems is through intelligent automation. As IT professionals we have helped to deliver systems that automate multiple jobs throughout our organizations. That is what computer applications do: they automate someone's job to make that job easier. But we have yet to intelligently automate our DBA jobs. By automating some of the tedious day-to-day tasks of database administration, we can free up some time to learn about new RDBMS features and to implement them appropriately. 

But simple automation is not sufficient. The software should 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.

This advisor software should collect data about the IT environment from the systems (OS, DBMS, OLTP, etc.), objects, and applications. It should require very little initial configuration, so that it is easy to use for novices and skilled users alike. It should detect conditions requiring maintenance actions, and then advise the user of the problem, and finally, and most beneficial to the user, optionally perform the necessary action to correct the problems it identifies. Most management tools available today leave this analysis and execution up to the user. But intelligent automation solutions should be smart enough to optimize and streamline your IT environment with minimal, perhaps no, user or DBA interaction.

The end result, software that functions like a consultant, enables the
precious human resources of your organization to spend time on research, strategy, planning, and implementing new and advanced features and technologies. Only through intelligent automation will we be able to deliver on the promise of technology. 

Synopsis 

As IT tasks get more complex and IT professionals are harder to employ and retain, more and more IT duties should be automated using intelligent management software. This is especially true for very complex jobs, such as DBA. Using intelligent automation will help to reduce the amount of time, effort, and human error associated with managing databases and complex applications.

From Database Trends, August 2000.
 
2000 Craig S. Mullins,  All rights reserved.
Home.