Why Should DBAs Care About PowerShell?

Posted on January 13, 2011 by Sean McCown Microsoft SQL MVP in SQL Server with 0 Comments

I teach PowerShell all over the place. I teach it at conferences, in webinars, in user groups, at work, etc. One of the questions I get from DBAs the most is “Why should I care about PowerShell?” That’s not only a very easy question to answer, but it’s also fun. I’ll admit though that it often makes me feel like an infomercial spokesperson: “How would you like to spend about ¼ of the time administering your servers as you do right now?” “Are you tired of doing everything one server at a time?” And stuff like that. It’s true though; these are the reasons you should care about PowerShell.

PowerShell isn’t just another scripting language. And in fact I don’t think it’s officially a scripting language at all; it’s a scripting environment. So what makes PowerShell so cool then? Well, let’s look at a couple of examples that will show how much easier it can be to do things in PowerShell.

Sponsored

1. Let’s say you want to get a list of all the tables in your database, along with their row counts and data space used. In T-SQL that’s definitely a cursor with some logic thrown in for good measure. And if you wanted to throw in how much space indexes are using too, that would make it even more involved. The same operation in PowerShell only requires a single line of code:

Dir | FT Name, RowCount, DataSpaceUsed, IndexSpaceUsed

Doing it even the simplest way possible in T-SQL can’t come close to how easy and concise that is.

2. Let’s say you want to script all the objects in your database. Using T-SQL it’s actually very long cursor code. Of course, everyone does it in the wizard in SSMS, but you can’t schedule that, or do it on multiple servers. You can buy 3rd party apps to do it for you, but now you’ve got to cover all your DBAs with licenses so the cost just goes up for everybody you bring on board. With PowerShell though, the operation is as easy as the last.

Dir | %{$_.Script()}

I won’t stop to explain any of the code in this article. My purpose here is to just get you thinking about what PowerShell can do for you, and to show you why it’s such a big deal. I actually discuss the scripting topic in detail in these 3 articles:

Scripting Database Objects in PowerShell
Scripting Database Objects with Advanced Scripting Options in PowerShell
Creating an Enterprise Scripting Template in PowerShell

3. Now let’s turn our sights to something you can’t really do in T-SQL. Let’s control our services. SQL Server (and many other apps) has a number of services that you might  need to turn off and on for various reasons. You could fake doing it in T-SQL using xp_cmdshell, but that’s a dangerous command to open on your server, and again, it doesn’t cover multiple boxes. Here’s how you can do it in PowerShell, and it can easily be called from an Agent job. Here we’re going to stop all the SQL-related services.

Get-service | ?{$_.DisplayName –match “SQL”} | %{$_.Stop()}

There you go. I’ve stopped all the services that contain the word “SQL”. And I bet by looking at the code you can figure out how to turn them back on, can’t you? So how would I run this on a remote box then? That’s easy, all you need is the ‘-computername’ parameter for the get-service command. It would look like this:

Get-service –ComputerName Server1| ?{$_.DisplayName –match “SQL”} | %{$_.Stop()}

4. Here’s something that’s not easy to do in T-SQL at all… creating and writing to files. Let’s say that you want to take the script you created in #2 above and save it to a text file. It’s actually so complicated to do in T-SQL, that not many people even know how to do it, so they fall back on VBScript. Well, as it turns out, PowerShell is far better at this than even VBScript is. Here’s the VBScript code for writing a simple line of text to a file:

Option Explicit
Dim objFSO, objFolder, objShell, objTextFile, objFile
Dim strDirectory, strFile, strText
strDirectory = “C:”
strFile = “\Holidays.txt”
strText = “Book Another Holiday”

‘ Create the File System Object
Set objFSO = CreateObject(“Scripting.FileSystemObject”)

set objFile = Nothing
set objFolder = Nothing
‘ OpenTextFile Method needs a Const value
‘ ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForAppending = 8

Set objTextFile = objFSO.OpenTextFile _
(strDirectory & strFile, ForAppending, True)

‘ Writes strText every time you run this VBScript
objTextFile.WriteLine(strText)
objTextFile.Close

Sponsored

Sponsored

Now here’s the PowerShell for the same operation:

“Book another holiday” | out-file c:\Holidays.txt

That’s it, really! All of that VBScript code was replaced by half a line of code. This is the kind of thing that makes PowerShell so amazing for everyday use. So if you’re not excited about it by now, let me leave you with one parting thought. Imagine performing maintenance tasks on all 500 or all 2,000 of your servers almost as easily as you perform them on just one box. That’s one of the things PowerShell does extremely well, and it’s how I manage my entire environment of 600 servers.

Sponsored