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.

It’s go time, ladies and gentlemen. Last week we reviewed indexes and the basic differences between reorganizing and rebuilding those indexes. Now, it’s time for the big reveal:

I’m allowed to touch client PROD servers!

A Funny Thing Happened on the Way to the Server

Thankfully, I didn’t have a ton of nerves logging into a client server for the first time, for two reasons.

First, as a WordPress developer, I’ve been in client accounts and such before. I’m aware that a company’s data can be more critical and sensitive than a WordPress blog, but both are important to that client. It doesn’t matter if it’s a multi-million dollar company or a food blogger’s site, you want to do your best and make sure you’re careful to do things correctly. Plus, one of my former WordPress clients makes seven-figures a year off her blog, so I can literally say her blog is every bit as critical as a company’s SQL Server database.

Second, our approach here at Dallas DBAs is very careful. Before ever logging into a client’s server on my own, Kevin first screen shared with me and I watched as he went through practices and procedures. Then, each day for the rest of the week we screen shared as he coached me through logging in and doing a server review. Only after a week of his oversight did he release me to begin solo server reviews on one client’s server. A couple of weeks later we went through the same process all over again with a second client. I’ll be adding a third and fourth client via the same processes in the coming days.

For now, my one and only task is to log into client servers daily and look for problems. I’m learning how to identify immediate red flag issues such as long waits, locks, and blocks. I’m checking the SQL Server error logs for anything out of the ordinary. I’m ensuring backups are up to date. And I’m learning how to effectively use monitoring tools such as Manage Engine, Solar Winds DPA, and Red Gate SQL Monitor.

As I go through this daily process, I’m learning how to properly report a server’s status to our clients, identify problems, and escalate those problems to Kevin. As an apprentice, it’s not my role to attempt to fix problems, but rather to stay engaged and learn how Kevin approaches making things right so that as I progress I add more and more knowledge to my tool belt.

Does This Look Right to You?

This is going to sound bad, but it’s sort of fun when a client’s server “breaks.” Let me explain.

If SQL Server and the underlying hardware always ran flawlessly then there wouldn’t be any need for DBAs, would there? In a way, being a DBA is mostly sitting around waiting for something to go wrong.

That’s not all there is to it. There’s consulting available where some DBAs help clients with architecture and hardware questions. A DBA might be consulted by a client who wants to upgrade their servers or systems. But let’s admit that the main action parts of the job involve putting out fires.

Those fires might be small, such as a client asking, “Why is this query so slow?” or “Can you help us implement a better maintenance schedule?” Other times it’s a 3 AM call from a client who’s panicking because someone accidentally dropped a server, a server died, a restore failed, or something else potentially catastrophic. That’s when a DBA really gets to serve their clients.

For me, that’s really the most exciting part about learning to be a DBA. What I do can have a real, significant impact on a client’s business and well-being. It’s an opportunity to serve.

If you take a look at the graph just above, as well as the graph further up the page, you’ll see an example of how my first week looking at a client’s server went. Long story short, this client had some growing pressure in their server in terms of wait and blocks. There were some regular overnight queries that were taking longer and longer to execute. Eventually this blocking was spilling over into the next hour and affecting subsequent performance.

Wanting the best for our client, I found myself feeling this strange mix of concern and joy over the situation. Concern that we as their DBAs needed to fix this, and joy that we were in a position to help. I had these feelings even though my only contribution to the whole incident was basically saying, “Hey, this looks wrong!” and escalating it to Kevin. But still, I had an opportunity to contribute and it was fulfilling.

In the end, it was an easy fix for our client. They discovered the query that was causing the issue was not critical and simply prevented it from automatically running from within their application. Problem solved. In fact, their server is now performing much better overall than it did before.

What’s the main point of all this, other than simply sharing more about what I’m learning? If you’re out there struggling to figure out what sort of work you should get into, I highly recommend finding that sweet spot where you not only find something you are good at and enjoy doing, but something that also allows you to serve others in some capacity. Serving others is where the long term rewards of any endeavor truly come from.

That’s all for this week. Join me next time for The Ironic DBA Files—Review One: A SQL Story.

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
    • Episode 6: Return of the TSQL
    • Episode 7: The Backup Awakens
    • Episode 8: The Last Rebuild

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 last post I talked about how I don’t enjoy coding all that much and how I once dreaded the idea of being a DBA due to the use of TSQL here and there. I’ve gotten over that fear and made some pretty decent strides in beginning to understand how the code works. Getting a basic grasp on the syntax does wonders for understanding what’s going on in a particular script.

You Don’t Know What You Don’t Know

I’ve had a practice Azure VM with an instance of SQL Server 2016 installed almost since day one in my training. The purpose of the VM is to give me a place to get familiar with the workings of SQL Server and it’s associated tools like SSMS.

I wrote a couple of weeks ago about implementing Maintenance Plans on my VM. I was taking regular backups and performing regular maintenance on the instance’s databases, but not really doing anything with it. In my studies, I’ve read and heard over and over that “you don’t have a valid restore plan until you’ve practiced restoring your backups.” So, I decided it was time to get that done.

I decided to approach my restore plan as if I were restoring to an off-site Dev or QA instance. I don’t have one of those, but Kevin had recently added a second, larger E: drive to the VM. So my thought process was to install a new instance of SQL 2016 on the E: drive and then restore my backups to that instance.

This was also a good refresher on installing and updating a new instance to the most current SP and CU. One of the things I’m seeing often when Kevin works with clients is that some of their server issues can be at least partially resolved by simply updating to current SPs and CUs. I’ve tried to internalize this and make it part of my regular instance install routine.

Once my instance was up and running, I started searching for the best way to restore my backups to the new instance. I found the script below and gave it a go.

RESTORE VERIFYONLY
FROM DISK = N'F:\SQLskills\20130609_SalesDB_Full.bak
WITH CHECKSUM;
GO

As you might see, this script was taken from sqlskills.com, and I had to tweak it be valid for my server, such as changing VERIFYONLY to DATABASE and naming the correct database and file names and locations on my E: drive. Simple, right?

Not. Even. Close.

I can’t go into much detail here simply because it would make this post as long as a short novel. Long story, short, the rest of my day and part of the next day was a revelation of rookie mistakes, including, but not limited to:

• Unfamiliar drive and folder permission problems on Windows
• Forgetting about Recovery Models when not finding log backups for some databases
• Not checking the error logs like I should have when problems arose

This sort of thing went on and on before I eventually threw in the towel and asked Kevin for help. He got me sorted out pretty quickly—as a Senior DBA should—and I was back on track restoring my backups.

Rookie Mistakes are Expected

Guess what? Rookie mistakes are part of the process. It’s very much like riding a mountain bike. You are going to crash when you push yourself to the next level. You’re going to forget how to handle certain difficult situations and need guidance.

What this really means is you’re striving to move forward. Learn from your mistakes and add the new-found knowledge and skills you earned from failure and recovery to your tool kit.

You see, not only is Kevin my Senior DBA guiding me through all of this, he’s also been my mountain bike coach since I started riding trails. This is why I emphasized last week about the need to find a mentor if you don’t already have one.

When I’m riding trails alone I tend to be much more cautious and stay well within my skills and experience. However, when I’m riding with others, especially when I have Kevin in front of me showing the way, I’m much more apt to push myself and try new things—even things that scare me.

That was my approach during this whole backup/restore episode. I wasn’t on a Prod server and I knew that Kevin was right there to help me when I needed him. He patiently showed me not only what I was doing wrong, but pointed out things I didn’t know about or had forgotten such as looking at all the options in the Restore Database dialog to properly name my databases and select the file locations.

As a side note, I mentioned before about how part of my problem was a permissions issue that was specifically a Windows thing. I’ve noted before how I’m a long-time Mac user, and one of my main problems in this restore process was simply the differences in the Windows file tree system. Even once I was able to restore my databases properly they were not getting saved where I really wanted them because of one simple click—an unnecessary click in the Mac OS Finder, but absolutely necessary in Windows Explorer.

It’s all part of the process, and I’m getting there one step at a time. Even if some days it’s two steps forward and one step back, progress is being made.

That’s all for this week. Join me next time for The Ironic DBA Files—Episode 8: The Last Rebuild.

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
  • Episode 6: Return of the TSQL

Follow @Dallas_DBAs