T-SQL Tuesday #44 Wrap UP

Hello Dear Reader, what a busy week we’ve had last week!  I’ve got 3 big Thank You’s that I would like to give. 

First off I’d like to say Thank You to Adam Machanic for allowing me to host T-SQL Tuesday #44.  When I first started blogging, I participated in T-SQL Tuesday to keep myself writing at least once a month.  Hosting one seemed like a very far off goal at the time.  Thank you Adam for coming up with the concept and helping to promote growth for all of us bloggers out there!

Secondly my SQL Family, SQL Friends, and fellow Bloggers.  Without you writing there is no content.  You put your hard earned time into this effort and I Thank you.  Not gonna like I’m getting a little verklempt! 

Lastly Dear Reader, Thank You.  Without you all we may as well not put words to digital paper.

The subject was Second Chances and I was very impressed with the blogs, all around great job everyone.   18 spectacular blogs all about different types of Second Chances.

Boris Hristov (@BorisHristov | Blog) Wrote about the need to surround yourself with individuals that are open to change in their lives.

Mikey Stuewe (@SQLMickey | Blog) Discuses the opportunity to redesign a data model and correct some things via Normalization, which lead to a win all around!

Koen Verbeeck (@Ko_Ver | Blog) Wrote about free time on the job, and how he would approach it now vs. years ago.

Jeffrey Verheul (@DevJef | Blog)  Talks about frustrating co-workers and finding humility by being the co-worker that also makes mistakes.

Rob Farley (@rob_farley | Blog)  Discusses getting better over time, and takes a look back at some of his early blog posts.  Delivered in the way that only Rob Farley can!

Jes Schultz Borland (@grrl_geek | Blog) Blogs about her first experience with SQL Server Clustering and the lessions she’s learned along the way.  She also has the funniest picture of the day, I believe people stopped and looked at me as I laughed.

Kathi Kellenberger (@auntKathi | Blog) Tells us about all the different types of HA/DR that we can utilize for our SQL Server so we can give it a Second Chance!

Steve, our resident Aussie/DBA in Exile, (Blog) talks to us about some of the many different mistakes made.  I believe my groan and the words “ouch” may have audibly escaped my lips stirring my cube mates when I read about the SAN bullet point.  Most importantly he reminds us to celebrate failure as well as success, and how to do it with a sense of humor.

Joey D’Antoni (@jdanton | Blog) A mountain of a man, with a stare that could straighten out T-SQL at 200 paces, tells us all about the moment you realize that you’ve done something wrong, and a time where a QA task was run, but not against QA.  Probably the best quote of the day goes to him “the bead of sweat moment”.  Beautiful description of a feeling that all of us have either had, or will have.

Chris Yates (@YatesSQL | Blog)  had a funny blog about being a young developer and how to avoid those darn message boxes.  A little MacGyverism was in play here.

Oliver Asmus (@OliverAsmus|Blog) shares with us a story of woe when he was a Junior DBA.  It involves a Delete statement that has a where clause, but only the delete portion was highlighted.  The dreaded where clause, we know thee well.  Fortunately a Sr DBA was there to help out and Oliver adds some nice thoughts on explicit transactions.

The always excellent Robert Pearl (@PearlKnows|Blog) gives us some Pearls of Wisdom on sending emails.  In our youth we tend to fire them off rather quickly, sometimes that leads to trouble.  This is great advice for anyone in the business world with an email account!

Lance England (@LanceEngland|Blog) reminds us of all the things that can go wrong when we leave our cell phone at our desk during lunch….. and kick off a large update transaction on prod…… and do not execute a commit or rollback.  The most important part is once you make the mistake how you learn from it.

Stuart Moore (@napalmgram|Blog) reminds us that it isn’t just mistakes that we want a second shot at.  There are quite a few where you did a good or even great job but you know you could have done better with more time.

Martyn Jones (@MartynJones|Blog) Wrote his FIRST T-SQL TUESDAY Blog this week!  First off Thanks for Joining the Party Martyn!  Martyn takes us through an exercise in making sure you’re executing your code in the right environment.  Great Point!  I always like to check SSMS to validate my environment, once bitten twice shy.

Tracy Boggiano (@TracyBoggiano|Blog) tells us all about a mistake that taught her 3 important things that every DBA should have!

The Editor and Chief of SQLServerCentral.com Steve Jones(@way0utwest|Blog) weighs in on SQL Slammer and the havoc that can be done by not patching.  Ahhh 2002, we all remember you well!

Tamera Clark (@TameraClark|Blog) insightfully talks about how second chances are not just for SQL, finding a new career and a place that you are happy is a second chance worth seeking!

This guy named Bradley Ball (@SQLBalls| you know the blog) talked about how to crash production in 30 seconds, and how it was an opportunity to learn about a bug and warn the rest of his team.

WRAP IT UP 

When you look at the people that participated this past month you see MVP’s, an MCM, Consultants, DBA’s, and none of us are perfect.  Mistakes are a part of life, and it isn’t the mistake that is important but how you handle it.

The next time you do I hope you remember that, take a deep breath, and just keep going.  That Second Chance to do things differently will be just around the corner.

As always Thanks for stopping by.

Thanks,

Brad

T-SQL Tuesday #44 How to Take Down Prod in 30 Seconds

Hello Dear Reader!  Welcome to my blog on T-SQL Tuesday #44 Second Chances.  I’m hosting this month, and we are writing all about second chances.  My second chance comes from the not to distant past.

One day the DBA team was given a toy frog as some part of swag from a vendor.  We did what any group of grown men would do.  We put a dunce hat on it.  We decided that whoever screwed up next would have it sitting on their cubical wall, and we would pass it around as the next offender appeared.  A fun little way to pass the time and rib one another.  

No sooner had I participated in developing this badge of shame, than I earned it.  The title says it all.  How to take down prod in 30 seconds, but I should clarify.  Not some, not half, but allllllll you’re clustered servers in just 30 seconds.

I have to give a special Thank you to my buddy Dan Taylor (@DBABulldog | Blog), you see I remembered I had the frog.  I had forgotten what I had done to earn it.  It was sitting on the edge of my mind, but no matter how hard I tried I could not remember it.  It was sitting in a fog just out of reach.   An itch that I couldn’t scratch.  A few words out of his mouth and it all came flooding back.  As a good friend we've swapped many stories over the years, without his memory (which is better than mine) I would have had to go with a less interesting tale of woe.

“So Balls”, you say, “How did you screw up?”

Well Dear Reader I had an unfortunate convergence of unexpected anomalies that peaked in a spectacular crescendo of a mistyped password.   Yes a mistyped password.  My second chance would be typing in correctly.  The next best thing is explaining it so you hopefully never have to feel the same pain.

I SOLEMNLY SWEAR I AM UP TO NO GOOD

I had a new production SQL 2008 R2 Instance to install.  Things were going pretty smooth.  I got up to the screen where you punch in the password for the service account, and that’s when it all went wrong.  

I mistyped the password.  GASP, SHOCK, AWE, OTHER SUCH EXPRESSIONS!!!!!

Normally I would agree no big deal, but the next time I punched in the password I didn't get a password error, I got an error informing me that the account was locked.  Enter the series of unfortunate events.

Imagine you live in a world where all of the Prod servers are using the same service account.  Imagine that you've suggested this be changed but it ended up on the “That’s a good idea we’ll tackle that another day” pile.  Imagine that you are not using Microsoft Clustering for your Clustered servers, and that the inventive Server Engineers rolled their own “health check”.  Imagine that your current password policy locks out when you mistype the password somewhere between 3-8 times.

“But Balls”, you say, “You only typed your password once?  Not 3-8 times!”

Exactly.  There’s a bug in the installer for SQL Server 2008 R2.  When you click the next button after filling out the service account information, you authenticate at least twice for every account you type in.  Not so in SQL 2005 or SQL 2008 (not R2).  But in SQL 2008 R2 one mistyped password counts a whole lot more.  Depending on the services being installed, enough to lock out an account.

Then you are left to watch the manual health checks fail because the account is locked out, attempt a cluster failover, only to be locked out on the other side because the SQL Service account was locked out.

You catch your error quickly.  Run to the Team Lead, report what has happened, hoping this can get fixed before the inevitable outages begin.  Then you race back to your desk.  You have an uncomfortable phone call to place to the help desk.

Imagine that while this unfolds you are waiting on hold for the help desk to open a ticket (you have to follow protocol), that will get assigned to an engineer, who will pass it on to AD Services.  Queue the uncomfortable elevator music.

Co-workers scrambling in the back ground, like the bull pin of a busy newspaper.  Someone is keeping an active wipe board of what servers are now down, every minute someone in your cube starting to say “Have you….” Only to be cut off by your response “Still on Hold”.  Queue the music.

Other co-workers are fielding calls from App Teams reporting that their applications are offline.  Other co-workers trying to reach managers that can bypass a well-orchestrated bureaucratic separation of duties that results in elevator music while you are still on hold. Did I mention being on hold?  While on hold forty-five minutes can feel like weeks.

The saving grace (for my job), the bug I found was easily to duplicate.  It was easy to see that this behavior was not in previous versions.  As an added bonus those service accounts started becoming unique real quick.

DEMO: THE BUG I LEARNED ALL ABOUT

We’ll skip ahead a bit.  Say you are installing SQL Server 2008 R2.  We’ve gotten up to the Server Configuration where we are punching in our passwords.  First let’s open up our Event Viewer, click on our Security Tab and clear it out. 

*If this were anything other than my personal VM I would backup the log so we could restore it, do not clear out a security log on a prod server without proper guidance.

Now the only event in our log is the event denoting that our log has been cleared.  Back to SQL Server. 

 

We will click on the Use the same account for all SQL Server services button and type in our .\s-sqlsrv service account.  Definitely not following best practices here.  SQL Engine, SQL Agent, and SSIS all getting the same service account. 

Let’s Type the password in wrong and see what happens?  Click OK.  Click Next.

SQL reacted just like we thought.  Theoretically we should have 1 bad login check right?  The same user name was in use, we don’t need to validate it 3 more times.  One should do.  Perhaps at most we’ve got three validation checks right?

Let’s head over to our trusty error log and see. 

We’ve gone from 1 to 13 errors in the click of a button.  How many failed logins do we have?  Not 1, 2, 3, 4, 5, 6, 7, but 8 failed logins from one attempt.  You’ll get this if you use the button or if you do not use the button.

You may be asking did this get fixed in SQL 2012?

One look at the installer and you can see the button is gone.  Let’s punch in the same service account name and an incorrect password.

And now on to our error log.

Wow!  Six entries, now we are looking at 3 entries per account.  Nope didn’t get any better.

WRAP IT UP

Long story short, make sure those passwords are correct.  Personally I like to use a utility like KeePass to generate, store, and copy my passwords from.  Anything that keeps me from typing.  Or as the case may be mistyping J.

As always Dear Reader, Thanks for stopping by!

Thanks,

Brad

SQL Saturday Orlando: LAST CALL for Speakers

Hello Dear Reader!  I just wanted to write to Thank All of you for the submissions to SQL Saturday 232 Orlando.  This has been a crazy event and we are still several months away.  

Everything started out as it normally does, Karla Landrum (@karlakay22 | Blog),   leading the way pulling a motley crew of Shawn McGehee (@SQLShawn | Blog), SQL MVP Kendal Van Dyke (@SQLDBA | Blog), SQL MVP Andy Warren (@SQLAndy | Blog), Ben Cork, and myself behind her.

We hit a hiccup early on.  Our venue wasn’t going to be available on the date we had originally announced.  Unexpectedly we had to shift our date.  Some speakers couldn’t make it, and it brought us into conflict with other SQL Saturdays that some speakers had committed to speak at.  At that time we put out a very public call for speakers.

There's Still time to get a seat at our table!

The response was overwhelming!  So overwhelming that we are closing the call a bit early.  The call for speakers will end on July 10^th and we hope to have the schedule out within a week or two after that.  Speaking with Rodney Landrum, my speaker committee co-captain, our goal is still the same.  No speaker will get turned away.

An essential part of SQL Saturday is to provide free training to the community.  Equally important is to help grow the next generation of SQL Server professionals who will be our speakers.  Look no further than myself to see proof of this.

So Dear Reader, get those abstracts in, because we’ll expand the number of rooms to fit you in!  Get ready to be part of the biggest SQL Saturday Orlando Ever!  Besides You know you want one of these!

As always Thanks for stopping by.

Thanks,

Brad