Monday, September 7, 2015

[T-SQL Tuesday] It's time to discuss "Strategies for Managing An Enterprise"

T-SQL Tuesday

Jen McCown is hosting this month's TSQL Tuesday . The topic for T-SQL Tuesday #70 is "Strategies for Managing An Enterprise".

That's easy: relentlessly use automation to know & improve your environment.  
Thanks, I'll be here all week.  Try the veal.  

In all seriousness, though, that's been my career - being a Lazy DBA (spending far too many hours to accomplish that) via automation.  I actually got fired from a job because they didn't know what I did - it all Just Worked, and I wasn't running around like things were on fire, so I must not be doing anything important.  (Yes, seriously, and the skills I gained meant that the next gig was a LOT more fun and a lot better overall).  It honestly doesn't matter what you use.  Over the years I've used batch files, Perl, shell scripts, SSIS, PowerShell, T-SQL, other peoples' tools... anything and everything.  The tools don't matter -it's the mindset.  Why are you spending 10 minutes a day doing X?  Spend an hour automating it, and then take those 10 free minutes a day and find more things to automate.  Seriously, it's a win-win.  You learn new skills, you gain experience with different topics, you better know your environment (why won't THIS server return the data the same way?), it makes your job less repetitive more interesting, and therefore life is more fun.  

There are even a couple different XKCDs that cover different aspects:
(though this can hold true )

In fact, it's the question I try to ask when interviewing: what have you automated lately.  Why?  How?  Heck, even if it's just automating server installs, it's a game-changer.  Not just for speed, but for consistency.  No more wondering if that setting was changed on ALL your servers, for instance. 

Now, fortunately for you, there are plenty of people on the Internet who relentless automate things, and have the graciousness/convinction to share.  So what things could/should you already be automating for free?
  • SP_Blitz - written by Brent Ozar and associates, it's a one-stop shop for "you're doing it wrong".  Runs quickly and easily, and tells you what it thinks you're doing wrong.  
  • Cleartrace - written by Bill Graziano, this tool has a command-line option, so you can import traces into a database automatically.  Why do that?  Well, say you have a customer-facing site, so you can look historically and see what queries y
  • SQL Server Maintenance Solution - throw away your crappy MS-built "maintenance plans" and get something with some OOMPH.  Backup all your databases simply, automatically reindex/defragment, and do integrity checks.  Just customize the jobs (and, to be honest, most people could just set up schedules and be done - that's how good the code is)
How can you go wrong?  

Finally, I'm not above self-promotion.  And hey, more free tools.  What automation do I have on this blog? 
1) Event Notifications:
What's this do?  Tells you what code has changed in your environment, how, by who, and when.  I love this ability - we've used it from everything from tracking down failed logins, to checking code into TFS so that devs can see what production _actually_ looks like.  (Oh, and it's a native SQL Server feature, works on anything from SQL Server 2005 Standard Edition on up... and it's free!).  I use this almost every single freaking week.  Which is either awesome or sad - but I'm going with AWESOME.  It improved my skills by at least 1 point on a 10-point scale, both by getting it set up, and because it means you're not playing detective trying to figure out what has changed.  Cannot say enough good things about this.

2) The EPR (Extensible Powershell Repository).
How's this?  Create a T-SQL/Powershell script, drop it in a folder, and within a few minutes you have the results from all your servers, saved to a table.  Bonus: options to keep history.  We've been using one of the reports lately to decommission processes.  (though I have a couple dozen in production, all of which help with specific cases).  The best part of it, honestly, is that you wonder "huh, I wonder how bad a problem this is", write the script in 10 minutes, drop it in the folder, and come back in a week/month and see how things look.  And then realize that knowing more helps you have a smoother environment.

3) Eventlog_Tracking:
The latest, and I'm presenting on it in Kansas City on October 3rd!  (Now I just have to finish writing it...)  Using PowerShell and WMI, capture the latest event logs from the Windows Event Logs.  In our environment it winds up helping with both the SQL and non-SQL sides of things,  (Yes, there are apps.  No, I didn't want to muck with them.)

Anyhow, thanks again for the idea, Jen!  


No comments: