Just in time for PASS Summit 2010, the CodePlex project SQL Server PowerShell Extensions (SQLPSX) has been updated . Here’s a rundown of what’s new in release 2.3…

Added MSI-based installer

The installer was built using the Windows Installer XML (WiX) and this was an exercise in learning WiX. I hope to blog about building an MSI the command-line way soon.

Added PBM Module

The PBM module adds functions which use the out-of-box cmdllet Invoke-PolicyEvaluation provided in the sqlps mini-shell. In addition to the PowerShell functions, tables and sample policies are provided that work with SQL 2000, 2005 or 2008 or 2008 R2. The module is similar to EPM Framework, but a much simpler implementation.

One of things the PBM module provides is the ability to write policy evaluations to both a consolidated SQL table and if desired to the Windows EventLog. Why write the policy evaluation to the EventLog? Well, this is how you can integrate PBM with SCOM. Just have your SCOM guys pick up the particular EventID.

Modified adolib module

We added invoke-bulkcopy which uses SqlBulkCopy class and allows column mapping. I’m a big fan of using the SqlBulkCopy class to load data using PowerShell and now Mike Shepard has given us a nice function to handle both simple and more complex data loading.

Modified OracleIse and SQLIse modules

Both

We moved saved preference to Isolated Storage instead of module directory, this means makes it easier to use system instead of user module location if so desired. We modified the prompt to show both Oracle and SQL Server connections. That’s right you can load OracleIse and SQLIse to query both data sources at the same time! We added auto, table, list, and isetab output options and moved output option to separate dialog box. The output option enhancements are pretty slick. I find send query output to a new tab especially useful.

OracleIse Specific

• Added PoshMode to OracleIse (already existed in SQLIse) which allows embedding PowerShell variables in queries

SQLIse Specific

• Added print and raiserror handling.
• Added multi-query handling

Modified SqlServer module

We changed statement timeout from the SMO default of 10 minutes to unlimited. This was needed for backup and other long operations. We fixed issues/added functionality in specifying database objects. This includes new pattern matching object names and speeding up retrieval of specific database objects in most of the get- functions.

We added progress indicator, percent complete and success messages to backup and restore functions. You’ll now see a progress bar when doing a backup or restore. In addition if the –verbose option is specified you’ll see the percent complete and success messages you’re used to seeing in SQL Server Management Studio.

Credits

My thanks to

• Bernd Kriszio for his work on OracleIse and many of the enhancements to SQLIse
• Mike Shepard for adding functionality to adolib

Next Steps

Download SQLPSX and leave some feedback on the SQLPSX site