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 database administrator duties and divide them into two categories: those that support development work and those that support the production systems. Of course, many DBAs support both, but the job duties differ.
Development DBA
Development DBAs work 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 business 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.
Supporting database development begins with data modeling and database design. 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. Data modeling tools are available to help DBAs model data and build relational databases.
Once the test database is created, the development DBA will assist 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 (databases) and show the differences is helpful to determine the effectiveness of a program execution. And, of course, the ability to refresh the data back to its state before the program ran, so it can be tested again.
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.
Additionally, 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.
Finally, development DBAs need to work closely with application developers every step of the way, including integrating and automating DBA tasks as part of the DevOps pipeline.
Production DBA
By contrast, the production DBA supports working 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 for managing databases and applications: Performance, Availability, 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 must be managed across the IT infrastructure. Next, availability refers to ensuring that the DBMS and its resources are up and running as needed by the applications and their users. And backup and recovery is the process of taking appropriate database backups of the appropriate type at the proper time to ensure recoverability.
When the production DBA focuses on PAR, applications will be performing according to the service level agreements, databases will be administered appropriately assuring optimal design and good organization, and data will be sufficiently backed up such that it can be recovered in the event of an error or downtime. Well, this will be the effect if your DBAs are diligent, well trained, and supported by the proper tools.
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.
Supporting Development and Production DBA Roles
Both development and production database administration are required to support database applications. However, it is not usually necessary to have different DBA staffs to perform the different roles. Indeed, 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.