T-SQL Tuesday: My Automation Epiphany

This post is part of the monthly SQL Server blogging event known as T-SQL Tuesday.  Each month, a different SQL Server blogger “hosts” the event by suggesting a topic or theme and accepting trackbacks from participating bloggers from their posts on the topic.  Pat Wright (blog | Twitter) is hosting this month and has selected automation as the theme.


In late 2010, Jeremiah Peschka (blog | Twitter) reached out of my monitor and slapped me upside my head when he wrote…

“99% of what you do could be replicated by a fairly stupid shell script.”

– Jeremiah Peschka, T-SQL Tuesday – Why Are DBA Skills Necessary?

That’s a Bitter Pill

He was right.  Well, maybe not 99% but I had to be honest with myself and admit that I was doing too much automatable work manually.

It wasn’t that I hadn’t automated anything.  The first thing I had done as a fulltime DBA was automate our morning checklist, displaying the results in a series of SQL Server Reporting Services (SSRS) reports.  Since then, I had automated process after process using technologies such as SQL Server Integration Services (SSIS), C#, SSRS, SQL Server Agent, and even a bit of Windows Shell scripting.  That is pretty much what I do.  But I had failed in two major ways.

I had failed to recognize the opportunities to automate the little things.  The pesky little things that constantly pop up and take up your time.  Little things such as running a query against 60 of the 80 databases on two particular servers and consolidating the results in a single comma-separated value (CSV) file.  Little things such making a sp_configure change on 15 servers to enable the setting to Optimize for Ad-hoc Workloads.  Little things such as configuring a new instance of SQL Server.

I had also failed to equip myself with a best-of-breed scripting language for automation.  I had known for years that Powershell was the future, yet for me it still remained just that.  Why stop what I was doing and learn yet another way to get things done?  I already had T-SQL, C#, SSIS, etc.

I’m Better than That

Well, if a fairly stupid shell script could do 99% of what I do, well that was great news because I didn’t really like much of that 99% anyway.   I would rather learn a new technology or design a new solution to a problem than repeat mindless busy work that could be automated.  The best days for me are when I get lost in something and lookup at the clock and the day has just vanished.  That does not happen while copying query results and pasting them into Excel!

I decided in December 2010 that I owed it to myself to bite the bullet and take this new fangled Powershell thing for a spin.  I decided to do what I always do when I want to learn a new language or technology:  I gave myself a requirement.  I set out to automate our manual checklist for configuring a new instance of SQL Server and (true to Jeremiah’s observation) I was able to automate 99% of it!

By the time I finished with that first project, two things had happened.  I had gotten to know Powershell fairly well and I had fallen in love with it!  Powershell is so full of ah-ha and ooh-yea moments that it is just a joy to work with.  I’ve since continued to seek out that joy by taking an automate-first approach to everything that comes my way now.  If I can automate it, I do automate it.  With each passing week, the percentage of what I do that could be automated is going down while the percentage of what I do that is automated goes up.


4 Responses to T-SQL Tuesday: My Automation Epiphany

  1. Matt Velic says:

    That’s quite amazing. Now if I could get PowerShell to automate the departmental administration that I do, I’d be sold!

  2. Tom Powell says:

    Yeah, PowerShell is where I need to be but I find the learning curve fairly steep. Ineed to just bite the bullet…

  3. […] Brian Garraty aka NULLgarity gives us excellent examples of biting the bullet and going with powershell for his automation.  I’m in the same boat on this one and need to jump in with both feet first!  Thanks for the push! […]

  4. Curtiss Murphy says:

    Nicely written Brian. Good thoughts!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: