SQL Server PowerShell Extensions

PowerShell is a very powerful scripting language that can amplify your ability to automate almost any administrative function. This month’s free tool is a set of handy PowerShell scripts, SQL Server PowerShell Extensions (SQLPSX). SQLPSX, which was written by Chad Miller, a SQL Server DBA living in the Tampa, FL area, automates many common administrative functions in SQL Server.

PowerShell scripts offer DBAs several advantages over the standard T-SQL and SQL Server Integration Services (SSIS) approach to automation. Some benefits of PowerShell include

You can learn more about PowerShell by visiting the Windows PowerShell page or give Miller’s blog post "The Value Proposition of PowerShell to DBAs" a read. Now let’s take a look at what SQLPSX has to offer.

Function Calls and Scripts in SQLPSX

In a nutshell, SQLPSX contains PowerShell scripts to perform more than 100 administrative SQL Server tasks, although many of the function calls and scripts focus on security settings for logins, users, roles, and permissions. SQLSPX is available on the CodePlex website, and I recommend reading the Readme.Txt file included with the project because it contains a full description of each function available. The following are some of the functions you can use to automate typical SQL Server tasks:

 In addition to function calls and scripts, SQLPSX provides a reporting element. Once you’ve installed SQLPSX PowerShell functions, you can create a database to store their output and then view that data using SQL Server Reporting Services reports and queries to analyze the security information via Business Intelligence Development Studio (BIDS) or Visual Studio.

SQLPSX’s System Requirements

You can download SQLPSX from www.codeplex.com/SQLPSX. This tool requires SQL Server 2008, the Server Management Objects (SMO), and PowerShell. SMO is installed by default with SQL Server Management Studio (SSMS), so if you have the native tools for SQL Server 2005 or later, you’re good to go. Once you’ve installed SMO or SSMS and PowerShell, you’ll need to set PowerShell’s execution policy to remotesigned. (The exact way to set this policy varies by OS.) You might also need to unblock the SQLPSX PowerShell scripts so that they can run without constraint. Refer to the SQLPSX documentation for detailed instructions on enabling PowerShell execution on your SQL Server systems.

Miller keeps a DBA-centric blog with lots of PowerShell information at chadwickmiller.spaces.live.com/default.aspx. I encourage you to read this blog to get familiar with PowerShell.