A "Real" DBA

A friend asked recently, "Would you be interested/able to show me some of the skills I'd need to be a REAL DBA?" He'd been working in a mix of databases for 10 years, and was comfortable with application-level aspects, such as tables, stored procedures, triggers, and views, but wanted some coaching on lower-level aspects, such as Backup/Recovery and Tuning. Mike, this one's for you!

Some shops I've worked in make a distinction between "Application DBA" and "Infrastructure DBA", where Application DBAs manage schemas and develop applications, and Infrastructure DBAs install the software, perform backups, and keep things running. Other shops I've been in have the "DBA" doing all of the above. (My thoughts on division of responsibilities is a topic for another post). I contend that both are "Real DBAs", each with different specialties, so I'll just use "DBA" from here on out.

It is helpful for a DBA to be familiar with both the application-level and infrastructure-level concepts, regardless of the database platform. If your organization splits responsibilities, you may know more about one or the other in practice, but should at least understand the concepts.

The Application DBA can write more effective SQL and procedures if they have a thorough understanding of how the infrastructure works.

• How does the database handle read consistency?
• How does it handle transactional integrity?
• What happens if I lose my connection?
• How is it backed up, so I know how to get data back when it, inevitably, gets lost.
• How does it store data on disk, so I can organize my schema objects appropriately?

The Infrastructure DBA can perform their job more effectively by understanding the applications that use "their" database.

• Are some tables more critical than others?
• What load is expected from the application, including over time?
• Does the application rely on version-specific features?
• A knowledge of different SQL techniques can be useful when developers come to you for help in tuning, since much tuning happens in the application and not a magical FAST=TRUE database setting.
• Be able to explain to developers why bind variables are important to both security and performance.
  

When it comes to many of the questions above, each database is different. Oracle handles read consistency differently than MySQL, which handles it differently than SQL Server. There are application design implications here, and different performance tuning approaches for each. The DBA needs to understand how the database they service manages data internally, to some degree. Reading the documentation is often the best place to start.

No DBA can know everything, so an effective DBA will have resources in mind where he can get questions answered as needed. Remain teachable and open to learning something new every day.

• Documentation is the obvious place to start (did I mention that already? Yes. Good. Can't mention it enough.)
• Online support systems and forums from your database vendor.
• Select other Internet forums that you TRUST. They are not all good, and many are the blind leading the blind.
• Know local resources you can go to... peers, mentors, user groups.

Down to more technical details, the DBA MUST, absolutely, know backup and recovery strategies for their chosen database. Each product has different methods and semantics, so learn what is appropriate. There may be multiple strategies (hot backups vs. cold backups, vs. exports - which are not really a backup, but that's another post). Understand how your database backup relates to your operating system backup. (For example, that O/S backup of /oradata, in and of itself, is utterly useless if your database is running at the time.)

Always remember, an untested backup is no backup at all. If you are not confident in your ability to restore a backup, you might as well not be taking it. Know how to restore the entire database, as well as subsets of the data... individual data files, individual tables, individual tablespaces, point-in-time recovery.

Performance Tuning is an acquired skill, learned mostly through experience, often with bumps and bruises along the way. In Oracle, at least, there are many tools available. Experiment with each of the tools available, learn their strengths. Use these tools to measure performance in good times and bad, so you can tell the difference. In a performance "crisis" you will likely be facing a situation unlike others you have solved in the past. Be prepared to do critical analysis on the fly. If you are making a change to fix a performance problem, have evidence to support why THIS change is the right choice and not the first parameter tweak that came to mind.

Know how to read your system's log files. Know how to generate diagnostic traces, if your database-of-choice supports it. Practice (in a test database) if you have that luxury, by setting up situations you expect to be a problem, then observing the messages that show up in the logs.

Practice installing the database, especially if you inherited a system already up and running. Perform regular patches. Pay attention to the files and directories that are created and know their purpose. Know what processes are expected to be running and their typical load. To beat the proverbial dead horse, the documentation for your system may be very valuable here.

The most important non-technical skill for the DBA may be communication. Cultivate good working relationships with the development team, and with the system administrators (if that doesn't already include you). Present yourself as an accessible resource, rather than the surly gatekeeper, and you will find a much smoother experience in implementing changes. At the same time, the "surly gatekeeper" hat is sometimes important and necessary - know when it is appropriate in your organization.

As long as this post has been, I'm sure I missed several important areas that could be added. As they come to mind, look for more posts.