Friday, January 16, 2009

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
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

No comments: