How many times do we struggle to solve a problem using T-SQL when we could probably solve it faster and more efficiently with a language whose main focus is not data manipulation and database object management? PowerShell can fill this gap.
Welcome to our new monthly column, which will cover examples of real-life SQL Server pro- blems that can be solved using PowerShell. We will not concentrate on the language itself, which is very flexible but can get complex enough to fill a book. We will actually take the reader’s basic knowl edge of the PowerShell language for grant ed. If you need to learn or brush up on it, there are many good books and resources available, including:
  • Microsoft Windows PowerShell Step By Step , by Ed Wilson. A classic; although it’s about PowerShell 1.0, it covers the funda- mentals and is an easy read.
  • Windows PowerShell in Action , by Bruce Payette. Written by a founding member of the PowerShell team and co-designer of the language, it gives you a unique perspective on the language.
  • Microsoft SQL Server 2008 Administration with Windows PowerShell , by Ananthaku- mar Muthusamy and Yan Pan. This book cov- ers both the language and SQL Server-spe- cific features

We know that a lot of SQL Server DBAs are not keen to learn a new language, especially if it means also learning .NET. But we are not .NET gurus and
will not be proposing any advanced .NET wizardry. Instead, our goal is to show you how PowerShell can help make your work as a DBA easier by using stan- dard PowerShell and SQL Ser ver Management Ob- jects (SMO) functionalities as much as possible.

A New Tool

As DBAs, we often find ourselves needing to build some kind of task automation. We have T-SQL to script with and the SQL Server Agent service to schedule our tasks. And just in case we need something more, we can dust off our command- line batch or VBScript skills. OK, we might be exaggerating a little. But real ly, how many times do we struggle to solve a problem using T-SQL when we could probably solve it faster and more efficiently by using a lan- guage whose main focus is not data manipulation and database object management? PowerShell can fill this gap. What makes PowerShell the ideal environ- ment for SQL Server administration tasks that cannot be performed —or are hard to achieve— using T-SQL? Here are some key factors:

  • PowerShell is integrated with numerous services.
  • You can access several common APIs through it (whether by using native cmdlets or through .NET).
  • The community continues to develop many extensions to it.
  • PowerShell is now distributed with the op- erating system, and can be installed on most older systems anyway.

As PowerShell is maturing, it is reasonable for DBAs to invest some time learning it. Even if you’re already confident with other programming languages that integrate nicely with .NET, there’s still a place for PowerShell. With PowerShell, you don’t have to worry about building and deploying your code, and you have the new powerful concept of piping objects — sending the results of one cmdlet to the next cmdlet.

SQL Server and PowerShell

Although SQL Server 2008 offers tighter integra- tion with PowerShell than older versions do, you can still use it with SQL Server versions before SQL Server 2008 in many cases. There are several options for using PowerShell with SQL Server.

  1. You can directly use ADO.NET objects to execute T-SQL batches and process results.
  2. You can directly use SMO to navigate SQL Server’s object hierarchy and use its methods to accomplish common tasks.
  3. You can use community-provided extensions such as the SQL Server PowerShell Extensions to easily access SMO and other object libraries.

Which SQL Server versions are supported, and thus which functionalities are available, depends largely on the approach you choose. For example, if you decide to use ADO.NET, then nearly every version of SQL Server for which there is a supported SQL Server driver is supported by de- finition. Of course, this means working with plain T- SQL batch requests and result sets. Or to put it ano – ther way, you won’t be dealing with an abstraction layer that presents a hierarchy of objects that reflects SQL Server’s objects. However, if you use SMO or community-provid- ed extensions, you have different kinds of abst rac- tion layers (and/or wrappers) available. And the sup- ported SQL Server versions are pretty much the ones supported by the corresponding SMO object library.

SQL Server 2008 PowerShell Support

SQL Server 2008 support for PowerShell consists mainly of a snap-in that implements a SQL Server provider and another snap-in that implements a set of cmdlets. The SQL Server provider snap-in enables mount ing SQL Server’s objects using hierarchies simil arly to how a file system’s objects are orga nized using paths. The drive for PowerS hell is called SQLSERVER: and is associated with the SMO mo- del; the root path nodes are based on some of the model’s classes. Table 1 lists the supported folders.

Table 1. Supported Folders for the SQL Server Provider

Table 1. Supported Folders for the SQL Server Provider

Table 2. Cmdlets implemented by Cmdlets Snap-in

Table 2. Cmdlets implemented by Cmdlets Snap-in

 

For this release of the provider. Table 2 summarizes the cmdlets implemented by the cmdlets snap-in.

Here are some other areas of SQL Server 2008 PowerShell integration that you can benefit from:
  • SQL Server Agent now supports a new PowerShell job subsystem, allowing job steps to be written as PowerShell scripts.
  • A mini-shell called SQLPS provides a preconfigured environment that has SQL Server’s extensions preloaded.
  • Context menus in SQL Server Management Studio open the mini-shell, automatically changing the path according to the context and reusing the current credentials.
You can find more information about SQL Server’s PowerShell integration in SQL Server 2008 Books Online under the “SQL Server PowerShell Overview” topic and in the community technical article “Understanding and Using PowerShell Support in SQL Server 2008,” by Allen White.

Getting Started with SQL Server 2008 PowerShell

A quick way to start exploring SQL Server 2008’s PowerShell integration is to simply open PowerShell from SQL Server Management Studio’s context menus, as Figure 1 shows.
Figure 1. Opening PowerShell from SSMSs context

Figure 1. Opening PowerShell from SSMSs context

Selecting Start PowerShell opens the SQLPS mini-shell, where you can begin playing with PowerShell. For example, you can try using cmdlet aliases to navigate SQL Server’s object model as you would a file system, as Figure 2, page 53, shows. Using the cd and dir commands to navigate the object hierarchy is easy and familiar, but the real power of PowerShell comes when you start applying the object pipe and filtering/looping functionalities. For example, let’s say you want a list of the Agent jobs that failed; you can get those results as Figure 3 shows.

 

Figure 2. Using cmdlet aliases to navigate SQL Servers object model

Figure 2. Using cmdlet aliases to navigate SQL Servers object model

Figure 3. Getting a list of the Agent jobs that failed

Figure 3. Getting a list of the Agent jobs that failed

 

More to Come

For this initial column, we decided to go with a gentle start, briefly explaining PowerShell’s bene- fits and showing some basic usage of Power- Shell with SQL Server 2008. In the coming months, we will look at how to use PowerShell to solve specific real-world problems. Although we already have some ideas of what we want to cover, we would love to hear what you’d like to see in this space.

Article co-authored by Gianluca Hotz and Herbert Albert.

Latest posts by Gianluca Hotz (see all)