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

Sponsored Content

What is “Inside Microsoft Teams”?

“Inside Microsoft Teams” is a webcast series, now in Season 4 for IT pros hosted by Microsoft Product Manager, Stephen Rose. Stephen & his guests comprised of customers, partners, and real-world experts share best practices of planning, deploying, adopting, managing, and securing Teams. You can watch any episode at your convenience, find resources, blogs, reviews of accessories certified for Teams, bonus clips, and information regarding upcoming live broadcasts. Our next episode, “Polaris Inc., and Microsoft Teams- Reinventing how we work and play” will be airing on Oct. 28th from 10-11am PST.

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:

External Sharing and Guest User Access in Microsoft 365 and Teams

This eBook will dive into policy considerations you need to make when creating and managing guest user access to your Teams network, as well as the different layers of guest access and the common challenges that accompany a more complicated Microsoft 365 infrastructure.

You will learn:

  • Who should be allowed to be invited as a guest?
  • What type of guests should be able to access files in SharePoint and OneDrive?
  • How should guests be offboarded?
  • How should you determine who has access to sensitive information in your environment?

Sponsored by: