This month’s T-SQL Tuesday is by Andy Leonard, who asks bloggers to answer ‘What is Your “Why?”‘
To answer that, I’m going to answer a different question first. This is a very typical question that I get in interviews, that sort of drives me nuts.
“What do you see yourself doing in 5 years?”
Uhh… working unless I’ve won the lottery? And hopefully not working in a job that sucks? :-p
Smart ass response aside, what I always say to this is that regardless of the job/role that I’m in, I want to continue doing one key thing – helping companies leverage SQL Server to its fullest potential.
I think back to some of my past jobs, and what I’ve done to help both companies. At one company, I was brought in because of a SQL Server based system had outgrown its original architecture and performance was beyond dismal. At another company, I became a liaison to a primary SQL Server customer, in part thanks to my personal drive to remain on the cutting edge of what Microsoft was doing with SQL Server.
But I’ve also tried to help individuals too. And in all of my recent roles, I’ve made sure to evangelize the SQL Server PASS community, and all of the phenomenal training and resource offerings that can be found in our awesome community.
All of this, because one of my core characteristics is that I am a “Giver.” I want to help.
Today, I am proud to be a Solutions Engineer with SentryOne. In my current role, I’m “evangelizing” SentryOne’s monitoring software, because I personally believe that it is a true solution that can improve the lives of SQL Server data professionals.
Would me, 5 years prior, have been able to predict that I’d be working for SentryOne in such a role? No way. But am I still staying true to my overarching goal of helping others leverage SQL Server more effectively? Absolutely!
And that’s why I do what I do.
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.
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
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.
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!
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.
Welcome to this month’s T-SQL Tuesday Round-Up! A few weeks ago, I sent out a call for bloggers and must say that I’m utterly blown away by the response. A whopping FORTY bloggers responded last week with contributions for Growing New Speakers! Four – zero! You people are all amazing!!!
I’ve decided to split the list of contributors into three groups.
- New Speakers: Those who have never presented before!
- Novice Speakers: Those who have presented just once, just a few times, or perhaps long ago in a galaxy far, far away. Generally if you’ve only spoken a handful of times, been speaking for less than a year, and/or self-identified as a new-ish speaker, you were grouped here.
- Experienced Speakers: Those who are seasoned speakers.
- Andrew Pruski-First Foray Into Presenting: @dbafromthecold
Shares story of first presentation
- Angela Tidwell: @angelatidwell
Writes about things learned at PASS Summit about presenting
- Arun Sirpal-SQL Server – Reconfigure: @blobeater1
Explores RECONFIGURE in a technical blog for a first presentation
- Chris Voss: @ceedubvee
Writes about upcoming first presentations and muses how to progress further
- Constantine Kokkinos-Exploring DBA Tools or: How I Learned to Stop Worrying and Love PowerShell: @mobileck
Blogs about DBATools & Powershell for first presentation
- Deb Melkin-If I Were a Speaker…: @dgmelkin
Explores three ideas for a first presentation
- Devon Leann Ramirez: @restinbeachface
Writes an introduction for first presentation
- Jo Douglass: @jodouglass
Writes about 1st presentation topic idea: surrogate keys as a DW/ETL anti-pattern
- Michelle Haarhues-On the Doorsteps: @mhaarhues
Explores Women In Technology and first presentation about women presenters
- Robert Preseau: @robertpreseau
Writes about overcoming mental obstacles to speaking
- Scott Millard-Taking the next step – becoming a speaker: @leftseatsql
Accepts the challenge to speak
- Tywan Terrell: @tyawnterrell
Writes about T-SQL fundamentals as first presentation topic
- Anthony Nocentino-Public Speaking – The First Time: @nocentino
Shares an updated recap blog from first presentation
- Bjoern Peters-Become a Speaker at a PASS Event or other Meetups – share your knowledge: @sql_aus_hh
Writes about elements that make for a good presentation
- Chris Lumnah: @lumnah
Reflects on lessons learned after first presentation
- Chris Sommer: @cjsommer
Shares things to think about when writing a new presentation
- Kenneth Fisher-Zip to Speaker: @sqlstudent144
Recaps prior blogs about speaking & offers encouragement
- Kevin Hill-Speaking & Presenting: @kevin3nf
Explores DBA Basics for Non-DBAs as a “first” presentation idea
- Matt Gordon: @sqlatspeed
Shares two key lessons learned
- Mike Kane: @tcp1433
Explores target audience, topic, and format
- Monica Rathbun-Helping New Speakers: @sqlespresso
Writes how to get started
- Wylie Blanchard-Use Video as a Tool to Enhance Speaking Skill and Create Content: @wylieblanchard1
Discusses value & benefits of videotaping one’s self
- Alexander Arvidsson: @arcticdba
Writes about body language
- Andy Yun-Building Your Slidedeck: @sqlbek
Shares slidedeck building tips
- Brent Ozar-The Three Parts of Every Presentation: @brento
Explores types of presentations
- Derek Hammer-Building an Hour of Content: @sqlhammer
Shares advice in building your first hour long presentation
- Doug Lane-Conquer Your Fear of Presenting with a Gift-Giver’s Mentality: @thedouglane
Presents a video blog about the Gift Giver’s Mentality
- Erin Stellato: @erinstellato
Recaps 4 prior blogs about speaking & addresses topic of comfort
- Jes Borland-Dealing with Failure: @grrl_geek
Discusses how to handle failure on the fly.
- John Deardurff-Speaking about SQL: @john_deardurff
Shares tips about learning itself
- Kathi Kellenberger-New Speakers: @auntkathi
Writes about how to field questions
- Lori Edwards-So You Want to Present: @loriedwards
Builds on prior blog post about speaking, sharing advice learned since
- Michael Swart-I’m Trying To Kick My “Undo Button” Habit: @mjswart
Writes about speaking at PASS Summit for the first time
- Mike Fal-Getting Ready for your Presentation: @mike_fal
Shares how to prepare the day of your presentation.
- Mike Lawell-Speak in Public? What? Me?: @sqldiver
Shares journey about starting speaking.
- Riley Major-No One Wants to Eat You: @rileymajor
Shares his personal story of speaking, followed by a huge list of tips, tricks, and resources
- Rob Farley-How I Prepare for a Presentation: @rob_farley
Writes about deeply exploring your topic
- Rob Sewell-Speaking? You? Go on.: @sqldbawithbeard
Shares advice about getting into speaking
- Steff Locke-A note to (potential) new speakers: It’s ok not to be perfect!: @stefflocke
Reinforces that perfection is not necessary.
- Steve Jones: @way0utwest
Encourages readers to speak.
WHAT COMES NEXT?
As I pledged in the first blog post, I will now be reaching out to all New Speakers, to offer personalized feedback. A number of Experienced Speakers also volunteered to help, so I will be working out those logistics. Additionally, if any of the Novice Speakers would like the same kind of help, please contact me – I will add you to the list too!
There are numerous opportunities to present: internally to coworkers, Toastmasters, User Groups, SQL Saturdays, & Virtual Chapters. And all of them are always looking for new speakers! When will you speak next?
Welcome to another edition of T-SQL Tuesday. I am this month’s host, and the topic is Growing New Speakers. My contribution to this month’s T-SQL Tuesday was inspired via a tweet by Jonathan Cox (b|t).
When I gave my very first presentation, I had next to no experience with PowerPoint. In fact, I found it a bit intimidating. Where to start? How should I make it look? Do I have to pick a color scheme?
Not to worry! When you first open PowerPoint, there are numerous basic templates already loaded! My advice is to select a template that is simple. Don’t select one that has a background with very cluttered graphics. Visual noise is distracting and is the last thing you want to worry about.
For your first presentation slidedeck, there’s no need to get fancy. Keep things simple like the templates below.
Readability is very important. What may look fantastic on your 1920×1200 laptop screen that’s 2 feet from you, will look drastically different when it’s been downsized by a projector to 1024×768 and is projected to an entire room.
I readjusted a handful of my slides to pass this test (thanks Karen!).
Color contrast is important. Different rooms have different lighting conditions, so be sure to use a simple, high-contrast color combination so everyone in your room can read your slides. A lightly colored or white background with dark or black text works best. Stick with the basics – they work.
When it comes to amount of content, don’t write huge walls of text on slides. Put only your main talking points. Your speaking will fill in the corresponding details. And if you do have a lot of slide content, don’t hesitate to split it into two slides.
Images and clip art. Some like art, I generally shy away from it – it’s all personal preference. If you do use an image from somewhere, you must give credit. Generally a footnote is acceptable. I prefer to rely on Creative Commons 0 images. Makes life hassle free. Check out Kendra Little’s (b|t) blog post all about Easy Free-Use Images.
I hope this collection of tips helps you on your way to building your first slidedeck! And if you still find yourself a little stumped, Google/Bing is your friend! There are many out there, who have written numerous articles & blogs on how to build effective slidedecks. Leverage their collective knowledge.
Reading Chris’s announcement post, I thought back to a recent conversation regarding purchasing of tools, to make our lives as data professionals easier. Many of us work for fantastic companies, but may not have the budget or ability to invest in tools for us.
My challenge to everyone is to consider giving yourself a gift and and purchasing a tool for yourself. Think about what you do on a regular basis:
- Do you often find yourself comparing “identical” databases for drift or other reasons? Like making sure pesky developers didn’t sneak in yet another index without informing you first? Wouldn’t RedGate’s SQL Compare make your life more awesome?
- Do you dig through Execution Plans regularly? How about investing in SQL Sentry’s Plan Explorer Pro version? Because you’re already using the FREE version & know it’s awesome, right? RIGHT?!?
- Sling a lot of T-SQL code daily? How about Mladen Prajdić’s SSMS Tools Pack, which is chock full of awesome features that make a T-SQL dev’s life more awesome.
I will fully admit that the cost of any of these tools is non-trivial. But on the other hand, this is about giving yourself a gift. And this gift is hopefully something you’ll use on a regular, if not daily basis, in the course of your career. Consider the value that such a tool would bring to your life. So go ahead… treat yourself to a gift!
Dynamic Management Views & Functions (DMVs & DMFs)
What are these? It is said that one’s eyes are the gateway to the soul, I say that DMVs/DMFs are the gateway into SQL Server! Formally speaking, the DMVs/DMFs expose internal information about your SQL Server. This information can be used to learn about its current state & health, and provides valuable insight for troubleshooting and tuning purposes.
Much earlier in my SQL Server career, I never knew much about how SQL Server operated under the hood. I only had knowledge of the things I could do directly, but I never questioned what was really happening behind the scenes. Then I attended SQLskills IE1 training, which was a turning point in my career. Among other things, it was my first exposure & deep dive into SQL Server Internals. I became enamored with learning how things really worked under the hood and the DMVs/DMFs became one of my best friends.
Since their introduction in SQL Server 2005, the number of available DMVs/DMFs has more than doubled. How can you see what’s available to you on a given SQL Server? One quick & easy way is to query sys.system_objects:
WHERE system_objects.schema_id = 4
AND system_objects.type IN (‘IF’, ‘V’)
AND system_objects.name LIKE ‘dm_%’
Of these, what are some of my favorites?
Combining these two, gives me very useful insight into both the structure of a table & any indexes it may have, but to how my workload is actively making use of each of these structures. Data found here is extremely useful when it comes to performance tuning.
I like to use this DMV to get row count & page count information about a database. Its output gives me greater insight into the contents of a table, particularly without the overhead of a SELECT(1) for a quick row count or sp_spaceused execution. Personally I find knowing size by page count to be far more valuable anyway.
Mixing & matching these three, you can obtain information regarding current connections and activity on your SQL Server.
I can’t tell you how many times I’ve had a “eureka” moment, discovering that a DMV/DMF exposed another interesting piece of information that I wasn’t aware of before. To say that there’s a wealth of information is an understatement. So whether you’re new or not to DMVs/DMFs, take another look – you too may find a new and useful gem hidden inside them!
And if you want to read all about all available DMVs/DMFs, be sure to check out Books Online: Dynamic Management Views and Functions (Transact-SQL)
A couple of weeks ago, I posted an Invitation to T-SQL Tuesday #68: Just Say No to Defaults. Last week, #SQLFamily responded in force with 22 blog posts! There’s a fantastic wealth of information across all of these blog posts and I was blown away!
Without further ado, here’s the round-up!
- Aaron Bertrand (@AaronBertrand) discusses SQL Server Setup & “Installing All The Things!” (Should I add a trademark notation to that phrase for you?)
- Andy Yun (@SQLBek) explored SSMS & Keyboard Query Shortcuts.
- Angela Henry (@SQLSwimmer) explores her two two quick wins to improve performance.
- Björn Peters offers us a post in German, discussing Min & Max Server Memory. (Thank goodness for Google Translate!)
- Bob Pusateri (@SQLBob) shares some of his Defaults to Change, not only in SQL Server, but in Windows as well.
- Cathrine Wilhelmsen (@cathrinew) explores using Batch Update change defaults in SSIS packages.
- Chris Sommer (@cjsommer) discusses using Powershell & SMO and explores an example.
- Daniel Mellor (@sqlsanctum) writes about his top six defaults that he makes changes to.
- Jason Brimhall (@sqlrnnr ) takes an interesting twist, to talk about the one default we all share – ourselves. He also wrote a preview post the week prior, showing us how to track changes via Extended Events.
- Jens Vestergaard (@vestergaardj) shares his exploration of Powershell & Desired State Configuration and discusses how it can be useful for changing Defaults.
- Jim Dorame (@DBAJD) gives us an excellent checklist of six items that he always explores, including Model DB!
- Justin Randall (@jh_randall) takes a turn by exploring SQL Server Agent.
- Kenneth Fisher (@sqlstudent144) reminds us of the importance of documenting our changes, and explores what can be automated to aid that.
- Koen Verbeeck (@Ko_Ver) gives us a SSIS-centric post, talking about changes he recommends.
- Lori Edwards (@loriedwards) shares three of her Defaults to change.
- Martin Catherall (@MartyCatherall) writes an informative post about Backup Checksum.
- Mike Fal (@Mike_Fal) flexes his PowerShell with a detailed exploration of Desired State Configuration.
- Nancy Hidy Wilson (@NancyHidyWilson) writes about SQL Agent, and a setting she addresses after installation.
- Rob Farley (@rob_farley) writes about something I am guilty of rarely ever thinking about – Default Collation.
- Robert Pearl (@PearlKnows) took the time to expand on the topic from a higher level, offering a wide variety of topics & solutions for further reading.
- Steve Jones (@way0utwest) explores one of the default settings on his must-change list.
I want to thank everyone for participating and sharing your knowledge. And I hope that those of you who are reading, but not yet blogging, will join us, start your own blog, and begin sharing your knowledge and your stories too!
Until next time!