SQL

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

Whew! Where to start? There’s really so much going on—and going into my brain. Last week was spent on a myriad of topics every junior DBA needs to know. I wrapped up some introductory absorption of understanding backups—along with clearing up some personal misunderstandings—and started messing around with Maintenance Plans. More on that later.

Can I Run DBCC CheckDB on My Brain?

Corruption happens, right? It really shouldn’t be any surprise to anyone who’s owned a computer for a few years or more. In my lifetime, I can’t count how many computers I’ve owned—going back to the 1980s—but what I can tell you is that the number one failure those computers experience most often is HD failure.

Yeah, I get that HD failure isn’t the only thing that can cause data corruption. That’s not the main point here. The point is that the likelihood of your database getting corrupted only increases over time. It’s really the 2nd Law of Thermodynamics applied to computer data—the universe tends to move from order to disorder, and your database is not immune to this law.

This is where CheckDB comes in, but here’s the deal. Similar to the last episode of this series, wherein I was somewhat flabbergasted to learn that there are people out there who don’t properly backup their database, I’ve been equally shocked to learn that there are those who don’t regularly—if ever—check for corruption issues either.

In their defense, I think there’s a huge swath of IT folks out there who ended up with DBA-esque responsibilities simply because they were the on-staff geek at their place of employment—the proverbial Accidental DBA. I’ve learned that these IT workers are already swamped with their normal responsibilities and the DBA piece is an added burden, so they learn how to do the bare minimum to keep the database running and move on with their day.

If you’re reading this and the above describes you, then you owe it to yourself to dig into learning all you can about CheckDB. What can CheckDB do for you? Here’s a quick rundown:

• Checks the logical and physical integrity of all the objects in the specified database.
• Checks the consistency of disk space allocation structures for a specified database.
• Checks the integrity of all the pages and structures that make up a table or indexed view.
• Checks for catalog consistency within the database.
• Validates the contents of every indexed view in the database.
• Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
• Validates the Service Broker data in the database.

I’m sure you understood ALL of that, right? Yeah, me neither—at least not yet. What you really need to know is that CheckDB will look through every single page of data in your database to find any corruption.

What should you do if CheckDB finds corruption? Drop everything and read the post What to Do When DBCC CHECKDB Reports Corruption from Brent Ozar (b|t).

Please, please, please, learn all you can about CheckDB before implementing it. The main thing you need to know right now is that it is the most intensive I/O operation you can run on your database, so it can take a lot of time on larger databases. It’s also a blocking procedure, so your database will be unavailable during execution which means you need to schedule it during a daily maintenance window if possible.

Are You Calling Me Normal?

Everyone thinks they’re normal until they meet other people who believe they’re the normal ones…then you start to second guess yourself…but I digress.

I spent an inordinate amount of time one day last week struggling to understand normalization. As typical for me, I went too far, too fast and ended up with a non-functional brain by the end of the day. (Thus the request above to run CheckDB on my brain.)

Man, there’s a lot to grasp in normalization—1st Normal Form, 2nd Normal Form, 3rd Normal Form, and beyond.

Here’s my takeaway about normalization after hurting myself trying to understand it at a deep level: Normalization is all about shaping data in a way to reduce redundancy. A well-formed database structure will be a normalized database.

This means, in the end, it’s a good idea to understand the basic concept of normalization but don’t worry about going too deep. Your time is much better spent on starting to understand how a well-ordered B-tree structure gives you performance gains. A tightly-structured clustered index is going to nail normalization down.

Are You Feeling Insecure?

A quick word here about SQL Server security, mainly because I hinted in the last post that I’d talk about it this week.

When it comes to SQL Server security, the first thing you need to grasp is the difference between logins and users. That’s it. If you can understand that fundamental difference, then you’re well on your way to understanding how to properly grant rights to people who want to poke around in your databases.

I highly recommend you read Kevin’s (b|t) post SQL 101: The SQL Security Model for starters. From there you can move onto Kenneth Fisher’s (b|t) posts Logins vs Users and Administrative Logins and Users.

That’s all for this week. Join me next time for The Ironic DBA Files—Episode 3: Revenge of the Index.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files

• Prequel: The Ironic DBA—Starting a New and Unexpected Career
• Episode 1: You Back That Up?

Follow @Dallas_DBAs

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.”
~Paul Randal

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.

What’s Next?

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.

The Ironic DBA Files

• Prequel: The Ironic DBA—Starting a New and Unexpected Career
• Episode 1: You Back That Up?

Follow @Dallas_DBAs

Hey there! I’m Jeff, the latest apprentice at Dallas DBAs and a new contributor to the blog. If all goes well, I’ll be around for a long time and we’ll all get to know each other better.

Who am I and what’s my story? I’ll try to be as brief as possible. Kevin and I have known each other for over 15 years, though we really didn’t start getting to know each other really well until about six years ago. When we first met, I’d been hired as the new music minister at his church, serving there for exactly 10 years to the day. Due to vocal troubles and a few other issues, I “retired” and essentially went home to work side by side with my wife in her established graphic/web design business. (I’m even the WordPress dev behind this very site).

About the same time, Kevin and I (along with our families) grew closer in friendship because we decided to intentionally stay in close contact. Our kids were close friends and we wanted to keep that connection going. Little did we know what would bloom from our decision to get together and play cards almost every week for the last several years.

During that time I’ve heard Kevin tell dozens (maybe hundreds) of stories and anecdotes about both cycling and database administration. (I can’t tell you how many times I’ve heard the name Brent Ozar!) A couple of years ago I gave in to my curiosity and asked Kevin to help me get started mountain biking. Since that day I’ve become completely addicted to riding my bike on dirt trails, and even on pavement from time to time. I’ve gotten so into the pastime that I even started my own website, NTX Trails—the most comprehensive source for mountain bike trail information and locations in North Texas.

The last couple of years in the freelance graphic design and WordPress development field have been tough. There’s more and more (global) competition out there so it’s become increasingly hard to land enough regular work. I even dabbled with adding photography to my portfolio over the last year and a half, a move which didn’t bump the income needle at all.

That’s when Kevin impacted my life significantly again. He’s offered to train me to become the best DBA I can be—as quickly as can be—without skipping any important steps along the way. I felt like it was an offer I couldn’t and shouldn’t refuse, so here I am jumping in with both feet.

So, unlike many of you who are Accidental DBAs, I’m more like an Ironic DBA (in training).

Why?

1. I’ve always been more of an “artsy” type. My original college degree is in music, and I spent over 20 years in music ministry if you count both my volunteer and vocational experience. On top of that, once I left music full time I became a designer—and as I mentioned, a photographer—both very visual fields. Even in my WordPress development phase I excelled at the design part and dreaded the development part (backend, PHP coding, etc.).
2. I’m a Mac user for life. I’ve literally been using Macs since the original 1984 model. Though I’m familiar with Windows, it’s been about 17 years or more since I’ve interacted with a Windows box on a semi-regular basis. At one point in my life I was a school computer teacher, and my classroom lab was 50% PC/50% Mac—way back on Windows 2000. I often threw out regular Windows vs Mac jokes such as, “Computers are just like air conditioners. They stop working properly if you open Windows.” Yeah, I was that guy.

Those two points alone make it strange that I would ever consider SQL Server DBA as a potential career, especially as I’m nearing 50 years of age. But the truth is I really enjoy learning new things and challenging myself, and that’s one of the beautiful things I see in the DBA world. Just this morning while training with Kevin he made mention of concepts that I’ll learn and be responsible for understanding “6 months from now,” or “1 year from now” and even “2 years from now.”

That’s very interesting to me to see such depth in the field so that there’s no way it can get stale or boring unless I just give up and decide I’ve learned all I want to know. Not to mention the continual updates and advances in technology that will necessitate always keeping my learning hat firmly planted on my head.

Plus, I’ve come to be convinced that I can excel at become a DBA if I work for it. Not only will I have Kevin to guide me and correct my mistakes, but the job is essentially about troubleshooting. I’ve always been the “IT guy” pretty much by default everywhere I’ve worked in the past. Mainly because I was the resident geek—especially in the ministry environments—but also because I’ve never been afraid to poke around with stuff on a computer in an attempt to troubleshoot a problem for a coworker.

I’m also very entrepreneurial in the sense that I’m a self-starter, need zero supervision to do my work, have handled customer service and satisfaction on my own for years, and am willing to put in whatever work is necessary to get stuff done.

So far I’m really enjoying the experience and training, but admittedly I’m only a little over a week into the process. Obviously, it will be a while before the newness wears off and I’m sure there will be hard days. I feel like I’m drinking from a firehose most days learning about systems and processes that are almost completely new to me, but I like the challenge. One of the hardest parts so far is just getting re-familiarized with how Windows operates. I’ve got about 35 years of Mac-centric muscle memory to overcome here!

Thus far I’ve worked with installing instances, updating instances to the latest SPs and CUs, learning about the different file types involved in a SQL Server environment, identifying the differences in the different recovery models, and getting started with understanding backups and restores. I’ve even started playing around with Power BI in my spare time. I’m sure there’s more I’ve learned that I’m forgetting, but time and repetition with make it all more clear.

There’s even more back story than this to share, but I’ve gone on too long already. I’ll share tidbits here and there as time goes on and as they’re relevant to what I’m learning about at the time.

I hope you’ll stick it out with me as I reinvent myself again in middle age. It should be a fun adventure. I’ll even talk about bikes from time to time too!

You can follow me on Twitter at @SQLandMTB.