Minimal knowledge requirements for SQL Server DBAs (Part 1)
next blog entry
I am currently working with a client, one of those large multinational corporations, where database related duties are spread across several very specialized teams. They have a team that does solely SQL Server installations and patches, another team that only does backups, another team that executes scripts in production, and the list goes on and on. The company also likes to hire people right out of college with no previous work experience, so it's not uncommon to have a "DBA" that's been working in his position for seven years, knows all obscure options of SQL backups but doesn't know how to start perfmon or what to do with a profiler trace. It's also not surprising that there are a lot of gaps in the database administration area that aren't being owned by any of the teams and the client really feel the pain, particularly when it comes to database performance troubleshooting (that's how we got engaged in the first place).
Recently there's been a change in management and the new boss decided that it was time to train up some "database generalists" or in other words production DBAs. I was asked to put together a list of knowledge requirements that should be expected from a person applying for this position. I am sure I've missed many points but I believe it's a good enough checklist for a DBA with 1-2 years of experience.
Physical database architecture
- Data files
- Transaction logs (physical log, virtual log)
- Filegroups
- File growth, shrinking
System databases
- What are master, model, msdb, tempdb, resource
Configurations and performance considerations
- Placement of data, log, tempdb files
- Mount points
- Enabling instant file initialization
- Lock pages in memory
- PAE, 3GB
- AWE
- Min, max server memory
- Max degree of parallelism
- Errorlogs
- Network configuration
- SQL Server services
- Service accounts
Physical data storage
- Pages
- Extents
- Raid 5, 1, 10
Data modeling
- normal forms
- denormalization
- primary key, foreign key, unique constraints
- check constraints, default constraints
- data types
Indexes
- Clustered
- Non-clustered
- How to choose where to place them
- Benefits and downsides of indexes
- Fragmentation
- Fillfactor
