This post is written on request: the one and only Rob Sewell invited us at TSQL2sday #94 to write about using PowerShell with SQL Server.
Since this whole site is about that, this is both easy and hard.

PowerShell First

When I started as a system and network administrator in 2007, I had a limited daily task list, and a very variable work load as troubleshooter, help desk black sheep, and so on. It happened there were empty hours and I looked for something to fill those. Someone suggested learning PowerShell. V1 wasn't launched much before that, and my colleague heard about it, but didn't know anything more. But he thought it would be good if someone in the team could start using this new tool.
Man, wasn't that the golden tip?
I ordered PowerShell in Action,first edition. Be sure to get 3rd edition if you want to read this for yourself, which I highly recommend.

Now you're a DBA

A few years later our development lead thought the time was there to hire a real DBA, because load and complexity became a bit to heavy to let a developer handle this as a side job. It appeared to be easier to find a new system administrator and promote me to be the DBA. As soon as I got the approval I ordered some books again, this time on SQL Server. In those first months I also got some formal training: Microsoft courses M10774A, M10775A, M10776A, M1077A and M10778A.
But, as the number of SQL Servers grew from 2 to about 15, I didn't like clicking all day in SSMS and went looking for a link between PowerShell and SQL Server.

SQL Server with PowerShell

Richard Siddaway's PowerShell in Practice had an interesting chapter on SQL server and Paul Nielsen's Microsoft SQL Server 2008 Bible had a chapter on PowerShell, so there were some first steps available.
But the book I really dove in to next was Microsoft SQL Server 2008 Administration with Windows PowerShell by Yan Pan and The MAK. It's mostly outdated by now, full of methods and strategies we no longer have to use because of the evolution we've seen in the past 10 years. But at the time, those two really did some pioneering, and I'm still using parts of their work today!

My first and biggest project

I set up a database to collect information about my SQLInstances, containing tables about computer configuration, SQL Instance settings, diskspace history, database size history, logins and users, table sizes, error logs and some more. I altered this DB design a couple of times over the years, and added both reports in SSRS and a Power Pivot model to analyze the daily findings, discover trends and raise warnings.
All I have to do when I install a new SQL Server is Upsert-SQLHost and Upsert-SQLInstance and the rest .... is AUTOMAGIC !!!

And then some

Besides this project, I wrote and wrote and ... copy-pasted a lot of PowerShell code :-)
Today I (Get-ChildItem .\SQL\ -Recurse *ps1).count 415 PowerShell scripts to watch, configure, check, manipulate ... our SQL Servers, or send them some T-SQL. And that's not even counting the specific modules.

Wait, Modules?

Oh, sure. While the first versions were somewhat limited and sometimes quirky, this is 2017, and the available modules have evolved and keep getting better every day. It's so much easier to have hundreds of functions ready-to-use. The first non-Microsoft contributions I got to profit from were bundled in SQLPSX. This project was started in 2008 with great work by Chad Miller, Laerte Junior t, Max Trinidad t, Aaron Nelson t, Mike Shepard w and others.
Today, Microsoft's SQLServer module and dbatools t are the center of the DBA world.
A big thank you to all who have put effort in the development of those wonderful tools!
And I'm proud to say some of the magic I learned during the years is now included in the dbatools module.
You should find and follow the team members and especially driving force Chrissy LeMaire t.

And what about you?

Now, to get back to Rob's question: "should we all use PowerShell?".
Uuuuhmmm, how can you doubt that?
If you are a DBA and have more than one SQL Server under your wing, and you are not using PowerShell, I'm very interested in your daily routine. To me there appears to be only one way to do my job as a DBA. That's with PowerShell.
That's why I'm a PowerDBA. What kind of DBA are you?

There. I probably forgot to mention and thank a lot of people who guided us to where we are now. When you meet some of them during your PowerShell journey, say thanks on behalf of me,will you?

Previous Post Next Post