Reference no: EM13946300

Case study

The role of the database administrator

An overview of what database administration means in the real world.

Respected consultant, Tomas B. Cox, offers a white paper that sheds light on the valuable role of the database administrator.

Understanding the DBA role

The DBA role naturally divides into three major activities: ongoing maintenance of production databases (operations DBA); planning, design, and development of new database applications, or major changes to existing applications (development DBA, or architect); and management of an organisation's data and metadata (data administrator). One person may perform all three roles, but each is profoundly different.

Modern organisations depend on several utilities centrally managed services distributed across networks the most common being electricity, water, and telephone services. Increasingly, organisations also depend on LAN and database services. Some organisations, such as airlines, with their reservation systems, or Amazon.com, with its Internet-based order system, are extremely sensitive to the availability of an underlying database service.

Established utilities such as telephone and power companies have evolved over time to maintain a relatively high degree of reliability and predictability or at least, high by the standards of the database industry. They've done this by defining exactly what they need to deliver and then carefully monitoring actual delivery on a real-time basis, storing these measurements for review and management. Service outages are analysed with an eye to preventing their recurrence, and problems are anticipated and prevented more often than reacted to. Even natural disasters are planned for, and the response is swift, co-ordinated, and well rehearsed.

Any database that is key to an organisation's operations such as Amazon.com's order database is functionally the same as any of these other utilities; work cannot proceed if the service is interrupted. The Internet is driving much higher database-uptime standards, because even a small mom-and-pop site that wants to sell on the Internet must be up all the time, or it will lose orders.

As Glenn Slate, operations manager for eMerchandise.com, puts it: ‘Better no visitors than visitors who have a bad experience, because they will go away, stay away, and warn their friends away. Response time, uptime, and reliability are crucial, especially for us, because we target an international audience. Many people in this industry don't realise we're on a frontier. Every shopper today will influence 20 friends who are getting online tomorrow.'

The operations DBA

Just as key databases must be treated seriously, so too the role of the operations DBA must be treated seriously. A good operations DBA spends most of the day not responding to user complaints but following a checklist of daily, weekly, and monthly maintenance tasks. The tasks will have been thought out and continually refined in order to anticipate, prevent, or detect and resolve problems. Every new problem will eventually give rise to a change in one or more checklists, bringing that problem under the overall detection-and-prevention regime. Once a steady state is achieved, the operations DBA role becomes one of quiet routine.

A truly state-of-the-art organisation will also modify database designs and DBA practices with these ideals in mind, and this can be taken to astounding lengths. One firm has created a set of design guidelines and self-running scripts designed to allow Oracle databases to be deployed to sites with no DBA at all and to run untouched for two years or more. Other sites have achieved similar economies of scale, where a handful of operations DBAs monitor and manage hundreds of remote Oracle databases.

Senior executives and managers need to lead the way for their organisations to adopt the proactive operations-DBA approach. The following would be excellent first steps:

¦ Formally separate operations DBAs from other DBAs, giving them different titles and possibly different managers
¦ Devise a monetary bonus plan linked to reliable database uptime and performance
¦ Create an infrastructure measurement group (IMG) that reports to the user community (not the IS group) and provides the uptime and performance metrics
¦ Foster a culture that rewards reliability, predictability and prevention, rather than heroics or extreme effort
¦ Create a formal service-level agreement (SLA) between IS and end users for each database application; making sure the IMG measures applications against the SLA
¦ Budget resources around the SLAs.

The architect

Successful software-development projects usually have one person, or, at most, a tiny group of people who design the application's architecture. The role of the architect is crucial to the project's success. It is quite common for the architect to be a person who has a DBA background and whose focus is on database design rather than operations. However, an architect who knows the demands of the operations side is more likely to design a database that's easy to maintain. For this reason, cross-training between the architect (sometimes called a development DBA or a design DBA) and the operations DBA is a good idea.

One of the architect's trickiest tasks is to change the structure of an existing database known as a schema change. Even a development or test database must be considered ‘in use' if developers are trying to write code against it, or if testers are running test cycles on connected code.

Author Mark Gurry wrote a white paper on schema changes on production databases, identifying 17 distinct steps required for a given change to succeed without disrupting users. He also points out that there must be a complete rollback script ready in case any step should fail, so that the database can be returned to its original state.

In most well-run shops, the task of executing database changes may be given to the operations DBAs, but the task of writing change scripts belongs to the architect, who must test them before releasing the code to production. So crucial is this task that Gurry has created Schema Manager, a tool from Quest Software (www.quests.com) that automates the script-generation process. Such a tool can increase architect productivity 100-fold during schema-change tasks.

Tasks that properly belong to the architect or the development DBA include table design (and redesign), security design, impact analysis, and table modification.

The data administrator

With the rise of data warehouses and data marts has come an increasing awareness of the strategic value of corporate metadata. Without good metadata, users and IS shops find it nearly impossible to compare data from different systems. Metadata is becoming the next battleground between database firms. Naturally, someone needs to be in charge of metadata the data administrator. Identifying and empowering a formal data administrator will shorten data warehouse project time lines and will improve the quality of data flowing through the organisation.

‘Metadata is the connective tissue of information', says Mohit Sahgal of Andersen Consulting. ‘With it, the most far-flung applications in the organisation can be tied together into a functioning whole. Without it, nothing will make sense.'

The data administrator must deal with the political reality that information is power. Just as banks have strict policies on the handling of cash it must be checked in, checked out, counted, and tracked so must information systems be subject to rigorous scrutiny. We must treat information like banks treat cash. If some were to go missing, it must be clear who was responsible for it.

Someone must set data-handling policies. Who enters new product numbers into the system? Who changes the format of product numbers? Who must be notified of format changes? What format will a new application use? These are the questions the data administrator must answer and then track the answers and audit compliance with policies.

The data administrator finds out or assigns who owns which data elements and who can create, change, and delete what. If a regional database keeps a copy of the corporate master price list, then who is responsible for keeping that copy current the price-list owner, who must then push changes in, or the application owner.