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.
“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.