You need to poop. Maybe not right now, but in general, you need to breathe, eat, drink, sleep, and poop. If you can’t do any of those things, then the rest of your needs will take a back seat until you can accomplish the basics.
Psychology professor Abraham Maslow designed Maslow’s Hierarchy of Needs to illustrate how us meatbags prioritize our daily tasks. It’s usually visualized with a pyramid:
As a presenter, I find this pyramid really useful because I can’t do a good job of presenting if I’m thirsty or if I need to hit el baño. I’ve learned that before I start teaching database stuff, I need to hit the bathroom, grab a Clif Bar, and down a bottle of water. No, I don’t always have sex before I present. Not to say it hasn’t happened.
I hereby propose Ozar’s Hierarchy of Database Needs:
1. Backups: You can’t lose more data than the business is comfortable losing.
Note that I didn’t say you can’t lose data. You can, and you will, because safeguarding data is expensive. Not all data is created equal, and businesses are comfortable losing certain kinds of data.
For example, I’ve worked with businesses that chose not to back up their development servers at all because they believed source code control was their first line of defense for developers. They regularly refreshed development servers once a week from production. If they lost a development server halfway through the week, everyone’s work was checked into source code control anyway, and the server could be rebuilt from scratch fairly quickly.
At the other extreme, I’ve worked with DBAs who knew full well their mission-critical databases weren’t well-protected from disaster.
The real problem isn’t backups – it’s communication.
Being a truly exceptional DBA means that your manager, plus every level all the way up the chain, is comfortable with your (in)ability to restore every system in the shop. Your managers have a list in writing of all the company’s systems, a high-level overview of how frequently they’re backed up, how frequently the restores are tested, and how long a restore will take. If you haven’t written a list like that, stop and do it now. It’ll only take a few minutes, and it’ll pay off dramatically. This list will cover your ass, and this list will get you the hardware you need to do the right kinds of backups.
This list will also free you from trying to be a perfectionist. The business doesn’t need transaction log backups every 60 seconds on every server. Learn to match up your job duties with the company’s financial needs so you can focus on what really matters.
Month 1: Learning Backups
Here’s what you need to focus on in the first four weeks:
- Run sp_Blitz™ and find out if you have databases that aren’t being backed up at all.
- Learn the basics of backups from Jeremiah in this 30-minute video.
- Get the business’s RPO and RTO goals in writing. These don’t have to be communicated back to your end users, but your manager has to give you something to aim for.
- Design a high availability & disaster recovery plan to meet the business RPO and RTO goals using our 30-minute video explaining SQL Server’s HA/DR options.
- Learn how to do an emergency restore from Brad McGehee at SQLbits.
- Read the Books Online section on transaction log backups.
It seems like a lot of work, but if you spend a few hours per week on this, you can knock it out and move on to the next level of the pyramid. I can’t emphasize enough how important it is to get this level done right. Even though it seems like it can’t impact performance or capacity, it really does.
2. Security: Know who has access to the data.
This gets a little tricky with Active Directory groups because the Windows team can add & remove group members without the DBA knowing.
My favorite visual communication method for this is a grid of servers & databases down the left side, and a set of columns for permissions across the top:
The first time I present managers with this information, I dumb this grid down because it’s an overwhelming amount of information for readers to digest. I want them to get the big picture: “We need to reduce the number of people who can get the DBA fired.” After the first round of cleaning house, I add columns for the database backup and the physical database files, both of which are a serious security risk. If someone can take a SAN snapshot of an unencrypted database, they can waltz right out the door with a copy of it.
Note that I didn’t say, “You have to control who has access to the data.” Just as the business needs to decide the value of data, the business also needs to make security decisions. Police don’t make the law – they just enforce it. Likewise, the DBA needs to serve and protect the data.
Month 2: Learning Security
Get the book Securing SQL Server by Denny Cherry (also available on Kindle.) This book manages to bring a very dry topic to life and focuses on the real-world implications of security design. You don’t need to absorb the entire book, but start with the chapters on securing your backups, logins, and protecting from SQL injection.
3. Capacity: Know when you’re going to run out of space.
Every database in your datacenter is growing. All of them. They’re not all going to drain your storage dry tomorrow, but every single database has an expiration date when they’re going to run out of space. In consolidated environments with dozens, hundreds, or thousands of databases on the same server, this gets a lot harder to predict.
To make matters worse, growth rates aren’t stable. All it takes is one leftover BEGIN TRAN statement in someone’s SQL Server Management Studio and your transaction log files can grow out of control overnight. The most out-of-the-box fix I’ve ever heard for this was Dave Stein‘s excellent suggestion to leave a 10GB backup file on your log file drive, and to leave explicit instructions with your sysadmins. When the alerts come in that the log file drive is running out of space, delete that 10GB file and start taking emergency actions. No, it’s not the most enterprise-oriented solution, and yes you should work towards something better, but this is a good start for small businesses.
Before you build your own scripts to alert you when the SQL Servers run low on drive space, step back for a moment and ask who else in the company needs this same solution. Odds are your team needs it for the file servers, backup servers, mail servers, even the web and app servers. Rather than reinventing the wheel, suggest that the entire team adopt a simple monitoring system.
Month 3: Learning and Implementing Capacity Monitoring
Make a list of things that bit you from behind recently:
- Running out of space in the data or log files
- A transaction that gets started, but not committed
- Long-running Agent job
- Blocking/deadlocking queries
Set up a development server where you can reproduce those problems at will. Then get demo versions of the monitoring programs you’re interested in, and see how they react to the problems. Are you able to troubleshoot the underlying problems much faster? If so, which one is more pleasant to use?
4. Performance: Know your current bottlenecks and how to fix ‘em.
Once the basic infrastructure issues are addressed, you can finally start turning your focus toward real performance tuning. Most database professionals still use tools like Perfmon to check CPU percentages and looking at Average Disk Queue Length for drive issues, but there’s a much better place to get started: our free First Responder Kit. We give you a checklist and free scripts to attack performance problems as they’re happening, live.
I didn’t say you had to fix the bottlenecks – I just said you need to know how to fix them. The actual process of fixing them is a business decision: the business has to be willing to spend the money and time to make their server go faster. Your responsibility is just to have the answer when the business asks, “Why is the server slow, and how can we make it faster?”
Month 4: Learn Performance Troubleshooting
Go through the First Responder Kit and learn how to use these tools:
- Wait stats
- The First Responder Kit Checklist
This month, you’ll be building experience that will carry you through the rest of your career troubleshooting the toughest issues. Mastering these basic techniques is the key to getting started solving really fun problems.
5. Futureproofing: Preventing availability, security, and performance issues.
When the groundwork is taken care of, you can start looking forward and reacting to tomorrow’s problems instead of today’s. It’s hard as hell to get here, but wow, the view is fantastic from the top of the pyramid. When your phone isn’t constantly ringing, you’ve got the time to:
- Plan upgrades and offer budget options to the business
- Review T-SQL code before it goes live
- Learn and grow your skills
- Train your coworkers and give back to the community
These are the most fulfilling parts of the database professional’s career. Not only do they make you feel better, they pay benefits forward. For example, when you’ve got time to learn automation, you can write scripts to save yourself even more time – thereby giving you even more learning time! When you can offer budget options for the business to make an old, unreliable server go away, you’re more likely to make it happen. By writing training presentations for coworkers and communities, you force yourself to learn more details.
Month 5: Learning How to Future-Proof the Database Server
Start by making a list of business problems. I’m not talking about “we’re experiencing locking on the Sales table” or “the CPU use is too high” – think about the problems that the business users face. Go walk into the offices where someone is interacting with both the customers AND the database server, and ask them what they’re worried about.
Don’t just ask them what bothers them about the database – ask them what they wish they could do better, or what keeps them up at night. You’ll hear problems like this:
- “We’re getting ready to add dealers in 3 new cities, and our reports are going to get bigger.”
- “I’m worried about what happens if this office gets hit by a hurricane this season.”
- “The vendor isn’t being good about giving us support on this app, and we’re thinking about changing to another vendor.”
Then figure out what SQL Server features, versions, and editions can help them solve their problems – or maybe the answers involve hardware or software changes, too. When you start examining what the business will be doing six months or a year from now, you can be ahead of the curve and fix problems before they get ugly.
Peace of mind: knowing where every database is at on the pyramid.
No, that’s not a line on the pyramid because what the database needs is separate from what you need. See, I used to blame myself if every server wasn’t at the very top of this pyramid. I got frustrated if I knew how to fix it but I couldn’t get the time/money/permission to make it happen. After a few years of using the Getting Things Done philosophy at work, I came to terms with the fact that I’ll always have more work than I can handle. My job is to do the things the business values most, and I just won’t have the time and resources to address everything on every server.
Your job isn’t to bring every database to the top of the pyramid. Your job is to do what you can with the time you have, in the order the business wants things done. Make a spreadsheet list of your servers (not databases yet – let’s start easy) and add columns for each level of the pyramid – Backups, Security, Capacity, Performance, Future-Proofing. Today, you probably have servers where you focus on performance, but you’re not even sure if the backups are good. It’s time to fix that.
Month 6: Give Yourself a Report Card
After you’ve put in all this work, stop and gauge your success. Make a list of before & after scenarios for each level of the pyramid. When you started, what did the database environment look like? What does it look like now? What work and learning did it take to get here?
Show the business the progress you made over the last several months, and explain that you were able to do it all for free (thanks to the help of friendly bloggers, ha ha ho ho.) This is great evidence for you to get a raise or get a training budget.