Thursday, March 30, 2006
Responsibilities of a Development DBA
Recently, I was asked to define the responsibilities of a Development DBA.
The following is the list of items that came to my mind. Feel free to add items you feel appropriate in the comments section.
1. Database design and implementation
Design and implement the database. Normalize and denormalize the database as necessary. This includes the creation of tables, views , common functions and procedures
2. Design, Implement and monitor back end jobs
Define standard notification for all back end jobs. Take corrective action when a job fails. Schedule the job to execute on non-peek hours. Verify job level and step level status on a daily basis.
3. Do database tuning and performance monitoring
Monitor the performance of individual programs and based on the performance results tune/reorganize the tables and indexes.
4. Optimize badly performing queries and procedures
Optimize badly performing programs by modifying/rewriting the programs in statement level.
5. Do application tuning and performance monitoring
Assess the performance of the applications and the load and impact on the database caused by applications. Provide suggestions to concerned developers where database access mechanisms can be optimized.
6. Perform troubleshooting on DB related issues
Help developers to troubleshoot database related issues. Provide trace files containing database access details for a specific database/program/time interval to the developers when required.
Make the developers aware about the known issues/bugs in the software and the limitations of the version/service pack in use.
7. Participate in design and architecture of new CRs.
Participate/review the design and architecture of the CRs to ensure that the design/architecture is developed with scalability and performance considerations.
8. Code Review
Review procedures/functions. Evaluate compliance with the defined standards and best practices. Use available tools best practices compliance.
9. Training
Conduct training on database related topics. Introduce new versions of SQL server. Distribute study materials. Assist the developers to start working with the new version.
10. Extend support to developers for writing complex programs
Help the developers write complex queries in an optimized manner. Share common patterns/tips and tricks used in the industry to solve similar problems.
11. Setup and maintain standards and best practices.
Define and enforce organization wide standards and best practices. Update the standards and practices based on the feedback and review experience.
12. Recommend Architectural changes.
Recommend changes to the current design/architecture which can give a database wide performance boost.
13. Monitor database activities
Monitor the activities on the servers and take corrective action when required. Look for issues like connection creeping, blocks, deadlocks, slow response etc..
14. Perform database health checking
Perform regular health checking activities on the database to ensure integrity, stability and robustness of the database. Check fragmentation levels and statistics concurrency. Perform re-indexing and update statistics when required.
15. Establish and maintain backup policies
Verify the backup process and occasionally test the backup. This includes the database backup and script backup.
16. Define and Enforce security policies
Define the minimum security credentials each role/user requires and enforce the defined security policy.
17. Capacity Planning
Monitor the space allocation and free space on the development and QA environments. Shrink the database files on a regular basis to recover free space. Configure/Recommend auto grow settings for each database based on its growth rate.
18. Disaster Recovery Planning.
Document and test a DRP plan for the development and QA environments. This should be in sync with the DR plan for the production environment.
19. Installation and configuration of SQL Server Databases.
Setup SQL server environments for development, QA and performance testing. Configure the servers and databases based on the requirement.
20. Evaluate, test and apply service packs
Test the service packs released for SQL server before applying them in a production environment to make sure they don't break the existing programs and the service pack is bug free.
21. Evaluate, test and upgrade to new versions
Evaluate the new versions of SQL server. Perform performance/scalability/availability/programmability comparisons with the existing version. Use the new versions to run in-house projects for a comfortable time period and recommend for upgrading to the new version if the results are positive.
22. Evaluate, test and recommend third party components.
Evaluate the components available in market that can boost the performance, secure the server, ensure high availability, prevent data loss, perform fast backups etc.., and if essential, recommend using them.
23. Coordinating and interfacing with developers, support persons, outside vendors, end users, other department team members, and service providers to resolve issues.
Interact with other departments and teams to resolve DB related issues. If required get assistance from the product support team.
The following is the list of items that came to my mind. Feel free to add items you feel appropriate in the comments section.
1. Database design and implementation
Design and implement the database. Normalize and denormalize the database as necessary. This includes the creation of tables, views , common functions and procedures
2. Design, Implement and monitor back end jobs
Define standard notification for all back end jobs. Take corrective action when a job fails. Schedule the job to execute on non-peek hours. Verify job level and step level status on a daily basis.
3. Do database tuning and performance monitoring
Monitor the performance of individual programs and based on the performance results tune/reorganize the tables and indexes.
4. Optimize badly performing queries and procedures
Optimize badly performing programs by modifying/rewriting the programs in statement level.
5. Do application tuning and performance monitoring
Assess the performance of the applications and the load and impact on the database caused by applications. Provide suggestions to concerned developers where database access mechanisms can be optimized.
6. Perform troubleshooting on DB related issues
Help developers to troubleshoot database related issues. Provide trace files containing database access details for a specific database/program/time interval to the developers when required.
Make the developers aware about the known issues/bugs in the software and the limitations of the version/service pack in use.
7. Participate in design and architecture of new CRs.
Participate/review the design and architecture of the CRs to ensure that the design/architecture is developed with scalability and performance considerations.
8. Code Review
Review procedures/functions. Evaluate compliance with the defined standards and best practices. Use available tools best practices compliance.
9. Training
Conduct training on database related topics. Introduce new versions of SQL server. Distribute study materials. Assist the developers to start working with the new version.
10. Extend support to developers for writing complex programs
Help the developers write complex queries in an optimized manner. Share common patterns/tips and tricks used in the industry to solve similar problems.
11. Setup and maintain standards and best practices.
Define and enforce organization wide standards and best practices. Update the standards and practices based on the feedback and review experience.
12. Recommend Architectural changes.
Recommend changes to the current design/architecture which can give a database wide performance boost.
13. Monitor database activities
Monitor the activities on the servers and take corrective action when required. Look for issues like connection creeping, blocks, deadlocks, slow response etc..
14. Perform database health checking
Perform regular health checking activities on the database to ensure integrity, stability and robustness of the database. Check fragmentation levels and statistics concurrency. Perform re-indexing and update statistics when required.
15. Establish and maintain backup policies
Verify the backup process and occasionally test the backup. This includes the database backup and script backup.
16. Define and Enforce security policies
Define the minimum security credentials each role/user requires and enforce the defined security policy.
17. Capacity Planning
Monitor the space allocation and free space on the development and QA environments. Shrink the database files on a regular basis to recover free space. Configure/Recommend auto grow settings for each database based on its growth rate.
18. Disaster Recovery Planning.
Document and test a DRP plan for the development and QA environments. This should be in sync with the DR plan for the production environment.
19. Installation and configuration of SQL Server Databases.
Setup SQL server environments for development, QA and performance testing. Configure the servers and databases based on the requirement.
20. Evaluate, test and apply service packs
Test the service packs released for SQL server before applying them in a production environment to make sure they don't break the existing programs and the service pack is bug free.
21. Evaluate, test and upgrade to new versions
Evaluate the new versions of SQL server. Perform performance/scalability/availability/programmability comparisons with the existing version. Use the new versions to run in-house projects for a comfortable time period and recommend for upgrading to the new version if the results are positive.
22. Evaluate, test and recommend third party components.
Evaluate the components available in market that can boost the performance, secure the server, ensure high availability, prevent data loss, perform fast backups etc.., and if essential, recommend using them.
23. Coordinating and interfacing with developers, support persons, outside vendors, end users, other department team members, and service providers to resolve issues.
Interact with other departments and teams to resolve DB related issues. If required get assistance from the product support team.
