Facilitating Efficient Data Management
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, 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 database administrator (DBA) is usually quite hectic. The DBA is required to maintain production and test environments while keeping an eye on active application development projects, attending strategy and design meetings, helping select and evaluate new products, and connecting legacy systems to the Web.
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 necessary because when production systems 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 for managing databases: performance, administration and recovery.
Performance tools help DBAs gauge the responsiveness and efficiency of SQL queries, database structures and system parameters. Such tools run in the background and capture database performance statistics, trace details and alert the DBA when problems occur. Advanced performance tools can take proactive measures to correct problems as they happen. BMC Software’s Smart DBA is an example of a performance, administration and recovery tool.
Your performance management tools must 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. 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, 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 component of database performance is the application itself. 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 rewrite SQL to be more efficient.
Administration tools simplify 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 ran.
The most important administration tool is the database change manager. 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, the DBA is posed with a very difficult problem. 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 re-created 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. If you’re working remotely, Expand Beyond’s Pocket DBA will allow remote database administration for various DBMSs from a wireless device or via a Web browser.
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.
Database Security and Integrity
The security and integrity of data in p