In my last posting I wrote about what it is that a DBA actually does, but not every DBA does exactly the same thing as every other DBA. At a high level, all DBAs are tasked with managing and assuring the efficiency of database systems, but there are actually many different types of DBAs. Some focus on logical design; others focus on physical design; some DBAs specialize in building systems and others specialize in maintaining and tuning systems; and there are specialty DBAs and general-purpose DBAs. Truly, the job of DBA encompasses many roles.
Some organizations choose to split DBA responsibilities into separate jobs. Of course, this occurs most frequently in larger organizations, because smaller organizations often cannot afford the luxury of having multiple, specialty DBAs.
Still other companies simply hire DBAs to perform all of the tasks required to design, create, document, tune, and maintain the organization’s data, databases, and database management systems. Let’s look at some of the more common types of DBA.
A system DBA focuses on technical rather than business issues, primarily in the system administration area. Typical tasks center on the physical installation and performance of the DBMS software and can include the following:
- Installing new DBMS versions and applying maintenance fixes supplied by the DBMS vendor
- Setting and tuning system parameters
- Tuning the operating system, network, and transaction processors to work with the DBMS
- Ensuring appropriate storage for the DBMS
- Enabling the DBMS to work with storage devices and storage management software
- Interfacing with any other technologies required by database applications
- Installing third-party DBA tools
System DBAs are rarely involved with actual implementation of databases and applications. They might get involved in application tuning when operating system parameters or complex DBMS parameters need to be altered.
Indeed, the job of system DBA usually exists only if the organization does not have an official system administration or systems programming department.
Some organizations create a separate position, database architect, for design and implementation of new databases. The database architect is involved in new design and development work only; he is not involved in maintenance, administration, or tuning of established databases and applications. The database architect designs new databases for new or existing applications.
The rationale for creating a separate position is that the skills required for designing new databases are different from the skills required to keep an existing database implementation up and running. A database architect is more likely than a general-purpose DBA to have data administration and modeling expertise.
Typical tasks performed by the database architect include:
- Creating a logical data model (if no DA or data modeler position exists)
- Translating logical data models into physical database designs
- Implementing efficient databases, including specifying physical characteristics, designing efficient indexes, and mapping database objects to physical storage devices
- Analyzing data access and modification requirements to ensure efficient SQL and optimal database design
- Creating backup and recovery strategies for new databases
Many organizations do not staff a separate database architect position, instead requiring DBAs to work on both new and established database projects.
Another common staff position is the database analyst. There is really no set definition for this position. Sometimes junior DBAs are referred to as database analysts. Sometimes a database analyst performs a role similar to that of the database architect. Sometimes the data administrator is referred to as the database analyst or perhaps as the data analyst. And sometimes a database analyst is just another term used by some companies instead of database administrator.
A data modeler is usually responsible for a subset of the DA’s responsibilities. Data modeling tasks include the following:
- Collecting data requirements for development projects
- Analyzing the data requirements
- Designing project-based conceptual and logical data models
- Creating and updating a corporate data model
- Ensuring that the DBAs have a sound understanding of the data models
In direct contrast to the system DBA is the application DBA. The application DBA focuses on database design and the ongoing support and administration of databases for a specific application or applications. The application DBA is likely to be an expert at writing and debugging complex SQL and understands the best ways to incorporate database requests into application programs. The application DBA must also be capable of performing database change management, performance tuning, and most of the other roles of the DBA. The difference is the focus of the application DBA—it is on a specific subset of applications rather than the overall DBMS implementation and database environment.
Not every organization staffs application DBAs. However, when application DBAs exist, general-purpose DBAs are still required to support the overall database environment and infrastructure. When application DBAs do not exist within an organization, general-purpose DBAs are likely to be assigned to support specific applications while also maintaining the organization’s database environment.
There are pros and cons to staffing application DBAs. The arguments in favor of application DBAs include the following:
- An application DBA can better focus on an individual application, which can result in better service to the developers of that application.
- The application DBA is more often viewed as an integral component of the development team and therefore is better informed about new development plans and changes.
- Because the application DBA works consistently on a specific set of applications, he can acquire a better overall understanding of how each application works, enabling him to better support the needs of the application developers.
- With a more comprehensive understanding of the application, an application DBA will have a better understanding of how the application impacts the overall business. This knowledge will likely result in the execution of DBA tasks to better support the organization.
But all is not favorable for application DBAs. There are cons to implementing an application DBA role:
- An application DBA can lose sight of the overall data needs of the organization because of his narrow focus on a single application.
- The application DBA can become isolated. Lack of communication with a centralized DBA group (if one exists) can result in diminished sharing of skills.
- When an application DBA implements useful procedures, it takes more effort to share these procedures with the other DBAs.
- Due to the application-centric nature of the position, an application DBA can lose sight of new features and functionality delivered by the DBMS group.
In general, when staffing application DBAs, be sure to also staff a centralized DBA group. The application DBAs should have primary responsibility for specific applications, but should also be viewed as part of the centralized DBA group.
Larger organizations sometimes create very specialized DBAs that focus on a specific DBA task. However, task-oriented DBAs are quite rare outside of very large IT shops. One example of a task-oriented DBA is a backup-and-recovery DBA who devotes his entire day to ensuring the recoverability of the organization’s databases. Security, compliance and data protection are other subjects that could be the focus of a task-oriented DBA.
Most organizations cannot afford this level of specialization, but when possible, task-oriented DBAs can ensure that very knowledgeable specialists tackle very important DBA tasks.
Performance analysts are a specific type of task-oriented DBA. The performance analyst, more common than other task-oriented DBAs, focuses solely on the performance of database applications.
A performance analyst must understand the details and nuances of SQL coding for performance and be able to design databases for performance. A performance analyst will have very detailed technical knowledge of the DBMS so that he can make appropriate changes to DBMS and system parameters when required.
However, the performance analyst should not be a system DBA. The performance analyst must be able to speak to application developers in their language in order to help them facilitate appropriate program changes for performance.
The performance analyst is usually one of the most skilled, senior members of the DBA staff, a role that s/he has grown into due to experience and the respect s/he has gained in past tuning endeavors.
Data Warehouse Administrator
Organizations that implement data warehouses for performing in-depth data analysis often staff DBAs specifically to monitor and support the data warehouse environment. Data warehouse administrators must be capable DBAs, but with a thorough understanding of the differences between a database that supports OLTP and a data warehouse. Data warehouse administration requires experience with the following:
- Business intelligence, query, and reporting tools
- Database design for read-only access
- Data warehousing design issues such as star schema
- Data warehousing technologies such as OLAP (including ROLAP, MOLAP, and HOLAP)
- Data transformation and conversion
- Data quality issues
- Data formats for loading and unloading of data
There are many types of data management jobs and it can be confusing when you try to match job title up against the responsibilities of the job. This document outlines the various “jobs” that can be considered (at least somewhat) physical, database administration duties.