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
- Easy multiserver automation that lets you perform any given function across multiple SQL Server instances
- Fast and easy data loads when you don’t need the sophistication of SSIS
- Quick and easy retrieval of properties of objects and processes on the server
Easier access to Windows resources, such as files and folders, Windows Services, and printers
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:
- Get-SqlServer calls the Microsoft.SqlServer.Management.SMO.Server object and retrieves a list of all available SQL Server systems.
- Get-SQLUser retrieves an SMO user object with added properties showing all objects owned by the user.
- Get-SQLData retrieves a SQL Server result set.
• Get-SQLDatabase retrieves the properties for one or more databases.
- Get-SQLUser retrieves the information about one or more users, including all the objects owned by the user.
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.
SQL Server PowerShell Extensions|
Benefits: SQLPSX provides you with a ready-made mix of PowerShell scripts to automate even the most difficult SQL Server administrative tasks, including database maintenance, provisioning, and authorization.
System Requirements: PowerShell; Server Management Objects; SQL Server 2008
How to Get It: You can download SQLPSX from www.codeplex.com/SQLPSX.