PowerShell: A Tale of Two Checklists (Slidedeck)

October 24, 2011

In the past couple of weeks, I have been fortunate enough to deliver my new presentation, PowerShell: A Tale of Two Checklists, at the Richmond Code Camp and to the Hampton Roads SQL Server and SharePoint User Group. Thank you to all that attended either and especially those that offered me feedback. As promised, there are the slides from the presentation.

As for my demo script, based on some feedback that I received, I have decided to tweak it a bit before posting it. I want to take some of the custom stuff out and make a version that could be universally applicable. I also want to make it driven by a configuration file so that the file itself does not need to change each time you run it. So, stay tuned and thanks to those that offered these suggestions!

If you missed these events, don’t fret! I am slated to deliver this presentation again at the SQLSaturday event in Washington, DC, on November 5th. This event is free and sure to be a blast! Get more information and register here. Hope to see you there!

Presenting at Richmond Code Camp 2011.2

October 4, 2011

I am honored and excited to have been chosen to present at the upcoming Richmond Code Camp on Saturday, October 8, 2011. My presentation is titled “PowerShell: A Tale of Two Checklists” and is scheduled for just after lunch. This will be my first time presenting at a Code Camp and my first time presenting on PowerShell!  The examples in my presentation are geared towards using PowerShell with Server Management Objects (SMO) to automate SQL Server configurations, however many of the concepts and lessons apply to any use of PowerShell for automation. I have included the session abstract below.

PowerShell: A Tale of Two Checklists

It was the worst of times.  It was the age of foolishness.  I had a long-winded, manual, GUI-intensive checklist for configuring new SQL Server installations.  Automating that checklist with PowerShell and SMO ushered in the best of times and an age of wisdom.  In this session, we will walk through my new automated checklist, make the case for using PowerShell for such tasks, and point those still mired in the winter of despair and manual drudgery towards the spring of hope that is scripting and automation.

If you are in the Mid-Atlantic area and are looking for an excellent learning and networking opportunity, you cannot do any better than the Richmond Code Camp. The folks that run this event have been doing it twice a year for a number of years and it is very well run, free of charge, and there are usually quite a few giveaways at the end of the day. Register here and I hope to see you there!

T-SQL Tuesday: My Automation Epiphany

February 8, 2011

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.