SQLandMTB

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.

In the previous post I rambled about for a bit talking about primary keys and clustered index keys. That writeup was very tip-of-the-iceberg stuff and there’s much, much more ground to cover on that topic in the future. This week I circle back around again to touch on a topic I’ve already written about in the past: Maintenance Plans.

You’re Gonna Use That?

I’ve learned that Maintenance Plans have something of a bad rap among experienced DBAs, and I can partially see why. If you’re new to this game like me, let me give you the two main reasons I’ve pinpointed as to why a Senior DBA might tell you to not use Maintenance Plans.

• Misconfiguration: Like so many others functions within SQL Server, it’s very easy for an Accidental DBA to choose the wrong options and parameters. I’m not going to run through best practices here, but simply knowing that you can screw up your database by incorrectly implementing a Maintenance Plan is something to be aware of.
• Inferior Product: While SQL Server is itself an enterprise-level piece of software powerful enough to encourage companies and governments worldwide to use it for their data, let’s admit that it’s not the best at everything. When it comes to performing regular maintenance tasks on your server, there are better options out there if you know where to look.

Here’s the core issue with both of the complaints above—an inexperienced DBA doesn’t know any better. And as we’ve discussed before, so many DBAs are not trained, let alone full time. They were thrown into the server room by the boss and told, “fix it!” just because they were the staff computer geek.

If an Accidental DBA digs just a little bit into the workings of SQL Server they’ll discover the Maintenance Plan Wizard and think, “Hey! This is easy!” The problem is that the Wizard assumes you know what every available option does and how it affects your data. They also don’t have the time or experience necessary to understand how to properly implement a third-party set of scripts. Think about it, if a new DBA doesn’t understand how to properly implement a built-in set of maintenance scripts, how can they be expected to implement a more complex third-party product?

Like me, a new DBA is going to look at the Maintenance Plan Wizard their first time out and see the shrink option and mistake it for compression. Compression the database is a good thing, right? So shrinking my database is a great idea! Making a mistake like this can create a database that performs horribly, and the Wizard makes no attempt to prevent you from making poor choices. Even newer versions of SQL Server with an updated Maintenance Plan Wizard don’t help a junior or apprentice DBA understand what the options mean.

Brent Ozar (b|t) said, “If maintenance plans are designed for the accidental and junior DBAs amongst us, I don’t think a lot of this stuff [options included in the Maintenance Plan Wizard] should even be an option. It should just default to the right thing, and take care of the database with Microsoft’s best practices set up as standard.” SQL Server 2016 Maintenance Plan Improvements.

So here’s my attempt to help my fellow apprentice DBAs better understand how to implement a rational set of Maintenance Plans that won’t simply end up making things worse.

Maintenance Plans for Apprentice DBAs

First of all, the best thing you can do for yourself is to go and learn what the various options in the Maintenance Plan Wizard can do, and what implementation of those options will do to your database.

Second, one of the very first things I learned from Kevin is that you should separate all the different Maintenance Plan options you intend to implement into separate tasks. Simply put, while it’s easy within the Wizard to throw together one plan that does an integrity check, an index rebuild, and a full backup, it’s always better to keep them separate and scheduled during appropriate maintenance windows.

Third, related specifically to backups, another guideline Kevin shared early on is that any server in production should be using the FULL recovery model. Why? Because otherwise you can’t perform Transaction Log backups, which means you lose the ability to apply point-in-time restores.

If you don’t know this already, a log backup truncates the transaction log when using either the full or bulk-logged recovery models. A regular log backup helps keep your transaction log from growing uncontrollably, allows you to perform the aforementioned point-in-time restores, and limits data loss during database recovery.

Other quick points:

• Perform Check Database Integrity Often: Do this DAILY if possible, but at least WEEKLY. Always schedule this during a wide maintenance window if possible. (Read more about CheckDB back in Episode 2.)
• Learn the Difference Between Rebuild and Reorganize Index: It sounds simple, but this is an instance where you need to understand a bit about what’s going under the hood of SQL Server and how rebuild and reorganize are not the same thing. One of the chief points to understand and learn more about is the fact that rebuilding indexes automatically triggers updating of statistics, while reorganization does not.
• Create and Schedule Backups to Meet RPO and RTO Standards: If you’re working on a server in production, don’t just throw together a set of backups and call it a day. You need to know what your company/client expects in terms of acceptable data loss and down time. Your set of Maintenance Plan backups must be crafted to fit these expectations.
• Never Use Shrink Database: NEVER. Perform this manually on demand when necessary. Check out Paul Randal’s (b|t) in-depth look at why you should not shrink your data files.
• Learn About the Other Tasks: There are three other major tasks you can perform with a Maintenance Plan—Maintenance Cleanup, Execute SQL Server Agent Jobs, and History Cleanup. I won’t cover these here, but go and learn about them once you’ve got a handle on the other steps above.

That’s all for this week. Join me next time for The Ironic DBA Files—Episode 6: Return of the TSQL.

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?
• Episode 2: Attack of the Corruption
• Episode 3: Revenge of the Index
• Episode 4: A New Primary Key

Follow @Dallas_DBAs

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.

Last time we took a look at some nomenclature related to SQL Server and touched on the basic differences between clustered and nonclustered indexes.

I’ll be honest here in this post—I’m worn out—but it’s the good kind of tiredness. Those of you who have been DBAs for a year or more probably don’t realize just how much knowledge you’ve absorbed and put into practice since you started. And those who are Senior DBAs…well…your knowledge can often look like magic to the uninitiated apprentice. (Insert Gandalf image here.)

I’m deep enough into the weeds that I’m finding it a bit hard to drill down to something specific to write about this week, but I’m going to give it my best try. Apologies in advance if this seems like a bit of a ramble.

The Keys to the Kingdom

Picking up a bit where I left off last week, let’s talk about Primary Keys.

Remember, a table is made up of columns and rows. Data in a database table is stored in the “cells,” or fields. Typically, tables in SQL Server have a column or set of columns that contain values to uniquely identify each row. This column/column set is known as the Primary Key.

A table can only have ONE Primary Key, though—as mentioned above—it is possible that a Primary Key can consist of either single or multiple columns.

How do you know if your table has a Primary Key? Well, if you created the table yourself and haven’t built a clustered index on that table yet, your table probably doesn’t have a Primary Key unless you created a primary key at the time of table creation.

Here’s a quick bit of code that shows how you might create a new table with a primary key constraint.

-- Create table with primary key constraint
CREATE TABLE Projects
(
    id INT NOT NULL,
    name VARCHAR(255),
    start DATE NOT NULL,
    end DATE NOT NULL,
    CONSTRAINT PK_Projects_name PRIMARY KEY (name)
);

The above snippet of T-SQL creates a table named “Projects” with four columns: id, name, start, and end. The project_name column is defined in the code snippet as the table’s primary key constraint.

Now, what you need to realize at this point is that as soon as a Primary Key is created on a table, that table is now a clustered index. The Primary Key is now also a clustered index key.

Note: The above column names are really not all that great, but are kept simple for illustrative purposes to my fellow apprentices.

I Need a Different Clustered Index Key

What you may or may not recognize right away is that a table’s Primary Key might not be the best value to build a clustered index upon. Hopefully you’ll have been able to put some thought into the process of building a new database’s architecture and have a chance to set keys up as best you can from the start.

Even if you get that opportunity, over time you may discover that user’s queries would be better served with a different indexing structure—which leads you to figure out how to drop a Primary Key in order to build a new clustered index.

Before we move on, let’s take a moment to consider what makes a great clustered index key. [Hat tip to the highly recommended Pluralsight Course SQL Server: Indexing for Performance by Kimberly L. Tripp. (b|t)]

Characteristics of a good clustered index key:

• Unique—Value is not duplicated elsewhere.
• Static—Value will not change.
• Narrow—Value is as specific as possible.
• Non-nullable—Value cannot be NULL.
• Fixed width—Value of the key has been assigned a fixed width.
• Ever-increasing—Value increases over time.

So, what happens if sometime in the future you determine the current Primary Key isn’t the best choice for your clustered index? You have to perform two T-SQL commands. You have to drop the primary key constraint and then build a new clustered index.

--Drop constraint
ALTER TABLE Projects
DROP CONSTRAINT PK_Projects_name
GO

-- Create clustered index
CREATE CLUSTERED INDEX IX_Projects_id
     ON dbo.Projects (id)
GO

HT: Pinal Dave (b|t)

Go ahead and test all the above code snippets out on your test instance. Wait, you don’t have a test instance?

Guess what? You can set up a inexpensive test instance by setting up an Azure account and creating a VM. Just be sure to pay attention to how you structure your resources and how long you keep it running each day. There’s no need to go crazy and create a VM that’s an uber-computer and leave it running 24/7. That’s a quick way to go bankrupt.

That’s all for this week. Join me next time for The Ironic DBA Files—Episode 5: The Maintenance Plan Strikes Back.

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?
• Episode 2: Attack of the Corruption
• Episode 3: Revenge of the Index

Follow @Dallas_DBAs

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.