Windows PowerShell was introduced in 2007, and it has since become a standard platform for administrative automation across Microsoft business and server prod-ucts. It’s a command-line shell, and product teams (like SQL Server’s team) extend the shell so it can help manage their products. In this chapter, we’ll look at how to use PowerShell with SQL Server.
16.1 Windows PowerShell overview
Windows PowerShell is a command-line shell, not unlike Unix-style shells like Bash or the old Windows Command Prompt (Cmd.exe). What’s different about Power-Shell is that it’s a modern shell, designed around modern needs and with the Win-dows OS in mind. The shell itself contains minimal functionality; feature and product teams write modules (or snap-ins) that give the shell additional capabilities.
For example, the SQL Server team created a module that lets the shell connect to, and manage, SQL Server. It’s important to keep in mind that the ability to manage a product by using PowerShell is dependent upon the specific product version pro-viding that module. SQL Server 2012 provided SQL Server’s first fully functional module, making it the oldest version of SQL Server that can be extensively man-aged by using PowerShell. SQL Server 2008 R2 shipped with a less-complete snap-in for PowerShell.
NOTE This chapter assumes that you have a basic familiarity with Windows PowerShell; if you don’t, consider reading Learn Windows PowerShell 3 in a Month of Lunches, Second Edition (Manning, 2012), which provides a begin-ner-level introduction, or Learn PowerShell Toolmaking in a Month of Lunches (Manning 2012), which gets into the scripting and toolmaking aspects of
144 CHAPTER 16 Windows PowerShell and SQL Server
the shell. Jeffrey Hicks and I are the authors of both. PowerShell In Depth by Jeffrey Hicks, Richard Siddaway and me (Manning 2013) provides a compre-hensive administrator’s reference to shell functionality.
To get PowerShell running on any version of Windows where it’s installed, hold the Win-dows key on your keyboard and press R. That brings up a Run dialog; type powershell and hit Enter (or click OK). If you’re doing this for the first time on a computer, I sug-gest immediately typing $PSVersionTable into the shell and then pressing Enter.
Doing so will reveal the shell’s version information. (If nothing shows up, you’re on v1.0.) Table 16.1 details the versions of PowerShell that ship with various versions of Windows, and the versions that can be installed.
SQL Server 2012’s PowerShell module requires at least PowerShell 2.0, and also works on PowerShell 3.0 (and later). If you want a later version of PowerShell than what comes with your version of Windows, you can download the Windows Management Framework (WMF) from http://download.microsoft.com. The WMF version corre-sponds to the PowerShell version (WMF 2.0 contains PowerShell 2.0), and the WMF includes supporting technologies, including Windows Remote Management and Back-ground Intelligent Transfer Service.
NOTE When Microsoft releases a new version of PowerShell, it typically is released as part of the latest version of Windows. A short time after that release, Microsoft typically makes the corresponding WMF download available for the two most recent versions of Windows. Avoid installing the latest WMF on servers until you’ve verified that the products running on that server are compatible with that version of the WMF and PowerShell.
One reason PowerShell has been well accepted by administrators is that, with a little bit of jump-start instruction, the shell is pretty easy to learn. It offers a number of dis-coverability features that help you find the commands you need to accomplish given
Table 16.1 PowerShell and Windows versions
Windows version Comes with PowerShell version Supports PowerShell version Windows XP/ Windows Server
145 SQL Server and PowerShell
tasks, and the command syntax is very straightforward and noncryptic. Commands typically come with extensive built-in help, including usage examples and explana-tions. Unfortunately, most of those features only apply to one of two ways that a prod-uct team can choose to implement PowerShell support—and unfortunately, the SQL Server team chose the other way.
16.2 SQL Server and PowerShell
There are a couple of ways to launch PowerShell and have SQL Server support preloaded for you. Note that launching PowerShell as I described earlier in this chapter will not automatically preload SQL Server’s module; you’d have to do so manually.
In SQL Server Management Studio, you can right-click nearly anywhere and find a Start PowerShell menu option. That will open a shell with several SQL Server modules preloaded. If you only launch a plain copy of the shell, run Import-Module SQLPS to load up the main SQL Server module.
NOTE Additional modules provide functionality for other SQL Server compo-nents, such as SQLASCMDLETS and Microsoft.AnalysisServices.PowerShell .Cmdlets. You can import those if needed. If you’ve installed SQL Server Management Studio on your client computer, then the PowerShell modules should also be installed and available.
If you get a list of commands implemented by the core SQL Server module, you won’t be impressed:
PS SQLSERVER:\> get-command -module sqlps | select name Name
146 CHAPTER 16 Windows PowerShell and SQL Server
Looking at the list, it doesn’t seem like SQL Server’s module can do much, does it?
The awesome Invoke-SqlCmd command can do a lot, because it lets you execute any T-SQL command you like, and since most of SQL Server can be managed by run-ning T-SQL commands. But that seems like cheating, right? Using PowerShell just to run T-SQL?
16.2.1 Meet the SQLSERVER provider
The trick is that much of SQL Server’s PowerShell support is implemented by some-thing called a PowerShell Provider, or PSProvider. These work a bit like a disk drive, with the root of the drive providing core functionality, and the folders beneath that root pointing to different SQL Server components. When the SQLPS module is loaded, it automatically maps to the local instance, if one exists; you can then add more connections to other instances that you want to manage.
For example, in the default SQLSERVER: drive, I can get a directory listing by run-ning Dir or Ls, and see the following:
Name Root Description ---- ----
---SQL ---SQLSERVER:\---SQL ---SQL Server Database E...
SQLPolicy SQLSERVER:\SQLPolicy SQL Server Policy Man...
SQLRegistration SQLSERVER:\SQLRegistr... SQL Server Registrations DataCollection SQLSERVER:\DataCollec... SQL Server Data Colle...
XEvent SQLSERVER:\XEvent SQL Server Extended E...
Utility SQLSERVER:\Utility SQL Server Utility DAC SQLSERVER:\DAC SQL Server Data-Tier ...
SSIS SQLSERVER:\SSIS SQL Server Integratio...
SQLAS SQLSERVER:\SQLAS SQL Server Analysis S...
As you can see, the subfolders of the drive each represent a major SQL Server compo-nent: SQL is the main database engine, SSIS is SQL Server Integration Services (SSIS), and so on. Underneath each component is a connection to a specific computer, and under those are folders for connections to instances on those computers. For example:
PS SQLSERVER:\> cd sql
147
Figure 16.1 illustrates the basic hierarchy to that point.
This hierarchy can get a bit cumbersome, because to do anything you’ve got a long path just to get started. If I want to manage something in the Database Engine on my computer, which is named WIN-NQT7TB6A571, and in my instance named SQL2, my command-line path starts with SQLSERVER:\SQL\WIN-NQT7TB6A571\SQL2.
A problem with this provider-based approach is that PowerShell doesn’t have a way to provide help files, or to help you discover what you can do or how to do it. You just kinda have to know. That’s good for book authors like me, but it makes it hard to get started.
NOTE By the way, in support of my fellow book authors, I’ll recommend SQL Server 2012 with PowerShell V3 Cookbook (Pact Publishing, 2012) by Donabel San-tos. It’s one I keep handy when I’m working with PowerShell and SQL Server.
Why did the SQL Server team elect to go down the provider path, rather than pro-viding a set of more traditional commands? Possibly because the provider model made it easier for them to delegate their programming work across the many sub-teams that comprise the overall group. By having each major component (such as SSIS) plug in to a top-level SQL Server provider, they created something that probably looked elegant in the planning stages. Providers also have the benefit of being more
SQLSERVER:
Figure 16.1 Exploring the SQLSERVER provider hierarchy
148 CHAPTER 16 Windows PowerShell and SQL Server
dynamic. You can add something to the hierarchy without necessarily having to pro-gram a whole new set of commands; the built-in PowerShell commands used to work within a provider will work. That said, the provider model has been criticized by some as being difficult to learn. Regardless, it’s what we have to work with, so let’s see what it can do.
16.2.2 Doing something useful
One common thing that administrators are asked for in SQL Server is a list of logins that, perhaps, have nonexpiring passwords. That’s certainly a security concern for many organizations, but clicking through SQL Server Management Studio to find all of those accounts can be time-consuming. It’s pretty easy information to get with a T-SQL query, but you can also do it with PowerShell:
TRY IT NOW By all means, follow along with these examples. I’ll provide the commands I type in boldface, so that you can pick them out of the example more easily.
PS SQLSERVER:\sql\WIN-NQT7TB6A571\sql2\logins> Get-ChildItem | Where { -not $_.PasswordExpirationEnabled } | Select Name,LoginType
I boldfaced the command to make it easier to read. You can see that I changed to the Logins folder of my SQL2 instance, running on the computer named WIN-NQT7TB6A571, under the SQL (Database Engine) component of the SQLSERVER: drive. I asked for a list of all items within that Logins folder, keeping only those whose PasswordExpirationEnabled property was False, and selecting just the Name and LoginType properties of those logins.
NOTE If all that seems clear as mud, keep in mind that most of the syntax is basic PowerShell stuff that I haven’t covered. Again, a good beginner-level book in PowerShell will teach you those things pretty quickly.
Let’s do another example. This one’s useful: generating a list of blocking processes, meaning processes that are currently blocking other processes. This example shows that the provider model—and even the handful of commands provided in the SQLPS module—often aren’t enough to do what you want. (This time, I’m using boldface to highlight the information you’ll need to insert.)
149 SQL Server Agent and PowerShell
$server = New-Object –TypeName Microsoft.SqlServer.Management.Smo.Server
➥ -ArgumentList "INSTANCE_NAME"
$server.EnumProcesses() |
Select Name,Spid,Command,Status,Login,Database,BlockingSpid | Format-Table –Auto
That should be a list of all processes running on the given instance; to see blocking processes:
$server.EnumProcesses() |
Where { $_.BlockingSpid –ne 0 } |
Select Name,Spid,Command,Status,Login,Database,BlockingSpid | Format-Table –Auto
This example used SQL Management Objects, or SMO (pronounced, “smoe”). That’s a .NET Framework API provided by SQL Server for management purposes. If it looks a bit like programming. . .it is. It was originally designed as a Visual Basic or C# API, but it’s accessible from within PowerShell. There’s a lot to it, far more than I can cover in this one chapter, although the cookbook I referenced earlier provides a lot of SMO examples.
I wanted to show you these two examples as a way of helping you understand how PowerShell works with SQL Server. Yes, it’s possibly more complex than it needs to be, given how other products (like Exchange Server) have managed to simplify their interfaces into sets of more easily discoverable and well-documented commands. But it’s what we have to work with. So what can it help you automate?
■ Inventory SQL Server configuration settings
■ Alter database properties
The list is huge. Anything you have to do more than once is going to become cumber-some in the graphical SQL Server Management Studio, and although some things can be automated by using T-SQL, other tasks will require you to use SMO, and PowerShell is perhaps the easiest way to do that.
16.3 SQL Server Agent and PowerShell
SQL Server Agent, being an automation tool itself, works fine with Windows Power-Shell. In fact, one of the cooler things you can do with it is running PowerShell scripts.
This is useful for tasks that can’t be handled by the Database Maintenance Plan func-tionality, or that can’t easily be accomplished by using T-SQL. Agent pops open a back-ground PowerShell process that has all the SQL Server modules preloaded, and then runs whatever commands you give it.
150 CHAPTER 16 Windows PowerShell and SQL Server
TRY IT NOW This is another good example to follow along on, as there are a couple of easy-to-miss details that I’ll point out.
To begin, I’m going to create a job that lists blocked processes, using the example code from the previous section of this chapter. Beginning in SQL Server Management Studio, with Management and SQL Server Agent expanded:
1 Right-click Jobs and select New Job….
2 Configure the General page as shown in figure 16.2.
3 On the Steps page, click New….
4 Configure the page as shown in figure 16.3. The command code I used is below;
notice that I used boldface for items that you must change:
$server = New-Object –TypeName Microsoft.SqlServer.Management.Smo.Server
➥ -ArgumentList "INSTANCE_NAME"
$procs = $server.EnumProcesses() | Where { $_.BlockingSpid –ne 0 } |
Select Name,Spid,Command,Status,Login,Database,BlockingSpid | Format-Table –Auto
Send-MailMessage -To "[email protected]" -From "[email protected]"
➥ -Body $procs -Subject "SQL blocked processes"
➥ -SmtpServer mail.company.com 5 Click OK twice.
Figure 16.2 Configure the job’s General page.
151 SQL Server Agent and PowerShell
This creates a job, but doesn’t have it run on a schedule. Instead, we’re going to run this as the result of an alert. Staying in SQL Server Management Studio:
1 Right-click Alerts and select New Alert….
2 Configure the General page as shown in figure 16.4. Note that I set the blocked process threshold to 5; you can adjust that how ever you like. Your report will be sent whenever the number of blocked processes rises above the specified number.
Figure 16.3 Configure the Steps page.
Figure 16.4 Configure the alert’s General page.
152 CHAPTER 16 Windows PowerShell and SQL Server
3 Configure the Response page as shown in figure 16.5.
4 Click OK.
You’re done! Go back to chapter 13 for an example of how I created a process that can block others; repeat that experiment if you’d like to test that your blocked process report. Note that the job I created depends upon the SQL Server Agent account being able to send email anonymously through the specified email server; that depends on the mail server being willing to do so. In your lab environment, without an SMTP mail server, the message won’t be sent.
16.4 Hands-on lab
This lab may seem a little unfair, because I’m going to ask you to do something I haven’t shown you how to do. But that’s very real-world! You’ll probably need to start using a search engine, including keywords like PowerShell, “SQL Server” (in quotes), and SMO to help limit the results to the most useful ones. I’ll provide an answer on MoreLunches.com: find this book’s cover image, click it, and find the Downloads sec-tion to get the sample solusec-tions ZIP file.
Write a PowerShell command that will list all indexes with fragmentation of more than 10% (on your sample AdventureWorks2012 database, this may produce no out-put, because it’s unlikely those indexes are that fragmented). Then, write a second command that will reorganize all indexes with more than 10% fragmentation.
Figure 16.5 Configure the alert’s Response page.
153