Q:  

I am very keen to move into the database sector. What are the typical day-to-day tasks of a DBA?

A:  

Sid Adelman’s Answer: The database administrator (DBA) is responsible for the physical aspect of the data warehouse. This includes physical design, performance, backup and recovery. Before starting, the DBA 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 same CASE tool should be used for the physical database design that was used for the logical data model. The advantage in that case 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 administrators always have an eye for performance. Performance will dictate designs and the creation of keys and indexes.

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.

The description of the DBA is taken from Data Warehouse Project Management by Sid Adelman and Larissa Moss, Addison Wesley, 2000

Chuck Kelley’s Answer: This can be considered as a list of starting points for any DBA:

  • Defining and implementing a backup/restore strategy.
  • Creation and tuning of all tables.
  • Tuning of SQL (if relational DBMS).
  • Monitoring of performance from a proactive standpoint.
  • Monitoring disk activity and problem disks for recovery without taking the DBMS down.
  • Restructuring as required.

Again, this is a beginning of a list.
Clay Rehm’s Answer: First, some clarification. A database administrator (DBA) creates and supports the physical data model and RDBMS. However, the mindset of an operational system DBA is much different than the data warehouse DBA. The DW DBA is 100 percent user-oriented and in tune with business issues and processes. This DBA must be very highly skilled in data warehouse RDBMS design, performance, monitoring and tuning. This person must be comfortable with star schemas, snowflake schemas and denormalization.

Other tasks that are required for either type of DBA include:

General:

  • Responsible for the physical aspects of the database including database design, performance, backup and recovery.
  • Communicate physical requirements to capacity planners.

Performance Monitoring:

  • Monitor and analyze response time.
  • Monitor and evaluate data loads.
  • Monitor computer resources used (CPU, disk, memory).
  • Monitor disk I/O activity.
  • Monitor which data fields are being used.
  • Monitor summarizations to determine which data is frequently summarized.

Tuning:

  • Creation of indexes.
  • Creation of partitions.
  • Tuning initialization parameters.
  • Distribute data files across disks.
  • Input to those creating summary tables.
  • Creation of summary tables.

Backup and Recovery

  • Evaluate backup and recovery scenarios and develop procedures.
  • Develop and document backup and recovery plan.
  • Write and test backup and recovery procedures.

Disaster Recovery

  • Evaluate disaster scenarios and develop recovery procedures.
  • Develop and document disaster recovery plan.
  • Write and test disaster recovery.

Security

  • Recommend which RDBMS security features are appropriate.
  • Implement and maintain RDBMS security features.

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