Database Skills Checklist

So what does a DBA / Database Administrator do? A skills checklist...

Database Administration Skills

All tasks can be done via Management studio UI & Wizards as well as TSQL,
Operating Systems checks can be performed (& automated) using VB Script

SQL Server installation

• SQL Server components (SSMS, Profiler, Query Analyzer, SSAS, SSRS, SSIS)
• SQL Server Versions (7,2000,2005,2008) & Editions (Dev, Standard, Enterprise)
• SQL Server requirements (hardware, OS, network protocols etc)
• Systems (Hardware, OS Installation, Networking, Security, Domains)
• Storage (Raid Levels, Disk types)

SQL Server Maintenance

• SQL Service Packs & Cummulative Updates
• OS Patches & Security Updates

Configuration

• Database Mail
• Linked Servers
• MDW (Management Data Warehouse)
• Resource Governor
•  ...

Backup/Restore

• Recovery models, backup / restore tools and SQL statements for backup / restore,
• Appropriate choice of recovery model
• Perform Backups at appropriate Intervals and Schedule them!
• Perform test Restores of the Backups!
• Take Backups offsite at regular intervals
• Have a documented DR (disaster recovery) plan

Monitoring (System Health)

• Activity - memory, blocking / locking, caching, disk usage, transactions, wait types, execution statistics
• Change Control - Database Objects
• Growth - dbs & tables
• Jobs - Import, Data Processing, System Health
• Query Performance

Security

• Logins - NT / SQL
• Authentication modes - Basic / NTLM / Kerberos
• Roles - Server / Database / Application
• Permissions - Server / Database / Schema / Object
• Keep it simple!

Database Maintenance

• Database Integrity
• Index Defragmentation (Reorganise or Rebuild depending on severity)
• File Defragmentation
• History Cleanup - Agent Job History / Backup Records

Troubleshooting

• Activity Monitor
• Performance Monitor
• SQL Profiler (trace)
• TSQL Commands - sp_who, sp_kill

High Availability Technologies

• Replication
• Clustering
• Mirroring
• Log Shipping

Performance Tuning

• Server Config
• Database Options
• Session Options
• TSQL Code - Execution Plans, table and index choices

Automate & Innovate

• Recurring tasks
• Reporting
• Record Statistics (disk usage etc)
• Predict trends
• Identify projects yourself

DBAs - see also '10 Ultimate Rules for Effective System Administration'


Database Development skills

Data Modeling / Database Design

• Logical Modeling
• Physical Modeling (3NF)
• Modeling tool (Visio, Erwin, can even generate Basic Diagrams in Management Studio)

Database Development

• TSQL (Transact SQL) for RDBMS Development
• CLR (Common Language Runtime) for .NET Integration
• SSIS (Integration Services) for ETL Development
• SSAS (Analysis Services) for Cube Development

Application Design

• Business logic location - Application?, Stored Procedures?, Triggers?, Scheduled Jobs?

Non Technical Essential Skills

Soft Skills

• Communication (Verbal & Written)
• Leadership
• Negotiation
• Project Management
• Time management

Document

• Configurations
• Decisions - Record the reasons behind anything important (design changes etc)
• Plans
• Projects