Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

PBM and PowerShell

The best new management feature added to SQL Server 2008 is Policy Based Management or PBM. PBM allows DBAs to automate many of the traditional daily (or more frequent) checklists*. Although PBM is useable out-of-box there are several key features missing:

  • Unable to capture results of policy evaluations against downward-level versions (2000 and 2005). The required system tables used for storing policy evaluation results are only available in SQL Server 2008 or higher. So, out-of-the-box you can only use the GUI (SQL Server Management Studio) to “see” the results of downward-level evaluations**. What’s needed is the ability to capture the results of downward level versions.
  • Lack of integration with enterprise monitoring tools. Traditionally integration features for monitoring solutions are provided by SNMP traps or Ops Mgr Management Packs. Even writing messages to the Windows Event Log on policy evaluation failure is at least something, but this too is missing or incomplete. What’s needed is at minimum is the ability to write messages to the Windows Event Log.
  • Provides an Instance-level view instead of instead of an enterprise view. Although you can visually see the results of policy evaluation within SQL Server Management Studio, again this is only applicable to 2008 or higher and the results are stored within tables on each instance. What’s needed is the ability to consolidate and report the results of policy evaluations across all instances for any version.

How can these issues be addressed…?

Enter PowerShell

One of the key strengths of any scripting language is the ability to glue together existing tools to make another tool or to fill gaps in current tools. This is especially relevant for database professional stuck on older versions of SQL Server. Support for new management features like PBM isn’t being provided to SQL 2000/2005. PowerShell provides great way to bridge these gaps and provide PBM support for older versions of SQL Server. I’ve often heard remarks a reason for not using PowerShell by DBAs is that they do not use SQL Server 2008, so PowerShell is less relevant to them, but on the contrary PowerShell is necessary to allow you to address with missing functionality.

Invoke-PolicyEvaluation cmdlet

The SQL Server 2008 (or 2008 R2) host, sqlps provides a cmdlet called Invoke-PolicyEvaluation which does exactly as the name implies, evaluates policies. To create a solution, all that is needed is a few functions around Invoke-PolicyEvaluation which will insert the policy evaluation result to SQL table and optionally write an error message to the Windows Event log.

Extending Invoke-PolicyEvaluation

Release 2.3.1 of the CodePlex project, SQLPSX adds a PBM module***. The module uses the Invoke-PolicyEvaluation cmdlet and then loads the output into a SQL Server table. In addition failed evaluations are optionally written to the even log.

Getting Started

  1. Download SQLPSX
  2. The functions obtains a list of servers from a Central Management Server (CMS) Group. Ensure you have a CMS server with group and servers registered. The examples that follow use a group named XA:

     

    CMS

  3. Create an empty SQL Server database (or use existing database). For example "MDW"
  4. Use SQL Server Management Studio to run the 2 table creation scripts dbo.PolicyEval.Table.sql and dbo.PolicyEvalError.Table.sql
  5. Copy PBM PowerShell module folder to a directory listed in your $env:psmodulepath
  6. Modify PBM.psm1 Script-level variables in PBM.psm1 to point to the server and database created in step 1 and set preference variables as desired:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    
    $Script:EvaluationMode = "Check"
    $Script:PolicyServer = "Z003\R2"
    $Script:PolicyDatabase = "MDW"
    $Script:CMS = "Z003\SQLEXPRESS"
    $Script:WriteEventLog = $false
    $Script:LogName = "Application"
    $Script:LogSource = "PBMScript"
    $Script:EntryType = "Error"
    $Script:EventId = 34052
  7. Import Policies from PBM\Policies folder or use your existing policy
  8. Ensure policies have a Category set. In SSMS >> Policy Management >> Policies >> <YOUR POLICY> >> Select Description Tab

PBM

Running Import-PolicyEvaluation function

  1. The module should be used in the sqlps mini-shell. Alternatively you can use one of the techniques document in this blog post to load the full sqlps providers and cmdlets in regular PowerShell host. Because import-module isn’t supported in sqlps, source the functions:
     . C:\Users\u00\Documents\WindowsPowerShell\Modules\PBM\PBM.psm1
  2. Run Import-PolicyEvaluation specifying a ConfigurationGroup (the CMS Server Registration Group) and PolicyCategoryFilter (as defined in step 6)   For example:

     

    Import-PolicyEvaluation "XA" "EPM: Configuration"
  3. Optionally create SQL Agent job for each configuration group with the following PowerShell Job Step:
. C:\<path-to-module>\PBM.psm1
Import-PolicyEvaluation "XA" "EPM: Configuration"

Evaluating Results

The results of each policy evaluation will be loaded to the PolicyEval table:

PBM2

Errors in invoking the policy evaluation will be written to the PolicyEvalError table.

If you sent $Script:WriteEventLog to $true, an event log entry will be created for each policy evaluation failure with the specified event id ($Script:EventId = 34502). NOTE: In release 2.3.1 this line in PBM.psm1 $log.WriteEntry($Message,$Script:EntryType,$Script:EventId) should be changed to $eventlog.WriteEntry($Message,$Script:EntryType,$Script:EventId)  The nice thing about this is Ops Manager can pick up the Windows Event log entry and create an alert. In my environment we’ll take it a step further towards enterprise integration and even create incidents in our help desk application via connectivity between Ops Manager and CA Service Desk.

The PBM module includes two SSRS reports (a master and detail) which show overall success as well as drill-through to details:

PBM3

PBM4

Summary

The solution:

  • Fills a gap in a current toolset
  • Builds upon tools to glue together components. The solution leverages PBM including the Invoke-PolicyEvaluation cmdlet, Windows Event Log, T-SQL and SQL Server Reporting Services. Although its possible create your own daily check of SQL Servers using 100% PowerShell you would need to build the reports, delivery mechanism, and handle data storage–leveraging PBM is a far easier solution. 

Notes

*PBM can do more than check and can even prevent or fix things which violate a policy, this is only applicable to SQL Server 2008 or higher. Since most larger companies are not running SQL 2008, PBM is primarily used for checking policy against downward level SQL Server versions.

**Using the Invoke-PolicyEvaluation cmdlet you can evaluate a policy from a PowerShell host, however results are not stored for downward level clients.

***I’m not the first person to do this and I should mention the EPM Framework on CodePlex. The EPM framework has been around since 2008, however after looking at the solution and feeling it could be simplified and also having issues with the speed of policy evaluations during testing, I decided to implement my own PBM solution.

Comments

Posted by Steve Jones on 27 December 2010

Very cool. I think that a bridge like this is really handy for other versions. The lack of PBM being ported back to at least 2005, was a huge mistake in slowing adoption, IMHO.

Posted by cmille19 on 27 December 2010

I know, this is especially apparent when I compare management tools from other DBMS vendors; Oracle Grid Control vs. SQL Server PBM. The 11g version Grid Control is web-based tool with downward level support for 8i. Not only that but SNMP support is built-in so it was easier to integrate into enterprise monitoring than PBM.

Posted by kromerm on 8 February 2011

cmille19: you enable SNMP on your DB servers ??

Posted by cmille19 on 14 February 2011

@kromerm SNMP agent is part of Windows OS. The only reason I mentioned it is to compare Oracle Grid Control with PBM. PBM has zero integration with enterprise monitoring solutions such as SCOM while Grid Control does via SNMP. To make PBM truly useful in an enterprise environment it must integrate with monitoring solutions.

Leave a Comment

Please register or log in to leave a comment.