on March 29, 2016
You’re just getting started as a SQL Server Database Administrator – or you’re trying to get there.
Here’s a learning plan and links to free articles and scripts that will equip you to tackle the three most critical skills to for DBAs.
Skill 1: Design Backup Strategies that Meet RPO and RTO
Secret: the DBA is always responsible for preventing data loss, even if it’s not part of their job description.
1.1 Establish RPO and RTO
Learn Recovery Point Objective (RPO) and Recovery Time Objectives (RTO) and establish them for every environment. Focus on critical production databases first.
- https://en.wikipedia.org/wiki/Recovery_point_objective
- https://en.wikipedia.org/wiki/Recovery_time_objective
Your mission:
- Get RPO and RTO signed off by business owners
- Notify business owners in writing if you can’t meet the objectives (this is not a hallway conversation)
1.2 Learn about Write Ahead Logging and SQL Server Recovery Models
For me, learning about Write Ahead Logging made backups and recovery models make much more sense to me, so I recommend doing that first.
- I explain write ahead logging and talk about how I learned this back when I was a Junior DBA in WAL: The concept that makes recovery models & backups make sense (Dear SQL DBA Episode 32)
- Microsoft reference on Recovery Models: https://msdn.microsoft.com/en-us/library/ms189275.aspx
1.3 Learn Backup Under the Simple Recovery Model
- Microsoft reference on this topic: https://technet.microsoft.com/en-us/library/ms191164.aspx
1.4 Learn Transaction Log Backups and Point-in-Time Restore
Work through the entire series “Stairway to Transaction Log Management” (Tony Davis and Gail Shaw) – registration required
- http://www.sqlservercentral.com/stairway/73776/
- Practice restores in Level 5 (includes sample scripts)
Skill 2: Document and Improve SQL Server Configuration
Secret: the DBA is responsible for database and server config, even if someone else made the decision.
2.1 Document Your Configuration in Detail
Free scripts to document SQL Server configuration: SQLSkills’ free diagnostic scripts, by Glenn Berry
- http://www.sqlskills.com/blogs/glenn/category/dmv-queries/
- Customized scripts for major SQL Server versions
- Excel spreadsheet for each script to save off information
- Tip: Run each query one by one and paste in the results
2.2 Identify the Configuration Changes You’d Like to Research First
Free scripts to prioritize configuration changes: Brent Ozar Unlimited’s free sp_Blitz script (registration required), by Brent Ozar.
- https://www.brentozar.com/blitz/
- Prioritized list of configuration by risk
- URLs for each diagnosis with more information
Reminder: if you haven’t completed establishing RPO / RTO and changing your backup jobs and restore practices to meet those, do those first before any other configuration changes.
Skill 3: Plan Successful Changes
Secret: Changes you make are going to break things. Change Management is your superpower to handle this without panic.
3.1 Change Request Basic Template for DBAs
3.2 Change Management and Source Control (Grant Fritchey)
Photo credit: Negative Space via Unsplash.com