DallasDBAs.com

Explaining SQL Server in plain english

Apprentice

The Ironic DBA—My First Year as a DBA [Part 4]

by Leave a Comment

My Favorite and Recommended Training Resources

As I’ve been in intense, focused SQL Server training for the last year, I thought it would be a great time to share links to the resources I’ve gathered over the preceding months. To be clear, I’ve not yet had the chance to use all of these resources, but have saved them for future use. So, I can’t guarantee that all of these resources are of equal value or quality. Some types of presentations will appeal to you in various degrees based on your learning style.

I’m not going to list many specific blogs or non-dedicated training resources, but there’s a wealth of information out there if you search for it. One of the best things you can do is jump on Twitter and follow the #sqlhelp and #sqlfamily hashtags to find some of the best minds in the SQL community. You should also join the SQL Community Slack channel and get involved.

Basics for Beginners and Accidental DBAs

Kevin’s Getting Started with Your First SQL Server Instance on Pluralsight.

Though it’s not out yet—but should be very soon—Kevin has a new class on Getting Started with SQL Server Maintenance coming out on Pluralsight that is the perfect next step once you’ve completed his first class.

You should also check out Kevin’s YouTube channel for an array of quick videos on many basic SQL Server concepts you should know.

And to finish tooting our own horn here at Dallas DBAs, you should read all the posts in the Accidental DBA category here on the blog.

For the first of many links to Brent Ozar materials, I suggest starting with his DBA Training Plan series of blog posts to get grounded.

I highly recommend the Accidental DBA Series at SQL Skills. Prepare to go a lot deeper and take your time with these posts. I should probably go back and re-read all of these myself.

SQL Server Tutorial has a nice collection of beginner-level tutorials to help you get a grasp on concepts.

You should definitely subscribe to PASS’s DBA Fundamentals Virtual Group. You can peruse the meeting archive to find past webinars on a variety of topics.

You can learn about SQL Server, Azure, and more at Microsoft Learn.

Check out Kendra Little’s Training Plan for Junior DBAs Learning SQL Server, as well as her Dear SQL DBA series.

Topical Overviews

The SQL Server Central Stairways series covers everything from Azure to U-SQL and more. There’s no particular order to climb these stairways, but some will require more previous knowledge than others.

Microsoft has a collection of SQL Server and Azure Labs and Workshops available for free.

Online Classes, Webinars, and Streams

Get a grasp on the basic of indexes and how they work with Brent’s How to Think Like the SQL Server Engine.

Speaking of Brent Ozar, we got in on his Black Friday sale last year for his Recorded Class Season Pass, and it has been a fantastic experience. Not only is Brent a top-notch presenter and teacher, he constantly updates the classes with his latest live presentation, which means you can re-watch the class every few months and learn about different aspects of the topic at hand based on class participant questions.

You can see all the content from past SQL Bits conferences. There’s over 900 videos available spread across dozens of SQL and data-related topics.

Here’s a handful of SQL Server related channels you should definitely get subscribed to today:

The Ironic DBA—My First Year as a DBA [Part 1]

by Leave a Comment

Whoo! It’s been six months since my last post. There are reasons for this, none of which are probably valid, but it’s the way it is. I’ve been a full-time DBA for just over a year now, and Kevin thought it would be a good time to review how far I’ve come thus far. Looking back, it’s been quite an experience.

Ozar Number = 3

Back in May of 2019, after several years of working from home as a graphic designer and WordPress developer, my family was more than three months into almost no income. Due to various factors, we just weren’t getting many leads and many of our clients had moved on to lower-cost options. Our emergency fund was empty, and I began sending out emails to friends and acquaintances asking them to keep an ear to the ground for any potential job leads. I started looking at everything from working in the warehouse at IKEA, driving a UPS truck, and even become an insurance adjuster.

My long-time closest friend Kevin—for whom I had done some Dallas DBAs-related graphics work over the years—responded by basically saying, “I think I can help. Let’s go for a bike ride and talk.” Before the end of the week, Kevin had me set up with a free PluralSight account and I began learning the basics of what makes SQL Server tick. It all began with Kevin’s own course, Getting Started with Your First SQL Server Instance.

The funny thing is, Kevin had been offering free one-day classes throughout the DFW area for several years, teaching introductory basics of SQL Server for accidental DBAs and those curious about database administration as a career. I had attended his classes more than once to give him an honest evaluation from the viewpoint of someone who knew almost nothing about SQL Server. Looking back now, I can see that Kevin was already working on me to consider changing careers, knowing I would probably be a good fit.

Coming back around to May 2019, we agreed for me to start with some self-paced investigation into the world of SQL Server, with the goal of me attending SQL Saturday Dallas on June 1, 2019. If I was still interested in pursuing DBA as a career, that day would be my first official day on the payroll at Dallas DBAs.

Funny enough, I had already attended SQL Saturday Dallas the year before as a volunteer photographer. Being an actual attendee was quite a different experience. I had already volunteered to be the photographer for a second year long before considering becoming a DBA, so I split my time between helping Kevin staff his company sponsor’s table, taking photographs, and attending a handful of sessions. Honestly, most of the sessions were way over my head, but it was worth getting to see some various approaches to troubleshooting. I ended the day attending Brent Ozar’s (b|t) class on What’s New in SQL Server 2019 and had a fantastic time despite being completely lost for most of the session. The term parameter sniffing has been indelibly etched in my brain since that day.

Oh, and what’s with the Ozar number? At some point in the past, my co-worker SQL Dork (b|t) came up with an idea that basically goes like this: If you’ve done online training with Brent then add 1; if you’ve met Brent in person then add 1; if you’ve taken an in-person class with Brent then add 1. My first day on the job at SQL Saturday, since I’d already done one YouTube “class”, my Ozar number went from 1 to 3. I’ve spent many hours training with Brent online since that day.

The Apprentice

From that day forward, and for the next several months, I was the newly-christened apprentice at Dallas DBAs—earning SQL Dork a promotion to Junior. I had already spent some time working in a VM 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. Now my training began in earnest.

In the interest of keeping this post from growing into a novel, below are the links to a series of posts I made in the first four months of training. These cover a lot of the initial concepts I learned on my way to starting work on production servers and becoming a junior DBA. Don’t worry, there’s more to come in my story as I try to fill in the gaps between those posts and what’s gone on in the last few months.

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



T-SQL Tuesday 121: The Gift of the Dream Job I Didn’t Know I Wanted

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Mala (b|t) who has asked us to write about “Gifts received this year.” One idea given to write about was “landing a job you never thought you would,” which is perfect for me.

If you’ve followed my story, you know that I’m pretty new to the DBA world. I’m a retired worship leader who’s lost his voice and went home to work side-by-side with his wife in a graphic/web design biz. As I watched that business go through an extended downturn, I attempted to add photography services and grossly underestimated how saturated the local market was. In desperation for meaningful employment I reached out to my friends for help and referrals.

Kevin stepped in and offered a trial period as a DBA apprentice at Dallas DBAs and I’ve never looked back.

Why has becoming a DBA been such an amazing gift? Here are a few of my top reasons.

  • I get the opportunity to learn new things daily.
  • I get challenged by this role a great deal. That makes jobs enjoyable for me.
  • I get a steady income that brings stability and is allowing my family to build a new house in order to foster/adopt more kids.
  • I get to work from home and share an office with my wife—my favorite person in the world.
  • I get to work with my best friend, Kevin.
  • I get to ride my bike way more often than if I worked out of the house.
  • I got a new bike for Christmas as a direct result of this job.
  • I get to ride bikes with Kevin.
  • I get to continue working and serving at our church, which I love.

I could probably go on and on, and I fully expect being a DBA to continue to offer challenges and rewards in the year ahead!

The Story of Jeff

by Leave a Comment

Jeff level enthusiasm, Image by zoegammon from Pixabay

Jeff (b|t) and I have been friends since sometime in 2003. He was the worship leader at my church at the time. Most recently Jeff and his wife have been running a graphics design firm. Websites, book covers, textbook layout, WordPress development, etc.

Earlier this year when the graphics biz was slumping he asked if I needed any help at Dallas DBAs. After some discussion I offered him a 3 month apprenticeship – full time at offensively low hourly rates. I would be teaching him and letting him self-teach through whatever means he could get his hands on.

Inside that 3 months he was already getting on customer servers and doing daily checks (with my guidance of course).  We have a process for this.

He currently has primary DBA responsibility for 5 hourly clients and one Pocket DBA™ client, escalating to me as necessary.

As of this month, Jeff’s efforts are driving enough billable hours that he is COVERING HIS ENTIRE SALARY!  SQL Dork (b|t), the other Junior DBA here has been self-covering for a long time due to only working part-time.

I would like to tell you all of the steps Jeff took along the way…but he already blogged the whole trip.

For your reading pleasure:

The Ironic DBA Files

Thanks for reading!

Kevin3NF

The Ironic DBA—Don’t Be Afraid of the Climb

by Leave a Comment

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.

Yes, my blogging pace has slowed somewhat, but I’m back with something to share. Last time I wrote about getting back to basics and reinforcing my knowledge of introductory SQL Server concepts. This week I want to go in the opposite direction and share why I think you should regularly push yourself out of your comfort zone.

The view from the top is worth the climb.

Climbing Sucks

I’ve mentioned before that I love mountain biking—I’m not great at it, but I love getting out on the trails. Unfortunately, I also have very low heat tolerance, and so I was almost completely off my bike for the majority of the Texas summer—about 2.5 months. I did sneak one ride in before the worst heat abated about 3.5 weeks ago, but it didn’t end well.

I’ve been able to get back out and do three or four trail rides a week for the last three weeks. The trail I’ve frequented is one of the closest to my house, though it’s not a favorite. There’s not a lot of elevation at this trail, but after so long off the bike even gentle slopes were tough to climb at first. To make matters worse, I’m overweight, have some significant balance issues, and absolutely suck at climbing.

Fast forward to just last night where I went out and rode a different trail—the same one that hadn’t gone well 3.5 weeks ago. This trail is not the longest or hardest in the area, nor does it have the most climbing, but it’s been my nemesis since the first time I put down tires on its dirt. I have a less than 50% completion record at this particular trail, so I’m determined to conquer it.

Last night’s ride was pretty good. I completed the entire trail for the first time in months, and set some personal records on a few segments along the way. But here’s the coolest thing about the ride. At one point I made it to the top of climb that’s typically pretty tough for me and took a quick water break. I immediately noticed that the climb had felt easier than expected, but I figured that was due to intentionally choosing easier gears than usual. I was pleasantly surprised to look down at my gears and realize that I was 9th gear when I thought I was probably in 6th or 7th. (For those who don’t ride bikes, the lower the gear number the “easier” the gear.)

The time I’ve been spending getting back to trail riding regularly is already beginning to pay off. I’m back to feeling comfortable on the bike, my fitness level is increasing again, and my ability to ride harder, longer, steeper trails is gradually improving.

Climbing is Awesome

Tim Chapman presenting “Troubleshoot SQL Server Like a Microsoft Engineer”

“That’s great,” you say. “Good for you. But what does this have to do with SQL Server stuff?” I’m glad you asked!

I recently attended a fantastic one-day workshop that was made possible by my local PASS group, the North Texas SQL Server User Group. After the huge success of this year’s SQL Saturday Dallas, and in particular the response attendees had from Brent Ozar’s (b|t) performance tuning pre-con, the group decided to bring in Tim Chapman (t) in for a one-day workshop entitled “Troubleshoot SQL Server Like a Microsoft Engineer.”

I was looking forward to attending even though I knew the vast majority of the class was probably going to be over my head. I’ve advanced fairly rapidly in my SQL Server knowledge since beginning this journey a little over four months ago, but the reality is I’ve only got four months of experience. That’s actually far too little time and experience to make the most of a class at this level. So why bother attending?

Learning is a climb, sometimes a very arduous climb. Just like some of my trail rides, there are moments when I can’t reach my learning goals and have to take a step back and start over. Some days the learning is smooth and the climbing is easy, boosting my confidence and my progress. Some days, the climb is hard, but not as hard as expected due to the work I’ve put in previously.

It’s for this very reason that I wanted to attend this workshop, and the same reason I’m currently working my way through Brent Ozar’s Senior DBA Class. Both are way over my head and deal with issues I won’t have to worry about being directly responsible for any time soon. But the classes make me climb and reach for a goal. Such classes make me dig deep and recall everything I’ve learned so far and apply it as best I can. Such classes expose holes in my current knowledge set and help flesh out a trail map for what I need to learn moving forward to become the best DBA I can be.

Sometimes the climb sucks, but getting to the top is awesome. Onward to the next peak!

Troubleshoot SQL Server Like a Microsoft Engineer—A Review

As for Tim Chapman’s class itself, let me give you a quick review and share my thoughts and takeaways from the day.

First up, I didn’t know what to expect walking into the class. Still being somewhat new to all this, I sort of expected the class to rapidly accelerate to light speed and beyond to leave me grasping for whatever small tidbits I could glean. That never happened.

Tim is an excellent presenter, but beyond that he’s an excellent teacher. So many technical people who are very smart, know their stuff, and have years of experience often don’t realize just how far beyond the average newbie they really are. But not once did I feel like the class was moving too fast for me to keep up, and a great deal of this is due to how Tim broke everything down logically, built concepts upon one another, and didn’t make assumptions that everyone in the room knew more than they really did.

Having said that, there definitely was a certain level of foundational knowledge required in order to make the most of the class, and thankfully my studies thus far proved to be enough. There were a few concepts and pieces in SQL Server that I’d never heard of before (namely triggers and forced query plans), but by and large I was familiar enough with SQL Server, relational databases, nomenclature, and SSMS to never get completely lost.

In a nutshell, here’s what Tim covered in roughly seven-hours-worth of sessions:

  • An Overview of Troubleshooting
  • Queries and Waits
  • Extended Events
  • Query Store
  • Most Common Problems

One of the most encouraging things Tim shared as a new DBA was this concept: “Often, there are 5 or fewer things to look at for a server to solve problems. Like the Pareto Principal: 20% causes 80% of the issues. Today’s Goal: Identify and master the 20%.”

This is why so much of the class was understandable and relate-able. The truth is, to know how you fix the other 80% of problems possible in SQL Server means you’ve either gained years and years of experience, know how to ask Google and StackOverflow the right questions, or both. Stay in the DBA field long enough and you’re sure to run into something rare, an edge-case that very few people have seen. But beyond those edge cases, the vast majority of the problems we see are repeated regularly. Getting a grasp on these underlying issues that regularly rear their head will help me be a solid DBA while giving me the foundation to troubleshoot those rare issues.

Big thanks to Tim for helping me put together a more direct path to follow on my self-guided learning. If any of you reading this ever have the chance to take this class from Tim in the future, I highly recommend it. I’d like to sit through it again myself sometime in the future when I’ve got more knowledge and experience in my tool kit.

Also thanks to NTSSUG for investing in your members and bringing Tim in to teach us. I’m looking forward to more opportunities in the future.

That’s all for this post. Join me next time for the next episode in The Ironic DBA Files.

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



The Ironic DBA—Failure Is Always an Option

by Leave a Comment

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 episode I showed how I tweaked some in-house scripts to provide more user-friendly output. This time around I want to revisit those scripts and give my fellow beginner DBAs some insight on some very basic troubleshooting.

Failure is Always An Option

I’m a big Mythbusters fan, and was saddened when the show eventually went off the air. There’s so much I learned about how the world around me works by watching the antics of Adam Savage, Jamie Hyneman, the rest of their crew. I still follow both of them on Twitter (links above), and also regularly watch Adam Savage’s Tested on YouTube.

Several pithy phrases were said over the many seasons of Mythbusters episodes, including gems like, “I reject your reality and substitute my own,” and “Jamie likes big boom.” My favorite line from the show is “Failure is always an option.”

My family has been rewatching some of the show’s episodes, and in one of their final shows Adam mentions how they wouldn’t have been able to accomplish that particular episode’s goals without their previous years of experience. If you were to go and watch every episode, you’d quickly realize that the Mythbusters failed more often than succeeded. It’s through repeated trial and error that they learned the most beneficial lessons.

Learning about SQL Server’s capabilities has been a similar sort of journey. It’s still early days for me, but I’m sure that most Senior DBA’s out there will tell you that the knowledge they’ve gained over the years has been full of “that didn’t work” moments. For fun, check out this video from Bert Wagner (b|t) about SQL Fails.

No matter how much I try to remember all of this, I’m still the sort of person who gets that flip-flopping stomach feeling when I mess up or can’t figure something out right away. It’s in moments like these that I have to take a breath and remind myself that I’m still learning. SQL Server is a very complex piece of software—so complex that I doubt there is any one person who knows EVERYTHING about it, not even the people who’ve worked to develop it over the years.

Failure is always an option. As a SQL Server DBA, you’ll soon learn that your client’s servers will fail—no matter how good you are at your job. How will you identify those failures? Here’s one way.

There’s Your Problem

SQL Server has a bunch of built-in tools and resources that help identify failures and errors. Having said that, there’s a learning curve involved that’s sort of like baking a loaf of bread. The components are all there at your fingertips, but you need someone to show you how to use them in the correct properly.

One of the scripts I run daily, the Read Errorlog script, has a bit of code that looks like this:

--Dump all the things into the table
	insert into #Errorlog
	EXEC sys.xp_readerrorlog 
	0 -- Current ERRORLOG
	,1 -- SQL ERRORLOG (not Agent)

What’s relevant here is understanding WHAT is being read when this script is run. I’m not all that concerned today with showing how we massage the output, just where the information is coming from.

The line EXEC sys.xp_readerrorlog is executing a widely-known but undocumented Extended Stored Procedure. This is why you see “xp” in the scriptlet. If a regular Stored Procedure were being executed you’d see “sp” instead.

NOTE: You’ll see the following message at the top of the MS Docs related to Extended Stored Procedures: “This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use CLR Integration instead.” We will probably need to rewrite our in-house scripts some time in the future to stay current.

Error logs are not stored in the database, but rather in text files on the host server. So, this Extended Stored Procedure looks outside of SQL Server to where the error log text files are stored within the hardware environment.

What is the procedure reading? xp_readerrorlog is pulling information from the files you can find in the Object Explorer under Management–>SQL Server Logs (highlighted in green). The Extended Stored Procedure helps make our lives as DBAs just a little bit more efficient by pulling the relevant information from the text files for us rather than forcing us to view each individual log file and scroll through hundreds of lines of results.

What’s also important to note here is what our version of the Read Errorlog script is NOT reading. Notice the Error Logs folder highlighted in red in the Object Explorer. You can find it under SQL Server Agent–>Error Logs. We don’t care about those error logs for this particular task.

xp_readerrorlogs accepts several parameters. The two we use most often are the Log Number and Log Type parameters.

The Log Number parameter we pass is “0”, which tells SSMS to read the current log. The Log Type parameter we pass is “1”, which tells SSMS to read from SQL Server Logs (green) and NOT from the SQL Server Agent Error Logs (red).

Beyond this, we are then able to use our script to tell SSMS what data we’d like displayed from the logs, rather than having it output every single line. For instance, if we’re specifically looking for deadlocks, our SELECT statement can be written to only look for LogText like ‘%deadlock encountered%’.

That’s all for this week. Join me next time for the next episode in The Ironic DBA Files.

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