Continue in 2 seconds

We are (were) primarily a mainframe shop that has, in the past couple of years, moved first to Oracle and most recently to SQL Server databases.

By
  • Sid Adelman, Clay Rehm
Published
  • January 13 2003, 1:00am EST

Q:

We are (were) primarily a mainframe shop that has, in the past couple of years, moved first to Oracle and most recently to SQL Server databases. We have begun to develop more and more applications on the PC and are looking for the reasons to create the position of DBA. We currently have people spending some of their time doing some of the DBA functions but don't really understand all the advantages or functions of a full-time DBA. Can you enumerate some of the advantages?

A:

Sid Adelman’s Answer: The following is taken from Data Warehouse Project Management by Sid Adelman and Larissa Moss, published by Addison Wesley, 2000.

The database administrator (DBA) is responsible for the physical aspects of the data warehouse. This includes physical design, performance, backup and recovery. Before starting, the DBAs must understand the users’ basic requirements and how the data warehouse databases will be accessed. Some changes to the design and configuration of the system are more easily made than others. The more that is known initially, the less disruptive and costly the changes will be.

Data warehouse DBAs are usually more closely involved with the users than are the DBAs responsible for operational (OLTP) systems. Data warehouse DBAs are constantly monitoring and tuning the data warehouse databases, adding indexes and summary tables. They monitor the SQL generated from the queries to help improve individual query performance.

The database administrator will typically use a modeling CASE tool to create the physical database design. The DBA will work closely with the Data Administrator in designing the database. The same CASE tool should be used for the physical database design that was used for the logical data model. The advantage is that the mapping between the logical and physical data models can be done on the CASE tool and exported to the central repository without having to write additional programs.

Database administrators will then create the data definition language or use the CASE tool to create the data definition language to build the database. Database administration will always have an eye to performance. Performance will dictate designs and the creation of keys and indexes.

If a distributed database environment is being considered, a good rule of thumb is the three (or four) to one complexity of a distributed database over one that is centralized. This does not mean data marts should not be distributed. There are some excellent reasons for distribution, but their cost, complexity, increased administration, and availability risk must be factored into any distribution decision.

Database administration is sometimes responsible for capacity planning, always responsible for physical design for good performance, for monitoring response time and resources used (CPU, disk I/O), for evaluating performance problems, for tuning the databases, and for reviewing the complicated SQL statements written by both application developers and power users.

Clay Rehm’s Answer: Typically the data architect (DA) is more of an analyst role who has great communication skills, works well with the business side in interviewing and identifying business requirements and translating those requirements into data models. The database administrator (DBA) typically is more of a developer/programmer role who has great database technical skills and is usually well versed in a relational database management system (RDBMS) such as DB2, Oracle and SQL Server. The DBA creates and supports the physical data model and supports and maintains the RDBMS. If the DA and DBA roles exist, then the DA creates the conceptual and logical data models based on the business requirements, and the DBA translates these models into physical data models and physical databases.

How your company would function without some kind of DBA role truly amazes me. Who is handling physical data modeling, backup and recovery, performance monitoring, tuning, database modifications and RDBMS upgrades?

The DBA is responsible for the physical aspects of the database including database design, performance, backup and recovery. This includes (but not limited to):

  • Generating the physical database design;
  • Distributing data and deploying distributed databases;
  • Communicating physical requirements to capacity planners;
  • Understanding alternative database designs;
  • Monitoring and analyzing response time, data load times;
  • Monitoring computer resources used (CPU, disk, memory), disk I/O activity;
  • Monitoring summarizations to determine which data is frequently summarized
  • Creating indexes, partitions, summary tables;
  • Generating, loading and using technical meta data, specifically meta data used to create the data definitions for the databases;
  • Using, evaluating and implementing RDBMS utilities.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access