There are many different ways to look at database administration. It can be done by task, by discipline, by DBMS, by server, and so on. But one useful way to look at database administration is in terms of the type of support being delivered to applications. You can paint a broad brush stroke across the duties of the DBA and divide them into two categories: those that support development work and those that support the production systems.
Development DBAs
The development DBA works to support the application development lifecycle. In a development environment, there is no immediate impact to business because the application/database is not yet operational. The impact to business is eventual — in terms of the quality of the applications that are being built by the developers and supported by the development DBA.
The development DBA focuses on tasks related to building an effective, usable database environment to support the creation and maintenance of applications. As programs and systems are being built, the development DBA is there to lend assistance and support – which includes building and maintaining the proper database structures required by applications.
Development DBAs need to be skilled in the process of data modeling and normalization to ensure that databases are designed to promote data integrity. Accomplishing this requires the ability to interview end users and to turn the results of such interviews into a logical data model and E/R diagrams. Furthermore, the development DBA must be able to translate the logical data model into a physical database implementation.
Once the test database is created, the development DBA assists programmers in building and editing test data. Test data generation, editing, and loading are required processes in order to ensure that appropriate data is available to test applications as they are built. These are responsibilities shared by the development DBA with the application development team.
As programs are tested, data in the database will change. Programmers need tools to be able to determine what changed as a result of running their programs. The development DBA needs to be able to provide tools to facilitate database testing and data refresh. For example, the ability to compare the contents of two tables and show the differences is helpful to determine the effectiveness of a program execution.
The development DBA needs to work with the application team to ensure that proper methods are used to embed data access and modification logic into application programs. This includes issues such as using the appropriate drivers, JDBC/ODBC implementation, coding proper connections, and so on.
Working with the application development team to ensure that appropriate and efficient SQL is being coded and tested is a big responsibility of the development DBA team. Although production DBAs must constantly monitor and tune SQL (as well as other aspects of the database and applications), it will be more effective if the SQL is built for performance during the development phase, instead of retrofitted later in production.
Finally, the development DBA must work with the application team to create and maintain effective database-coupled application logic – stored procedures, triggers, and user-defined functions (UDFs). These are programs that are under the control of the DBMS. The development DBA will help to build, test, and maintain stored procedures, triggers, and UDFs. Successful implementation of these database code objects requires both programmer and DBA skills. Some of the issues involved in successfully supporting these objects include source code management (check-in/check-out), versioning, impact analysis, and code testing.
Production DBA
By contrast, the production DBA supports completed applications as they run your business operations. Production DBA work focuses on assuring availability, optimizing efficiency, and promoting usability. Because once the database applications are operational, the key task becomes making sure they stay that way. It all boils down to keeping databases running up to PAR, where PAR has two meanings. The first meaning for par is like it is used in golf, implying a standard or norm. But PAR can also be an acronym that defines the three primary DBA responsibilities they for managing databases and applications: Performance, Administration, and Recovery.
Database performance management is the optimization of resource usage to increase throughput and minimize contention, enabling the largest possible workload to be processed. Of course, database performance cannot be achieved in a vacuum. Performance needs to be managed across the IT infrastructure. Administration refers to the day-to-day tasks of keeping databases up and running including change management, reviewing database structures, security and authorization, and like tasks. And backup and recoveryis the process of taking appropriate database backups of the appropriate type at the proper time to ensure recoverability.
By focusing on PAR, the production DBA helps to assure that applications perform according to the service level agreements, databases are administered properly, and data is sufficiently backed up such that it can be recovered in the event of an error or downtime.
Finally, the production DBA needs to be concerned with security and compliance efforts. Production data needs to be appropriately secured such that only the required users have access to it. And the data needs to be in compliance with governmental and industry regulations. Of course, there is a security and compliance aspect to test data, but it is much more of a production DBA responsibility.
Both Develoment and Production Database Administration is Needed
Both development and production database administration are required to support database applications. It is not necessary to have different DBA staffs to perform the different roles. Indeed it might not even be desirable because intimate knowledge of how a database application was developed can make it easier to support that application once it becomes operational in the production world. But the bottom line is this: you will need to define, plan for and staff both development and production DBA roles in order to create useful database applications.