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 week I revisited the need to have a good understanding of Maintenance Plans, even if you end up rarely using them later on in your DBA career. As with all the other topics I’ve touched on so far, having a good understanding of the fundamentals will give you a solid foundation to start building your skills and knowledge upon. This week, let’s talk about code, specifically T-SQL.

Let Me Tell You What I Really Think

Honesty time, here—not that I don’t strive to be honest all the time anyway—I hate coding. I know how to code various languages at differing levels of proficiency, but I’ve never really enjoyed the process of learning a new language and parsing it. This is the number one reason why I avoided looking into SQL DBA as a career when Kevin first hinted at the possibilities a couple of years ago. The thought of sitting in front of a computer and banging out code for most of the day was a non-starter for me.

Thankfully, I learned that becoming a DBA involves some code wrangling, but not anywhere near as much as I expected. In fact, the early days of my training had me pretty much avoiding TSQL as much as practical so that I could concentrate on all the things I’ve previously written about.

However, there came a point where there was no option but to begin learning the basics of TSQL so I could better understand what was going on under the hood of SQL Server. Plus, though there are often multiple ways to get something done in the SSMS GUI, there are times when writing a quick script or copy/pasting a script is faster, more flexible, and more reliable for a particular database’s needs.

I’m not here this week to tell you I’m now a TSQL guru by any means. I’ve still got a long way to go, though I’m fairly comfortable writing very basic scripts and parsing slightly more complex ones. But I still hit a wall pretty quickly in understanding what’s going on within even a slightly long or complex script. That’s ok, I’ll get there.

A Few Tips I’ve Learned

• First up, if you aren’t blessed to have a mentor guiding you along the path, do everything you can to find one. I can’t tell you how many times I would have been completely lost without Kevin there to fill in the gaps.
• Second, don’t be afraid to read stuff that’s over your head. Just don’t spend too much time trying to figure out what it means if you’re not grasping it right away. Case in point: I often take the time to Google stuff and land on either SQLSkills.com, SQLAuthority.com, or BrentOzar.com. Those three sites have become my go-to sources when I want to figure out how to fix something or better understand how SQL Server works.
  The difficulty is that these sites often go beyond my current levels of understanding fairly rapidly, especially when they start using script examples. That’s fine. Getting exposed to TSQL and reading explanations about what the scripts accomplish help me gain familiarity.
• Third, don’t expect to fully understand a script unless you understand what it’s supposed to accomplish. This sounds similar and somewhat backward from my last point, but often I run into difficulty understanding a script because I simply don’t grasp what the script is doing to the database.
  For instance, one of the tasks Kevin had me perform recently was to create a new Filegroup on my practice StackOverflow database, and then move that Filegroup file to a new drive on my VM. I needed to do it through TSQL scripting, which meant I had to learn what scripts to use, but I also had to have a fundamental understanding of what was actually happening to my database. I couldn’t have one without the other.

• Fourth, get familiar with the Script button in your various SQL Server windows and dialog boxes. Often, when you’re performing some sort of job or task in the SSMS GUI, the software provides a little button up in the toolbar that will show you the actual script that will run once you’ve chosen your options, set various parameters, and hit “OK.”
  Hitting that Script button (when available) before you click “OK” will pop up a new query window in the background and populate it with the script will be run. Use this as a learning tool to see how the choices you made in the task window create specific commands within your script to be executed.
• Fifth, find yourself some good tutorials. Though it’s pretty basic in a lot of ways—and teaches the ANSI standard alongside TSQL—I’ve found the SQL tutorial at W3 Schools to be an excellent training resource. I’ve spent a few hours using it just this week to reinforce what I already knew and take my TSQL knowledge to the next level.

What have you done to help yourself learn TSQL? I’d love to hear your own tips and tricks so I can try them out for myself.

That’s all for this week. Join me next time for The Ironic DBA Files—Episode 7: The Backup Awakens.

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
• Episode 5: The Maintenance Plan Strikes Back

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.

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