If you are currently a DBA, I bet you can relate to the title of this article. Doesn’t it seem like there are always more things to do at the end of any given day? The sad truth, though, is that many people do not know what a DBA is, does, or why they are even needed! Sometimes, that includes your manager!
So today, we’re going to take a little time to hash through the primary responsibilities and tasks that DBAs are typically charged with performing. Not all of these tasks are needed every day, but some are, and many are of the utmost importance when they are needed.
So, let’s start at the beginning… Every organization that manages data using a database management system (DBMS) requires a database administration group to oversee and assure the proper usage and deployment of the company’s data and databases. With the growing mountain of data and the need to organize that data effectively to deliver value to the business, most modern organizations use a DBMS for their most critical data. So, the need for database administrators (DBAs) is greater today than ever before. However, the discipline of database administration is not well understood or universally practiced in a coherent and easily replicated manner.
Implementing a DBA function in your organization requires careful thought and planning. A successful DBA must acquire a large number of skills — both technological and interpersonal. Let’s examine the skills required of an effective DBA.
General database management. The DBA is the central source of database knowledge in the organization. As such he must understand the basic rules of relational database technology and be able to accurately communicate them to others.
Data modeling and database design. The DBA must be skilled at collecting and analyzing user requirements to derive conceptual and logical data models. This is more difficult than it sounds. A conceptual data model outlines data requirements at a very high level; a logical data model provides in-depth details of data types, lengths, relationships, and cardinality. The DBA uses normalization techniques to deliver sound data models that accurately depict the data requirements of the business. (Of course, if your organization is large enough a completely separate group of data administrators may exist to handle logical database design and data modeling.)
Metadata management and repository usage. The DBA must understand the technical data requirements of the organization. But this is not a complete description of his duties. Metadata, or data about data, also must be maintained. The DBA must collect, store, manage, and provide the ability to query the organization’s metadata. Without metadata, the data stored in databases lacks true meaning. (Once again, if your company has a data administration group then this task will be handled by that group. Of course, that does not mean the DBA can ignore metadata management.)
Database schema creation and management. A DBA must be able to translate a data model or logical database design into an actual physical database implementation. In addition, they must manage that database once it has been implemented. The physical database may not conform to the logical model 100 percent due to physical DBMS features, implementation factors, or performance requirements. The DBA must understand all of the physical nuances of each DBMS used by his organization in order to create efficient physical databases.
Capacity planning. Because data consumption and usage continue to grow at an alarming pace, the DBA must be prepared to support more data, more users, and more connections. The ability to predict growth based on application and data usage patterns and to implement the necessary database changes to accommodate that growth is a core capability of the DBA.
Programming and development. Although the DBA typically is not coding new application programs, s/he does need to know how to write effective programs. Additionally, the DBA is a key participant in production turnover, program optimization (BIND/REBIND) and management, and other infrastructure management to enable application programs to operate effectively and efficiently.
SQL code reviews and walk-throughs. Although application programmers usually write SQL, DBAs are likely to be blamed for poor performance. Therefore, DBAs must possess in-depth SQL knowledge so they can understand and review SQL and host language programs in order to recommend changes for optimization.
Performance management and tuning. Dealing with performance problems is usually the biggest post-implementation nightmare faced by DBAs. As such, the DBA must be able to proactively monitor the database environment and to make changes to data structures, SQL, application logic, and the DBMS subsystem itself in order to optimize performance.
Ensuring availability. Applications and data are more and more required to be up and available 24 hours a day, seven days a week. Globalization and e-business are driving many organizations to implement no-downtime, around-the-clock systems. To manage in such an environment, the DBA must ensure data availability using non-disruptive administration tactics.
Data movement. Data, once stored in a database, is not static. The data may need to move from one database to another, from the DBMS into an external data set, or from the transaction processing system into the data warehouse. The DBA is responsible for efficiently and accurately moving data from place to place as dictated by organizational needs.
Backup and recovery. The DBA must implement an appropriate database backup and recovery strategy for each database file based on data volatility and application availability requirements. Without a backup and recovery strategy, system and user errors could render a database inoperable and useless. Furthermore, the backup strategy must be developed with recovery time objectives in mind, so that data is not unavailable for long periods when problems inevitably occur. This is probably one of the, if not the absolute, most important database administration task.
Ensuring data integrity. DBAs must be able to design databases so that only accurate and appropriate data is entered and maintained. To do so, the DBA can deploy multiple types of database integrity including entity integrity, referential integrity, check constraints, and database triggers. Furthermore, the DBA must ensure the structural integrity of the database. Data integrity is right up there with backup and recovery in importance level.
Procedural coding and debugging. Modern databases are comprised of more than just data — they also contain program code. The DBA must possess procedural skills to help design, debug, implement, and maintain stored procedures, triggers, and user-defined functions that are stored in the DBMS and used by application systems.
Extensible data type administration. The functionality of a modern DBMS can be extended using user-defined data types. The DBA must understand how these extended data types are implemented by the DBMS vendor and be able to implement and administer any extended data types implemented in their databases.
Data security. The DBA is charged with the responsibility of ensuring that only authorized users have access to data. This requires the implementation of a rigorous security infrastructure for production and test databases. Data security comprises both DBMS security (revoke/grant) and security on external resources (file structures, user IDs, and so on).
Database auditing. Being able to report on who did what to which data and when, along with how they acted upon that data, is a requirement for many governmental and industry standards and compliance specifications. DBAs need to be involved in terms of setting up and enabling the DBMS for database auditing capabilities.
General systems management and networking. After a database is implemented, it will be accessed throughout the organization and interact with other technologies. Therefore, the DBA has to be able to function as a jack of all trades in order to integrate database administration requirements and tasks with general systems management requirements and tasks (like job scheduling, network management, transaction processing, and so on).
Business knowledge. DBAs must understand the requirements of the application users and be able to administer their databases to avoid interruption of business. Without a firm understanding of the value provided to the business by their databases and data, the DBA is not likely to be able to implement strategies that optimize the business’s use of that data.
Data archiving. When data is no longer needed for business purposes, but must be maintained for legal purposes, the data needs to be removed from the operational database but stored in such a way that it remains accessible for e-discovery and legal requirements. This is database archiving.
Enterprise resource planning (ERP). Enterprise Resource Planning (ERP) software packages place additional burdens on the DBA. Most ERP applications (SAP, Peoplesoft, etc.) use databases differently than homegrown applications, requiring DBAs to know how the ERP applications impact the business and how the databases used by those packages differ from traditional relational databases.
Web-specific technology expertise. For e-businesses, DBAs are required to have knowledge of Internet and Web technologies to enable databases to participate in Web-based applications. Examples of this type of technology include HTTP, FTP, XML, CGI, Java, TCP/IP, Web servers, firewalls, and SSL. Other technologies that fall into this category include database gateways and APIs.
Storage management techniques. The data stored in every database resides on disk somewhere (unless, perhaps, it is stored using an in-memory DBMS). The DBA must understand the storage hardware and software available for use, and how it interacts with the DBMS being used. As such, DBAs must be able to allocate, monitor, and manage the storage used by databases.
The bottom line is that the DBA must be a well-rounded staff member capable of understanding multiple facets of the business and technology. The DBMS is at the center of today’s IT organization— so as the one tasked with keeping the DBMS performing as desired, the DBA will be involved in most IT initiatives. Did I forget anything?