Register for Semperis' Hybrid Identity Protection (HIP) Conference - June 30 - July 1 Register for Semperis' Hybrid Identity Protection (HIP) Conference - June 30 - July 1
SQL Server

Why Should DBAs Care About PowerShell?

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.

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

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.

Related Topics:

BECOME A PETRI MEMBER:

Don't have a login but want to join the conversation? Sign up for a Petri Account

Register
Comments (6)

6 responses to “Why Should DBAs Care About PowerShell?”

  1. Powershell Basics for DBAs

    [...] So let’s get into the basics of PowerShell and see what it’s all about. By the way, if you’d like to know why you should even care about PowerShell, you can read about some of the things it can do for you in my article, Why Should DBAs Care About Powershell? [...]
  2. PowerShell Basics for DBAs » www.vinilrichard.com/mylife/ – My Internet Diary

    [...] So let’s get into the basics of PowerShell and see what it’s all about. By the way, if you’d like to know why you should even care about PowerShell, you can read about some of the things it can do for you in my article, Why Should DBAs Care About PowerShell? [...]
  3. SysAdminDB » PowerShell Basics for DBAs

    [...] So let’s get into the basics of PowerShell and see what it’s all about. By the way, if you’d like to know why you should even care about PowerShell, you can read about some of the things it can do for you in my article, Why Should DBAs Care About PowerShell? [...]
  4. PowerShell Basics for DBAs

    [...] even care about PowerShell, you can read about some of the things it can do for you in my article, Why Should DBAs Care About PowerShell? Familiar concepts PowerShell is easy because it presents you with things as drives. For example, [...]
  5. SolarWinds Free VM Console | Daily Freeware Download

    [...] taking virtual machine snapshots, and performing shutdowns and restarts has never been easier!What do you do when a virtual machine is running amuck - or not running at all - and you just want t...and easily shutdown and restart a virtual machine - even if you're not a VMware administrator. [...]
  6. Scripting benefits | Preventionofswineflu

    [...] PowerShell Scripting Advantages for DBAsJan 13, 2011 … Advantages that PowerShell offers to DBAs. Real world examples demonstrating the simplicity of scripting with PowerShell, in contrast to T-SQL … [...]

Leave a Reply

Register for the Hybrid Identity Protection (HIP) Europe Conference!

Hybrid Identity Protection (HIP) Europe 2021 - Virtual Conference

Mobile workforces, cloud applications, and digitalization are changing every aspect of the modern enterprise. And with radical transformation come new business risks. Hybrid Identity Protection (HIP) is the premier educational forum for identity-centric practitioners. At the inaugural HIP Europe, join your local IAM experts and Microsoft MVPs to learn all the latest from the Hybrid Identity world.