Welcome to the first episode in my training to become a DBA. I suppose this could be called Episode 2 since I wrote my first post two weeks ago, but let’s just consider that a prequel sans all the retconning. Last time, I listed the basic concepts I’d delved into so far, including starting to take a look at backups.
There’s Even a T-Shirt
Here at Dallas DBAs, we have a t-shirt that (not including the logo) has a total of seven words printed on it. These are common phrases a DBA might (or should) say throughout the course of their career.
Using “NO” a lot sounds quite a bit like parenting, so I think I can figure out appropriate times to use that statement. And while at the SQL Saturday pre-con last week, Kevin (t) kept a running tally of how many times we heard the words, “It depends.” I’m sure it makes a fun drinking game…not that I’m into such things.
Then there’s the phrase, “You back that up?” Coming into the DBA field, there’s a part of me that always quietly asked, “Why wouldn’t someone back up their stuff?” The more I delve into things the more I realize that database backups are far more complex than simply making sure you copy everything to Dropbox or an external drive—sort of.
Ok, so I knew there was more to it than that even before my first day, but I didn’t understand the vast differences in the types of backups and the myriad methods that could be used to ensure proper backups are happening.
The First Rule of Backups
The First Rule of Backups is the direct inverse of the First Rule of Fight Club—you’ve GOT to talk about backups. Just a few weeks into this journey I’m somewhat flabbergasted by the number of stories I’ve heard from within the industry about companies who simply don’t have a valid restore plan, or worse yet, don’t even bother keeping periodic backups of their data.
Here’s one of the few pieces of advice I feel like I can give my fellow beginner DBAs:
If you begin working with a company or client that does not have a backup and restore plan and isn’t all that interested in putting one together, do not bother going to work for them.
One of the primary and most important functions of a DBA is to protect the data. Think about it. If there’s no data, then there’s no reason to pay you money to administrate it. And if you as an administrator can’t protect the data via backups you either a) need to consider a completely different career, or b) go work somewhere that allows you to properly do your job.
Part of your job is going to be speaking with decision makers to discover how much data loss (Recovery Point Objective) and how much downtime (Recovery Time Objective) is acceptable. Initially, they’ll probably say “absolutely no data loss” and “absolutely no downtime.”
As you will learn, that’s pretty much impossible to achieve—or at least guarantee—and prohibitively expensive to attempt. Once you talk them out of the trees, you can work together to come up with a reasonable RPO and RTO, which will then allow you to formulate a valid RESTORE plan.
You Need a RESTORE Strategy
I hurt my brain a LOT last week watching Paul Randal’s (b|t) PluralSight course, SQL Server: Understanding and Performing Backups. It was a really great follow-up in many ways to the stuff that Kevin has been teaching me, and really reinforced backup concepts and practices for me before eventually getting too far over my head.
But the line that stuck with me more than any other was this:
“Never plan a backup strategy. Plan a restore strategy.”
Boom! That’s a truth you need to stick in your DBA toolbox and keep forever. So then how do you know how to create a restore strategy?
Well, it depends.
- First, you need to figure out the RPO and RTO before you can come up with the plan to achieve them. Once you have those figures in hand then you can begin putting together a strategy for meeting those policies.
- Second, as a newbie DBA, you’ve got to get a handle on SQL Server Recovery Models—at the very least understand the major differences between FULL and SIMPLE and how those differences in backup capability will impact your restore strategy. I won’t attempt to go into explaining Recovery Models here because there’s a ton of great info out there to guide you.
- Third, do everything you can to understand the differences between the types of backups—Full, Differential, and Transaction (T-Log)—and how and when they should be used. This was something I didn’t fully grasp at the beginning, but once you study the Log Backup Chain and how the different types of backups affect that chain, you’ll be well on your way to understanding how to build a restore strategy.
- Fourth, don’t try to learn too much too fast. There’s a lot to absorb here, and if you’re anything like me it’s easy to attempt to dive in too deeply and start learning stuff you don’t need to know yet. The beautiful thing about this industry is that—unless you’ve been thrown to the wolves—you’re likely working under one or more Senior DBAs, which means you don’t need to know everything yet. You won’t need to know how to put together a recovery strategy on your own for several years to come. Be patient, you’ll get there.
Oh, so much more, and lots of it is pretty fun stuff. Just today I’ve spent a good amount of time learning all about DBCC CHECKDB, and most of the last few days have been learning about the basics of indexing and SQL Server security basics. (Spoiler alert: SQL Server’s built in security capabilities are pretty basic.)
Come back next time for Episode 2: Attack of the Corruption. In the meantime, I’d love it if you followed me on Twitter at @SQLandMTB.