T-SQL Tuesday #105: Brick Walls & Asking For Help

Welcome back to another edition of T-SQL Tuesday! This month’s edition is hosting by Wayne Sheffield (b|t), who asks participants to blog about a time they hit a brick wall.  Funny enough, I feel like I’m up against a brick wall, with everything going on in my life right now!  Was challenging enough to try to find the time to write this blog, but that’s another story for another time.

Flashback

A couple of years ago, when I was in an Operational DBA role, we were faced with a division wide domain change effort due to an acquisition.  We had numerous SQL Servers that had to be flipped from Domain A to Domain B.  Before this, I never even dabbled in Active Directory, so all of this was completely new to me.  We didn’t even have an Active Directory specialist on our Ops team (those folks were remaining behind in the acquisition), so we had to bring in a consultant to help us (who was awesome by the by).  We were doing things in a phased, but very rushed timeframe, so had little time to prepare, much less train up.

Leaving out names to protect the innocent (hah), we cut over our first set of Production servers.  Then I started seeing an error that I’ve since come to dread:

“Cannot generate SSPI context”

What… the… ?!

Kerberos Double Hop

I may have created this meme out of frustration… and may have tweeted it out too

To make a long story short, this had to do with Kerberos and the new domain service account that SQL Server was running under.  But I didn’t know that then, and this became my brick wall.

Much Google-fu and cursing later, I finally started getting somewhere, finding some articles on the topic.  This one in particular had context, though I still didn’t understand the underlying issue.  But what I did notice, at the very end of the article, was what saved my bacon.

“Posted By: Adam Saxton

Hey, wait a minute.  I’ve met Adam in passing at some event or other.  I think I follow him on Twitter too!  So I took a chance and reached out to him.  That resulted in an e-mail exchange, where he pointed me to other resources about Kerberos & SQL Server, and helped me and my Ops team fully understand the issue.  He answered questions and was a phenomenal resource, that saved by team’s bacon!  I was blown away.  Adam, a guy at Microsoft, was directly answering questions and helping us out for free.  My non-SQL Server Ops colleagues were also equally impressed and grateful for his insight and guidance!

Moral of the Story

If you’ve hit a brick wall, absolutely do your due diligence and try to find the answer, but don’t be afraid to also reach out and ask for help – even from “big guns” at Microsofth.  You can only go it alone for so long, before the time burned searching for the right answer outweighs the consequences of not asking sooner or asking too soon.  #SQLFamily is here to help!

Cannot generate SSPI context – TL;DR

P.S. If you came upon this blog because you were searching on that SSPI error message, do yourself a favor and grab the Kerberos Configuration Manager tool for SQL Server.

Advertisements

T-SQL Tuesday #104: Do You Have a T-SQL Toolbox?

Hello everyone!

Am back on my personal blog with another edition of T-SQL Tuesday. This month’s edition is hosted by Bert Wagner (b|t) who has asked us to share “Code that you would hate to live without.”

For me, my “can’t live without” is not a single snippet of code, but rather an entire folder of scripts, that has traveled with me from job to job over the last +10 years. Inside it, I have numerous scripts that I have written, collected, and adapted throughout my career as a DBA & DB Dev. Here’s a snapshot of my Toolbox folder!

 

Perusing the list, it is mostly a mix of what I categorized as Tools and Examples.

I keep Tool scripts separated out and appropriately labeled so I can jump to them quickly when needed.

Examples store various snippets of useful code, that I’ve just failed to fully memorize despite using on a regular basis. It’s frankly just easier to pop open my appropriate Examples file, copy & paste, and go on my way. I’ve considered replacing these with SSMS snippets or templates, but have usually opted not to because of having the hassle of deploying them to shared jump servers.

The other notable file is the 1_Scripts.sql file. That was my original toolbox, that contains numerous other T-SQL examples that I never bothered splitting out into individual Example files. I think of it as my most used and it contains snippets for a variety of random things.

 

Do you maintain a toolbox of your favorite scripts today? Well, do you ever find yourself having to re-look up the exact T-SQL syntax for something that you happen to use just often enough, but not often enough to memorize? Then give this a try!

 

Interested in hosting your own T-SQL Tuesday?  Steve Jones has taken over, so reach out to him!

T-SQL Tuesday #91 – What DevOps Is & Is Not

Welcome to another edition of T-SQL Tuesday. This month’s blog party is hosted by Grant Fritchey (b|t). His mission, should we choose to accept it, is to write about DevOps.

Let Me Tell You A Story

Allow me to share my perspective and anecdotal experience. When I first heard about DevOps a few years ago, fellow operational IT folks were up in arms. Why? I asked what the big deal was and the answer I got was “DevOps is going to eliminate the need for Testers & Operations. Developers will control and manage the entire stack, end-to-end.”

Well, the reactions to that message ranged from contempt & ridicule, to job loss terror. I heard many who felt threatened. The perception was that the expertise of Testers and Operations was being marginalized by Developers who evangelized DevOps. Somehow that message was being circulated, and it was downright arrogant and foolhardy… and fortunately not an accurate picture of what DevOps strives to be.

That’s Not How I See Things

My take on DevOps, is that it is all about moving away from a “we do this, you do that,” siloed mindset of operating. It is about breaking down barriers of communication and creating tighter integration. The world of DevOps still requires specialists – that will never change. I see DevOps as pushing the three fundamental groups of software development: Developers, Testers, and Operations, into integrating far more closely together than they ever did before.

How?

In the world of DevOps, an Operations team might utilize a monitoring tool that feeds useful directly back to Developers and Testers. Developers & Testers may cross train, so both learn how to effectively write automated unit tests. Developers & Testers could cross train with Operations, to improve application deployment automation processes.

These examples all share one common theme – teams reaching outside of their traditional skill boundaries, to actively engage, learn, and integrate. This active engagement is what has often been missing from traditional operations.

 

This is what I believe DevOps is all about. The tools and processes being pioneered today help all of us build better, more stable software, which is better for all of us.

Coming Full Circle to Start A New Chapter

They say that there’s a time in one’s life where you “come full circle” and you find that you go back to where things started. For me, one of those places, where many things started for me, is Charlotte, NC. In October, 2013, I attended my very first PASS Summit in Charlotte. In fact, I started this blog the day before I left for PASS Summit 2013! (SQL Family Got Me Here)

So how have things come full circle? Am I moving Charlotte, NC? No. But I will soon begin a new chapter of my SQL Server career, with a Charlotte based company.

I am honored to have accepted an opportunity with SentryOne. As of June 19th, I will be joining the SentryOne family as a Senior Solutions Engineer! The way I look at it, this is an opportunity where I’ll be mixing teaching, presenting, and consulting-lite.

What is even more mind-blowing to me, is not the position itself, but the opportunity to work for SentryOne. I was first exposed to them, back when they were SQL Sentry, as a SQLskills IE1 attendee in May, 2011. I was extremely impressed, not only with their tools, but with their community focus. I’ve also been a customer, having worked several different jobs that used their monitoring products, and built up a lot of respect for the company and what they were doing. And I have been extremely fortunate to become friends with a number of long-time SentryOne employees over the years.

It is humbling to have been given this opportunity to be join their ranks. I can’t wait to see what comes next!

Describing a SQL Server Wait – Like a Date

I wanted to blog about SQL Server Wait Stats today, but from a bit of a different angle than your normal Wait Stat blog.

I often find myself being shown TOP X wait stats output and being told “OMG, these are horrible, we need to fix these!!!” And I stop them, counter with a “no, not necessarily, let’s dig deeper. Are these waits abnormal?” “Abnormal? SQL Server has waits, that’s always bad, right?”

If you’re already familiar with SQL Server Waits, you will know that SQL Server is always waiting on something. And that’s perfectly okay. A few months ago, I was driving home from SQL Sat Cleveland, and came up with a fantastic analogy that I thought would describe these waits rather well.

Pretend two people, Vanessa and Wade, are on a date and having a conversation. Vanessa says something, then Wade responds, and their conversation continues back and forth. But between each person’s statement, there is a very brief moment of silence. That can be thought of as a “wait” – the other individual is processing the prior message and formulating a response. And in the context of this conversation, these waits are perfectly normal.

Now, let’s pretend that Wade says something really, really stupid. It actually shocks Vanessa, who is speechless. Instead of responding immediately, she waivers and remains silent. A long period of time passes before she responds to Wade. This particular wait is abnormal! And so goes a SQL Server wait.

Okay Andy, does that mean that if my SQL Server waits all have long timeperiods, that those are all of the bad ones? No, not necessarily either. In the example of Vanessa and Wade, yes, because their normal cadence of conversation is very rapid with only brief waits. But let’s look at a second example.

Pretend you have two grumpy old men, Jack and Walter, fishing out on a lake in a boat. It’s quiet and they’re grumpy, so they’re not saying much. Walter might make a comment about the fish not biting, and Jack may respond after a lengthy period of time with a brief response. After a long period of silence, Jack may comment about his beer being empty, and he asks Walter for another. Walter fails to respond, because he’s dozed off. And only after several minutes does Jack actually turn around to see that Walter has dozed off, and gets a beer for himself.

If we take a step back, think of each couple as a server’s workload. What does your workload look like normally? It is difficult to know if a given SQL Server Wait time period is anormal if we don’t know what our times look like under normal circumstances. This is why many stress the importance of baselining your SQL Servers.

So the next time you find yourself explaining SQL Server Waits to someone new, feel free to use this analogy to help illustrate why waits in of themselves are perfectly normal, and what a good wait vs a bad wait may look like.

T-SQL Tuesday #88: Surprise – Those Aren’t Test Accounts!

Welcome back to another edition of T-SQL Tuesday. This month’s host is Kennie Pontoppidan (b|t), who has invited participants to share a WTF story!

A few jobs back, I worked for a software firm. Our system had a complex system of “accounts” which were tied to client companies and users.  And our users could have multiple different accounts through which data would get routed to “make widgets.”  Without going into details, these accounts were simply like an individual’s savings account – they were fairly complex entities which were simply labelled as “accounts.”

My team had been wanting to clean up obsolete account data stored in our account manager database for a terribly long time. Aside from just raw storage, having obsolete accounts lingering in our system had numerous other consequences which made reporting and other things terribly irritating. But our business was always extremely afraid to ever delete anything – so we had account data for everyone we ever had, etc.

As a “pilot,” they agreed to let us finally delete several hundred internal-only accounts. These accounts were obsolete, only used for demo, and were not associated to any client companies, just our company.  As the point person for making this change, I put together a complex T-SQL script to manually remove all of these accounts from the various tables of our database system. But what I also did was put together a backout/restore T-SQL script that restored all of those accounts. I did this out of paranoia and lessons learned from prior experiences. This way one could run my delete script & my restore script repeatedly. The QA team tested my scripts for 1-2 weeks and we were finally given the go-ahead to clean out the stale records.

Monday morning came along and as scheduled, our Production DBAs executed my script before start of business. 30 minutes later, the frantic calls started to reach us. Seems some of our clients could no longer “make widgets!” Accounts that they needed to route data were gone! My manager and I looked at one another in horror – we were only deleting internal accounts!!! We didn’t hesitate and immediately had our Prod DBAs back out the change with my backout script, before the rest of the United Stated started business. The backout was executed immediately and all was back to normal, but business folks were pissed and wanted to know what happened.

As we dug through things, we came to discover that some of our implementations folks had jury-rigged some of their implementations. To meet customer requests for unsupported features, they utilized our company’s internal demo accounts! WTF!!! We in development were furious. Their use of the internal accounts was not documented anywhere, but the data trail didn’t lie.  And these clients weren’t in QA, which is why they never picked it up either!

Moral of the story? Have a robust restore plan. Sure, our systems were fully logged and we could have executed a point-in-time restore. But for a scenario like this, having an immutable data restore script, that was fully tested and ready to be executed, was far faster. It allowed our business to get back online almost instantaneously & saved my bacon.