Using PowerShell to program SQL Server’s Policies and Data Collection

I received a question today about whether I'd converted my Policy-Based Management examples using SMO (see the multi-part "Programming Policy-Based Management with SMO" series, starting here) from C# to PowerShell yet. I did do this a while ago; they're available as a script download on the SQLskills website (look on the "Past Conferences" page under TechEd 2008). But…

Since then RC0 has changed PBM a bit. The multipart name policy can't use ExecutionMode.Enforce any more, so I changed it to ExecutionMode.None which equates to "On Demand" in the SSMS dialog. There was a change to one of the enumerated constant names too. So the updated scripts for RC0 are posted as part of this blog entry.

As long as I was working on parts of the PowerShell provider for SQL Server and SMO, I decided to put together an example of using the DataCollection APIs as well. The script creates a custom DataCollection Collection Set. That's pretty straightforward. Using the CollectionStore instance just use the correct location in the provider hierarchy and get a CollectionStore instance.

# set a collectionstore object for the default instance on local machine
$colpath = ('SQLSERVER:\DataCollection\' + (get-item env:\computername).Value + '\default')
$col = get-item $colpath

Then create a CollectionSet instance with (your CollectionStore $col is specifed as a parameter to new-object) and one or more CollectionItems in the CollectionSet. Then set the CollectionSet and CollectionItem proprties and call Create. This script enclosed as well, named CreateCustomCollectionSet.ps1.

As an aside, I've enclosed a script called start_smo_sql.ps1. This is a script that I call at PowerShell startup if I know the SQL Server provider is already installed. It sets convenience variables for long SMO namespace names, paths to the default Server, CollectionStore, etc. It's not invoked from my main PowerShell profile %UserProfile%\My Documents\WindowsPowerShell\profile.ps1 which is executed by all PowerShell shells/this user. Instead it's invoked from %UserProfile%\My Documents\WindowsPowerShell\Microsoft.SqlServer.Management.PowerShell.sqlps_profile.ps1. This profile is only executed when I'm using SQLPS.exe, the SQL Server-specific custom shell. It's quite cool that PowerShell accomodates separate profiles for custom shells.

The toughest part in creating a custom collection set is that the XML schema for collection set properties has been updated in one of the CTPs, but not updated in Books Online RC0. The updated XML schema can be obtained by executing:

use msdb
select * from syscollector_collector_types

and using the parameter_schema column to get the schema you need to figure out how to create a validatable parameter. My T-SQL custom collection set used a parameter with an XML namespace on the root element (only) that looked like this:

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
          <Value>select * from sys.dm_exec_query_stats</Value>
</ns:TSQLQueryCollector> (3.23 KB)

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.