Which areas of a DBA's knowledge should a developer delve into?

Question

I must admit the question is quite broad, so I'll try narrowing it down a bit. In our company we are 3-4 developers and have some SQL Server based installations running at our customer's sites (database sizes up to 100GB, up to 100 concurrent users, intranet applications). Nobody of us has real good experience in running/maintaining/administrating (whatever) databases. The customers not even that much. It's working ok till now, but I can't tell for sure if it's because we doing everything right or if we just didn't hit areas/situations we aren't proficient in.

So, I'm looking for the essential things you need to know when running a database from a DBA's point of view. You know the hard facts and know what matters most in your day for day job.

In which subjects should I gather deeper knowledge, what should I've heard of and what can I care of not until I face it for the first time?

I'm aware of the question Software Engineers and DBAs, but it's not quite what I was looking for. There are also lots of books around, but I'd like to hear it from those with practical experience.

Answer 1

The two things I deal with every day.

1. Disaster recovery.

2. Performance tuning. (Both for individual queries, and for the dbms itself.)

Your disaster recovery plan needs to be

• scripted,
• tested, and
• practiced.

I'm using scripted in the sense of something an actor would follow, not something written in Python. It should tell everyone who needs to be involved exactly what to do. (And often, exactly what to say, too.)

Performance tuning for queries includes understanding keys, indexes, and normalization. (Often "tuning" problems are actually structural problems.)

Answer 2

I'm inclined to agree with @Catcall, database recovery should be top of the list. The implications of both backup and recovery options are typically the most poorly understood outside of a DBA team and the most likely to result in disaster.

• Ensure you have defined and agreed (by technical and non-technical management) RPO (Recovery Point Objective) and RTO (Recovery Time Objective) for all databases and systems.
• Document backup and recovery procedures to the extent that they could be followed by non-technical staff.
• Ensure all documentation is held in printed as well as electronic form, both on and offsite. A disaster recovery runbook stored on the local network won't be much use if the buildings on fire.
• Test every aspect of recovery procedures, often. Backups are irrelevant, it's restores that matter.

Next, from a database agnostic perspective, is an understanding of what a database server is built to do; provide Atomicity, Consistency, Isolation and Durability to your transactions and data. Commonly misunderstood, frequently the cause of performance problems and the primary source of data inconsistency.

For your chosen platform, get into the internals of how ACID compliance is implemented. Look for topics like what the transaction log does, what write-ahead-logging is, isolation levels and storage internals. Understanding key aspects of database internals makes other aspects of DBA work, performance tuning and resource management for example, much easier to grasp.