Monday, March 10, 2008

Taking a Stab at a Shared Industry Definition for a "DBA Workbook"

Whenever I come across shops where business users, application support groups or infrastructure teams complain about the DBAs not meeting their expectations, the first question I ask is “OK, so what’s your DBA really supposed to do?”. And I’m amazed by the range of answers I get. It seems like there is quite a bit of vagueness regarding the DBA role. Just recently I blogged about measuring IT admin productivity, but in the case of DBAs, it appears there is not even a consistent set of expectations regarding what to expect from them, let alone measure their productivity.

Alright first things first! Has anyone come across a good DBA Workbook? I’m not even talking about a cookbook with step-by-step instructions on how to do different tasks, but just a preliminary workbook that documents exactly what the heck they are supposed to do.

So, what’s goes into an average DBA’s work-day? As I work at multiple companies, I see DBAs doing things all across the gamut - from ensuring backups are proper to tuning SQL statements to even writing stored procedures! It seems DBAs in many environments either gravitate towards what their predecessors used to do (and try to meet expectations that were set way before their time) or end up catering to whatever the loudest squeaky voice tends to ask. Sometimes I see DBAs being grouped into teams of specialists – like Operations DBAs (aka Systems DBAs), Application DBAs, Development DBAs and Engineering DBAs. I’m myself partial to having the DBAs segregated into not more than 3 groups: say, the Operations DBA team, a Development DBAteam and optionally, an Application DBA team (especially if the environment has large complex ERP/CRM type implementations). I don’t see the need for additional silos such as Engineering DBAs or Build DBAs; instead I view them as more of an area of focus for personnel within existing teams. For instance, each DBA team needs to have Tier 3 personnel that are responsible for Engineering-type DBA work like defining standardized database configurations. Anything more than 3 core teams tends to prevent economies of scale and can create workload imbalance in my experience (at any point in time, you may find one DBA group to be super-busy while another is relatively sitting idle or working on low priority activities). Similarly, creating too many silos can also lead to islands of institutional knowledge wherein none of the DBAs have a comprehensive understanding of the entire IT stack – from the database to the OS and storage, and all the way back to the application. A siloed lop-sided view can prevent effective root cause analysis during problem management.

It is also not uncommon to find sub-groups within the 3 main DBA groups based on DBMS platform (Oracle team versus SQL Server team, etc.) and OS platforms (UNIX DBAs versus Windows DBAs). The former sub-category tends to be more prevalent than the latter. However I’m increasing finding DBA managers interested in building cross-platform awareness. As leading DBMS platforms begin to display similar capabilities, databases are increasingly getting commoditized. Also with different 3rd party applications requiring multiple DBMS platforms as well as changing business requirements (say, the occasional M&A) casts more pressure on a DBA team to manage heterogeneous platforms. Furthermore, decision automation platforms like Data Palette help create an “abstraction layer” across physical DBMSs allowing DBAs skilled in one platform to be productive on others. So in the future, we may be seeing even less of a need for platform-based sub-categories.

Alright, so here’s a stab at a preliminary DBA workbook. Please feel free to adopt it as you see fit (since clearly there is no such thing as a “single size fits all” here.) This workbook is not supposed to be a precise guide for what DBAs in your specific organization should be working on, rather it’s meant to serve as a starting point template for you to customize per your business requirements. But do bear in mind that if you find the need to reinvent it completely, then perhaps you don’t really need a DBA, it’s some other role you are seeking to fill. So in other words, consider a 10-20% deviation from this definition as reasonable.

All you DBAs and DBA Managers out there – I would love to get your input on what you feel is right or missing here. Hopefully this can develop into a common definition of what should constitute a legitimate scope of work for DBAs. Such a definition sets expectations both within and outside the DBA team, and fosters higher collaboration. It should help managers analyze gaps in DBA scope of work in their own organizations, as well as identify gaps in skill-sets for future training and hiring. And the best part is that such a workbook can be linked to an SLA (that outlines the service levels the DBAs are responsible for achieving), and last but not least, be the pre-cursor for a more detailed cookbook comprising specific run-books for recurring (and some non-recurring yet complex) operations. The goal is to reduce surprises for other groups and avoid DBAs having to hear users and customers say things like “oh, I expected our DBAs to do more. In my previous company, they did so much more…”.

Also, if you ever venture into a DBA outsouring/co-sourcing arrangement with a 3rd party vendor, this can serve to delineate what you expect the vendor's DBAs to do versus your own team.

Operations/Systems DBA Workbook
- Database backups
- Database recovery
- Implementing robust change control and configuration management policies
- Capacity planning & system-level architecture
- OS/Storage/DB configuration and optimization
- Database security checks and audits, including compliance-related reporting & controls
- General database health checks
- Database monitoring
- Working with DBMS support (Oracle Support, Microsoft Support, etc.)
- Defining and maintaining quantitative SLAs and helping enhance current service levels
- Physical data model/architecture, DDL generation and maintenance
- DB server migrations and decommissioning
- Database creation and configuration
- Applying patches
- Upgrading the databases from version X to version X+Y
- Maintenance (proactive, reactive and automated); both scheduled and unscheduled
- Database refreshes
- Log-file review
- Trouble-shooting and repairs
- Reporting on state of databases
- Database tuning (proactive and reactive)
- Maintaining specialized environments such as log shipping, replication, cluster-based instances, etc.
- SOP (Std. Operating Procedure) definition & automation
- DBMS and related tools license management
- Documentation related to all Systems DBA areas
- Management of database-related tools (monitoring systems, tool repositories, etc.)
- Automated measurement of quantitative SLAs & any deviations thereof
- Development & utilization of detailed triage processes
- Detailed user workload analysis & segregation
- Infrastructure advise and planning
- Disaster recovery/failover services

Application DBA Workbook
- Setting up application/reporting environment
- Loading flatfile data into the database (script setup, automation & troubleshooting)
- Database cloning
- Application back-end process design and management
- Carry out application-specific trouble-shooting and reactive repairs
- Application configuration and setup
- New application implementation and upgrades
- Creating and implementing application security policy recommendations, including compliance-related reporting & controls
- Data management and manipulation. For example, ensuring data cleanliness, validating scrubbing rules, addressing batch job failures due to bad data (referential integrity violations, etc.)
- Perform application workflow analysis, characterization and segregation to reduce workflow and data-related failures
- Define triage and escalation procedures for each application failure such that appropriate parties can take ownership of specific issues during problem situations
- Liaison with 3rd party application vendors (e.g., Oracle/PeopleSoft Support) for support and trouble-shooting
- Work with in-house Development DBA and Development team in longer-term proactive application repair and re-architecture
- Application DBA related documentation and cross-training
- Assist QA in building and implementing test plans to validate and test application bug fixes, patches and upgrades
- Evaluate recurring/common application failures and define application “Repair SOPs” so that similar application failures get handled in a consistent manner.
- Strengthen application architecture for better performance and scalability and reduced failures - Working with the Systems DBAs on application/database integration (example: integrating two databases, etc.)
- Working with the Systems DBAs on configuring the database to optimally accommodate each application (example: providing consultative input on tasks such as data sizing, capacity planning, custom setup of memory structures and disk layouts, etc.)

Development DBA Workbook
- Specialized database SQL tuning
- Documentation related to all pre-production DBA areas and cross-training
- Participate in application and database design reviews
- New development projects implementation
- Logical data modeling and architecture
- Logical to physical design conversion
- SQL tuning
- Developing and deploying DDL/DML coding standards
- Coding (or recoding) of specific database modules for enhanced database/application performance
- Database code (DDL/DML) version control
- Scripting and testing schema builds and new rollouts
- DBMS software installation, configuration and management for development database environments as per standards set in Production (by Systems DBAs)
- Data cloning within development environments
- Training Developers on writing optimal SQL code
- General liaison between Systems DBAs and Developers
- General liaison between Systems DBAs and Application DBAs
- Change-control guidelines and provisions for new project implementations
- Assist QA in building and implementing test plans to validate and test pre-production DB bug fixes, patches and upgrades; as well as assist in stress/volume testing.

No comments: