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.
