An update on the latest tools and techniques to help simplify DBA tasks.
It's 2:30 in the morning and, like many DBAs, Sam is asleep right now with his pager close by. As most good DBAs do, Sam runs batch loads into his data warehouse at night so he won't use up resources on his large Unix server, which his users need for OLAP processing during the day. The only problem this morning is that a larger-than-normal volume of data has shown up to be processed, which could spell trouble for Sam's restful night of sleep. As more and more data begins to pour into his Oracle database, Sam's target tablespace begins to fill up until it finally reaches the stage of being completely full. At this point, Sam's pager would normally begin to summon him from sleep to correct the out-of-space problem and then restart his batch load.
However, that's not what happens. Running on Sam's Unix server is an intelligent database agent that has been continually monitoring the amount of free space in all of the Oracle tablespaces. Detecting that one of the tablespaces has violated a minimum predefined threshold of 10 percent free space, the agent has automatically added a new datafile to the tablespace that increases the amount of available space to over 75 percent, which lets the larger-than-expected batch load finish without errors. In addition, the agent sends an explanatory email message to Sam, which he reads later that morning after waking from an uninterrupted night of sleep.
Welcome to the world of the automated DBA. Today's administrators have at their disposal a new breed of intelligent, sophisticated database tools that lets them manage their complicated network of database engines better than ever before. [For more background information on the subject of tool automation, see our cover story, "Server Automation Principles," from DBMS, February 1996, page 50.] Although some database professionals view these types of products with suspicion, more administrators are turning to them every day to assist in building, monitoring, and maintaining corporate data. In the past, a DBA may have had only one database vendor and platform to deal with --but no more. Thanks to client/server and the onslaught of Internet/Intranet applications that need to access database servers, today's DBAs must build and maintain an intricate network of databases that are multivendor and multiplatform. They must have knowledge of each vendor's SQL syntax (for example, Oracle's PL/SQL and SQL Server's Transact SQL) and be versed in each database's backup and recovery needs, tuning strategies, and much more. Is it any wonder DBAs are searching high and low to find tools that will help them?
If you're one of these people, then read on. It's my aim to provide a path to some of the better tools that can assist you with the many tasks needed for database administration. Let's look first at some of the current trends, and then we'll turn our attention to specific tasks that DBAs must perform to get their job done, and how some of the vendors are meeting those challenges.
Instead of having to react constantly, administrators can now step back and, armed with the right toolset, map out a strategy ahead of time to deal with many of the problems that can bring a database to its knees. By either creating or working with vendor-predefined thresholds, DBAs can automatically monitor their databases for potentially problematic situations and fix them before they become a crisis. The actions a DBA can define include pager or email warnings to notify administrators that a problem is brewing and alerts that trigger corrective actions where a job is run to head a major predicament off at the pass.
These scenarios normally consist of a console or scheduling facility where DBAs identify potential problems and select which thresholds should trigger action. Coupled with the console are intelligent agents installed on each database server that are used to monitor the health of the database. These agents are responsible for actually notifying administrators of problems and taking corrective measures when necessary. I'll profile a few of these products a little later to give you more insight into how they work.
That scene is changing. Now database tool vendors find themselves competing not only against each other for corporate dollars, but against the very companies their products are designed to work with. For example, the quantum-leap-of-the-year award, in my opinion, has to go to Oracle for its Enterprise Manager toolset and Performance Pack, which ships with version 7.3 of Oracle's flagship database. Anyone who has been working with Oracle products for the past several years knows how command-line-driven and primitive Oracle's DBA tools have been, even with the Windows NT workgroup product (yes, workgroup does ship with GUI tools, but they aren't exactly great to work with). With Enterprise Manager (see Figure 1), Oracle finally introduced a product that lets administrators manage many of the major tasks facing Oracle DBAs on multiple platforms. I've used Enterprise Manager successfully to take care of my databases on Unix, Windows NT, and Novell platforms, and I have been impressed with the tool's power.
How will third-party vendors compete against Enterprise Manager offerings from Oracle, Sybase, Microsoft, and other database powerhouses? Many administrators still turn to third-party companies because their tools allow heterogeneous management of databases from a single console. It's true that products from Oracle, Sybase, and the like currently only work with their own database engines, and enabling DBAs to control a variety of databases from one product simplifies life. But will that capability, along with other subtle features, be enough for corporations to continue shelling out money for third-party tools when decent management products ship "free" with the database? The answer for now continues to be "yes."
In terms of functionality, though, what are DBAs interested in? In an article of this nature, there's no way I can list every category to answer that question, but from a broad perspective, here are some of the most critical day-to-day duties that many administrators want help with:
Many DBAs (myself included) have abandoned keeping database object creation scripts in server directories in favor of maintaining a physical database design that can be constructed and versioned easily in a modeling tool. These physical modeling tools provide relief from writing endless lines of data definition language (DDL) to build complete database or user schemas. Instead, once all physical design characteristics have been entered in a graphical manner, you can generate a complete database creation script that can be either launched directly from the tool to create a database or run through the database vendor's query tool. Referential integrity support is also built into most modeling tools, allowing either the declarative form of referential integrity to be used for databases that support it, or triggers to be automatically constructed to handle integrity constraints.
In addition, the major modeling tools also offer reverse-engineering capabilities to let DBAs capture existing databases in a physical design. For DBAs who must maintain or alter vendor-supplied database packages, this feature offers real help that's been needed for a long time.
The two big heavyweights duking it out in this arena are LogicWorks' ERwin and Powersoft's S-Designor. With version 5.0, Powersoft recently split apart its S-Designor offering into components that may be purchased separately. By combining Powersoft's DataArchitect product with MetaWorks, administrators have a powerful tool that performs conceptual and physical data modeling and is coupled with a robust repository to help corporations share models and manage metadata. With version 5 of S-Designor, Powersoft has corrected several problems that existed previously with the repository. Bugs, however, still exist. For example, the tool is supposed to work with most relational databases, but I ran into numerous problems when I tried to use it with Microsoft SQL Server. When I phoned Powersoft's tech support and mentioned MS SQL Server, the rep on the other end replied, "Ooooh -- lotsa problems . . ." The product works great, though, with Oracle or Powersoft's own Sybase SQL Anywhere database.
Embarcadero Technologies recently introduced its ER/1 product into the modeling fray. Embarcadero has ambitious plans for ER/1, including an Internet/Intranet strategy. In addition to having a normal GUI offering for ER/1, engineers at Embarcadero are using Java and ActiveX to make ER/1 a Web browser plug-in. This will enable models to be published on a corporate Intranet for review, and it will let users work with ER/1 from home on their own Web browser.
For starters, you can use some of the modeling tools I've mentioned to estimate the size of your overall database, tables, and indexes. Once a physical design has been mapped out, Powersoft's S-Designor can produce a report that indicates how large each table and index should be, given an estimate of the expected number of rows. You can then set these properties inside the tool so that each table or index's DDL contains the correct size parameters and tablespace or segment placement.
Once you know the approximate size of your intended database, you should construct the actual tablespaces or devices that will hold the information next. For this task, nearly every vendor offers GUI tools to build these types of database structures. One thing to note from a buyer's perspective: It's important that the vendor and tool you choose keep up with enhancements that the database giants make in their product. For example, newer releases of Oracle7 let you specify that an underlying datafile of a tablespace extends automatically when available free space becomes an issue. Users of Platinum's Desktop DBA product have this relatively new option (autoextend) available to them when they create their tablespaces.
Despite DBAs' best efforts, space problems do arise and must be dealt with as soon as possible to minimize response-time problems and the dreaded out-of-space errors. To detect when tablespaces or devices are near the end of their free space, nearly every third-party vendor, as well as the database companies themselves, provides either graphical tools or stored procedures that will report on the amount of used vs. free space. Console products (which I will discuss in more detail later) can provide alerts and warnings as well as "fix it" jobs that can monitor and correct space problems.
A more subtle, but almost equally problematic, situation is fragmentation, both at the tablespace and table/index level. Over time, tablespaces can become fragmented if tables and indexes are constantly created and dropped. Two types of fragmentation normally occur: honeycombs and bubbles. Honeycombs are two adjacent blocks of space right next to one another in a tablespace. Bubbles, which are harder to deal with, are blocks of space scattered throughout a tablespace.
Oracle provides a new tablespace tool, bundled with the Performance Pack that ships with version 7.3 of Oracle (currently priced separately at $295 per user), that lets you view all extents in a tablespace, including bubbles and honeycombs. To combat honeycombing, you can choose an option to coalesce all adjacent honeycombs into single chunks of space. No option is available, however, to get rid of bubbles.
For DBAs who want a more complete solution, Bradmark Technologies offers its excellent DBGeneral product, which performs both table-level and full tablespace reorganization. Platinum Technology also has a strong offering in its TS-Reorg product. With TS-Reorg, you can perform reorganizations of Oracle tablespaces or Informix dbspaces manually, or schedule them to run unattended in off-hours. TS-Reorg also has an excellent reputation for performing fast tablespace reorganizations.
In addition to tablespace fragmentation, DBAs must fight table and index fragmentation. When a table or index has been sized incorrectly, it can grow into multiple extents, some of which may not be adjacent to each other in the tablespace. This situation can lead to degraded response times, because the database must scan for multiple extents throughout the tablespace. For users of SQL Server, tables with clustered indexes can become fragmented when the clustered index splits to keep incoming data in order of the index. Again, detection and correction are necessary for both these situations.
Many tools will show you how many extents a table or index is taking up. Some tools that support MS SQL Server will run the DBCC SHOWCONTIG command to display fragmentation statistics for clustered index tables. To correct Oracle fragmentation, Oracle's new tablespace tool, Tablespace Manager, will schedule and run a job that compresses the fragmented table back into one extent. Bradmark's DBGeneral also does a fine job of this compression. To correct Microsoft SQL Server fragmentation, use Microsoft's Enterprise Manager to graphically rebuild the clustered index, which reorganizes the underlying table. You can also issue the new Microsoft SQL Server 6.5 DBCC command to rebuild a clustered index, which also accomplishes the desired reorganization.
One hurdle that both parties still have to clear are the DBAs who just don't have a firm comfort level using graphical tools to construct their databases. These folks normally come from the command-line-driven Unix or mainframe environment and feel a lack of control when using GUI methods. A friend of mine recently attended a class at one of the big database companies; his instructor first went over all of the company's new GUI tools. Immediately, however, she followed up with, "But I don't like or recommend using them --everyone has all they need with the command line tools and should use them instead."
That instructor has a valid concern in one area. Because GUI tools shield the DBA from the underlying syntax, there is a chance that an administrator might forget some SQL that might become necessary. Networks go down all the time, and DBAs could find themselves sitting at server consoles wracking their brains to remember the command-line syntax they need. Of course, a good set of documentation helps in these cases. (To keep this from happening, many tools have a "Show SQL" feature that lets you view which SQL commands the graphical tool is sending to the server.)
For those of us who use the GUI tools most of the time, however, they're a hard show to beat. I especially appreciate the way in which the GUI tools handle security. No matter the database, it usually is a chore to use straight SQL to find out the object permissions and database privileges granted to groups and/or users. With the graphical productivity tools, a few clicks of the mouse show me exactly the detail I need. Adding or revoking permissions is a breeze, too.
Many tools in this area have taken up complete residence at corporations that heavily rely on them to perform day-to-day DBA duties. I have yet to find a SQL Server shop where I haven't seen Embarcadero's DBArtisan product. The company's Team/SQL product line is also hitting the mark for shops that use Oracle. For database professionals looking to manage different databases from one tool, Platinum's Desktop DBA does a very good job, as do BMC's strong offerings in its Patrol product line.
Most databases come with an EXPLAIN function built in, so a SQL statement can be put under the microscope to see how the engine's optimizer is selecting the access path to the data. Also, many database vendors ship some tools to help examine the time SQL statements spend in the parse, execute, and fetch modes. If these tools are provided by their database of choice, DBAs should encourage their development staff to use them to find out exactly what's going on behind the scenes with the optimizer. Surprisingly, there aren't many good third-party products in this area to examine application performance. Platinum, however, has an excellent offering in its Plan Analyzer products, which I've found to be first-rate.
What about current applications? How can you tell if poorly performing SQL is the culprit behind slow system-response times? What's needed is the ability to capture executing SQL statements and then put them through the statistical ringer. One new tool that is shipping with Oracle's performance pack, TopSessions, lets you choose SQL statements from active sessions and then issue interactive explains against them to see which access paths the statements are using. Platinum's Plan Analyzer for Oracle lets you filter out suspected system hogs and capture the executing statements for later analysis. (See Figure 2.) I hope that more vendors will release products to assist in this area, especially when so much bang for the buck can be obtained in terms of overall system performance.
DBAs spend a good deal of their time ensuring the proper backup of corporate information, as well as putting together and testing (hopefully!) recovery scenarios. In this era of data warehousing, warehouse administrators also spend hours each day moving data from legacy systems to warehouse servers and occasionally from warehouses to data marts.
Most tool vendors that support backup and recovery operations merely utilize the existing backup commands and products that ship with today's RDBMSs. Relief is provided by outside vendors, however, in the area of scheduling, monitoring, and viewing results from backups and restores. Without decent scheduling facilities, DBAs are forced to hand-code command-file batch programs that are then scheduled via whatever operating system mechanisms are available. These tasks can become difficult to manage when numerous servers and different databases are involved.
The Patrol suite of products, from BMC Software, alleviates this haphazard approach by providing the type of scheduled backup and recovery features that DBAs need. Administrators can manage and perform backup and recovery functions across their enterprise network, regardless of the database vendor. Intelligent agents monitor a job's progress and provide information to a central location for review.
Many database vendors themselves have recognized the need for better control over database backups, and they have provided graphical means to schedule and run these processes. Users of Oracle's or Microsoft's Enterprise Manager products can easily schedule backups to run and then view the results of a night's work in the morning through review facilities. Of course, these products only work with their own database; for heterogeneous support, a product such as Patrol is needed.
Third-party vendors are also providing more tools for moving, loading, and unloading database information. With the popularity of warehouses and the shrinking batch windows facing many DBAs, solutions that provide fast and accurate data transfers are being heavily sought out. Some of this functionality can be accomplished with the built-in replication features of databases, but when more than one database vendor is involved (for example, a large Informix data warehouse that feeds numerous Microsoft SQL Server data marts), the plot tends to get a bit thicker.
For administrators seeking robust data transfer mechanisms, Platinum offers the InfoPump product, which replicates and moves data among disparate databases. Movements of data can be performed on a scheduled basis or predicated upon some event. For Oracle users who want to speed unloads of tabular data, Platinum also provides Fast Unload for Oracle. This product extracts information from Oracle databases and builds file layouts that can then be used by products such as Sybase's Bulk Copy Program (BCP) or Informix's DBLoad; standard delimited files can also be created. In addition to Platinum, other vendors such as BMC also offer strong utilities in this area.
With the current tool offerings available, this slow, manual process doesn't have to be the case any longer. An administrator can now take a much more proactive stance, putting an end (well, almost) to the firefighter persona that the DBA traditionally assumed. Tools should allow the identification of problems, troublesome thresholds, and corrective action for when those thresholds have been exceeded. The good news is that for some databases, these types of tools come free. Others require you to open your checkbook and make a purchase that could be anywhere from under $1000 to many times that amount.
An example in the free tools area is Microsoft's Enterprise Manager, which ships with MS SQL Server. Users can define alerts in the tool, and these alerts are monitored by Microsoft's SQL Executive agent. (See Figure 3.) If an alert is triggered, you can request that a task be executed to correct the problem and/or notify you by pager or email. Oracle's new Enterprise Manager product provides almost identical functionality for its database.
Tools that come in around the thousand- to several-thousand-dollar range include Platinum's SQL Spy, which provides competent performance monitoring and alerting for databases such as Oracle, Microsoft SQL Server, and Informix. Once you get out of this price range, it's time to bring in the big guns.
Products that provide complete administration solutions include BMC's Patrol suite, Bradmark's DBGeneral database console, Platinum's DBVision product, and Common Sense Computing's I/Watch. Nearly all animals in this jungle have a central console that manages multiple databases on various platforms. Installed on each database server is an intelligent agent that provides the eyes and ears into the database and monitors the database for troubling situations. Administrators can normally monitor their platforms in a graphical nature and, with a few clicks of the mouse, see complete overviews of their database server's performance. Drill-down capabilities for when exception situations arise are commonly provided.
Thresholds for monitoring purposes either come predefined or can be custom-built. For example, I/Watch has a complete scripting language that gives exceptional freedom to administrators who want to define special thresholds to monitor. Nearly all products in this category can contact a DBA either by pager and/or email when trouble is on the horizon. Another nice feature of tools such as I/Watch is the ability to gather performance data into pre-assigned database tables and review it at a later time for capacity-planning purposes.
One final feature of these tools -- a feature that will be popular with administrators -- is expert guidance when problems are detected. For example, I/Watch's next release will contain an online DBA expert that will recommend solutions to abnormal database conditions.
Needless to say, when evaluating products that perform such critical functions, you need a heavy period of evaluation and testing to ensure that, when the chips are down, the product performs as the marketing material claims it will. Check out each vendor's financial stability and technical support. When you've completed this process, and your tools have been carefully selected, you can then begin to take charge of the huge task of performing accurate, proactive DBA tasks in today's client/server jungle.
Note: For more information on the companies mentioned in this article, refer to the product chart.
Figure 1.
--Using Oracle's Enterprise Manager to administer multiple databases on multiple platforms.
Figure 2.
--Capturing active SQL statements using Platinum's Plan Analyzer for Oracle.
Figure 3.
--Defining an alert and action to take when a database reaches full capacity in Microsoft SQL Server.
January 1997 Table of Contents | Other Contents | Article Index | Search | Site Index | Home
DBMS and Internet Systems (http://www.dbmsmag.com)
Copyright © 1997 Miller Freeman, Inc. ALL RIGHTS RESERVED
Redistribution without permission is prohibited.
Please send questions or comments to dbms@mfi.com
Updated Friday, December 13, 1996.