Automatic DBA

Over the years I’ve been a DBA on and off, depending on what my employer at the time needed. Some IT shops do not even have a DBA. They have either a developer who thinks they are qualified because they know SQL, or a network engineer who sat through a MCSE boot camp course and knows how to use Google. Neither of these are a skilled DBA. So what does it mean to be a DBA and why bother having the skill around?

I’m going to restrict my comments and analysis to the Microsoft platforms, SQL Server. I’ve worked with Oracle, Sybase, Ingress, Informix and a couple other now defunct platforms, but SQL Server has been my concentration for years and that is what I know best. Regardless, I think they would apply to any of the other platforms.

You can prowl the web and find all kinds of different DBA’s, with the appropriate description and caveats. So as not to get all tripped up on titles, I just simplify the list down to these three:

• Data Architect – This position is logical/theoretical in nature: They envision the future. They translate the strategic goals of the company into tactical plans that can be implemented by the DBA’s. They look into the future 3 to 5 years, driving the technological direction. They outline the flow of information company-wide and design the overall logical model(s).
• Production DBA – This position is technically focused on platform installation, maintenance, and health: They establish the infrastructure. They are the gatekeeper that ensures that the intellectual property of the company is safe, secure, and available. They work across servers and databases to monitor health and performance, tuning as appropriate. They work with the application DBA as necessary to help the business units achieve their goals.
• Application DBA – This position, in contrast, is business focused: They implement the features. Usually integrated with the development/support team to help manage the intricacies of an especially complex application. They work within the database boundaries to ensure that data integrity, query performance, and overall database functionality work for the business process and are appropriate for the business goals.

These, of course, are just my opinions. But, an organization with these three positions in place would cover almost all the needed territory. You could conceptualize this as:

Vision -> Infrastructure -> Implementation

We seem to do very well with the first two: I know quite a few people over in Database Services for my current company and they do a good job of supporting LITTERALLY THOUSANDS databases; the numbers are mind-numbing and GROWING. And the support I’ve received from this group is to be commended. The amount of abuse that my DBA (who I think is the model of a Production DBA) took and just kept chuggin’ along is astounding. His group does a great job on Vision and Infrastructure.

But what about Implementation? Who is ensuring the proper use of the database features and ensuring quality down at the level of the developers? When I was given the App DBA position in when I first arrived I tried to find others from which I could model my effort. When I asked Darrell about it, he just laughed at me. So I sat down with my manager at the time, Bob Johnson, and designed the position for our unit. The APP DBA would be responsible for:

1. Develops technical deployment plans, coordinates cross-team deployment efforts, and ensures smooth deployments.

   • Responsible for creation, maintenance, and process improvements for all releases deployment plans for all code bases.
   • Responsible for all coordination efforts with DBA Group, including completion of necessary processes, documentation and securing the necessary approvals.
   • Responsible for environment resource allocation and planning for all release efforts in all stages of SDLC.
   • Execute deployment plans in all environments for entire SQL code base, including supporting infrastructure (e.g., DTS, SSIS, replication, etc.).
   • Ensure all supporting documentation is updated to reflect release changes.
   • Ensure the integrity and health of environments during deployment.
   • Test and validate data script execution in all lower environments, track issues and manage correction for release.
   • Review, execute and capture results for production support data scripts.
   • Route all necessary documentation for SOX and resolution.

2. Work with all teams and members to ensure functional, reliable, and secure environments are available.

   • Perform periodic review and control of access in all environments and user groups.
   • Manage, schedule and execute data refreshes across all environments.
   • Ensure all SQL scripts follow approved guidelines and are managed within TFS.
   • Identify, design and implement environment management process improvements.
   • Execute performance monitoring in all environments when appropriate and initiate proactive issue resolution.
   • Ensure all data is secured and in compliance with audit requirements and provide support for audit activities.
   • Work with DBA Group to plan and implement server and network infrastructure changes to ensure maximum uptime and minimum interference.
   • Monitor database performance and investigate anomalies; document issues, causes, and possible resolutions. Communicate issues to management, product owner, and developers.

3. Conduct process identification, analysis, improvement and automation activities.

   • Develop, test and deploy tasks that assist in automation.
   • Validate structural changes and templates are design to support automation and process improvement.
   • Initiates code review process for code changes and complete agreed upon changes.
   • Document and perform analysis of current technical and business processes.
   • Design process and procedures that take into consideration future business needs and flexibility.
   • Design and develop automation taking into consideration the industry’s current technological and methodological directions.
   • Focus on adaptability and continuous integration to support Agile development.
   • Provide improved visibility of environmental management, deployment, and integration processes.
   • Investigate, determine, and implement the tools that are the best fit based on current and future business needs and constraints.

4. Set the direction of database interaction though consulting and collaboration to ensure the flexibility, usability and scalability of database resources.

   • Create/maintain the design view of the database system which encompasses the ERD, data dictionary, and SQL object definitions.
   • Create/maintain the data flow diagrams and process view which addresses the security, performance, and scalability of the system.
   • Participates in requirements definition. Capture/maintain the architectural aspects of the database via ERD’s, data flow diagrams, and replication/index plans.
   • Perform analysis of current technical and architectural needs.
   • Design systems that take into consideration future business needs and flexibility.
   • Design databases taking into consideration technology industry directions such as master data management and universal data modeling.
   • Design physical database models that support the business need and maximizes cost effectiveness.
   • Define the methods used for scripting the database, produce templates, and train developers on their use.

5. Provide database consultation and support for the development areas.

   • Collaborates with the development areas to ensure that database architecture strategies are clearly communicated and the hand-off to detail design occurs smoothly
   • Create prototypes and sample code/templates for use by the development areas
   • Produce blue prints and documentation related to the database standards and processes
   • Lead and provide coaching and mentoring in database code review
   • Provide coaching and mentoring to the development resources throughout the development process.

After we agreed to these items and started to put them in place, things got a lot smoother. So…Where are the App DBA’s? And what are they doing?