Here are some notes on “SQL Server 2008 Policy-based Management” I took while attending an advanced class on SQL Server taught by Greg Low (from http://sqlblog.com/blogs/greg_low/ and http://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.


Scenarios

  • Manage a large number of SQL Server, in the thousands
  • Manage situations where some servers are working and some aren’t

Server group management

Server group management - Demo

  • Experimenting with Queries going to a group of servers
  • Trying “SELECT @@VERSION” – Multiple results
  • Configure SSMS under “Multiserver Results” – Single set of results
  • Done in the client, servers do not really know about it

Before Policy-based Management

Policy-based Management (PBM)

  • Explicit declarative management, may use DDL triggers
  • Facets – Fixed list of things you can set policy on, like database, SP, etc.
  • Policies – Applies to a facet and to a target (enabled/disabled, can restrict to certain servers)
  • Conditions – When the policies apply
  • Books Online: Administering Servers by Using Policy-Based Management
  • See http://msdn.microsoft.com/en-us/library/bb510667.aspx

Policies

  • Start from scratch or import file
  • Many policies included in the box, as an XML file (none installed by default)
  • When importing, also populates dependant conditions
  • Usually keep disabled at first. Enable later.
  • Can store policy as XML

Conditions

  • Boolean expression – Field, operator, Value. Combine with AND/OR, groups.
  • How to group AND/OR blocks – select multiple, right click, group
  • When using IN operator, put Array() in Advanced Edit.
  • Can see the policies that use that condition (dependencies)
  • Evaluation mode – On demand / on schedule / on change: prevent (varies based on the facet)
  • Schedules – Some already there

Evaluating

  • Run against multiple servers, option to view details, export results (XML)
  • Careful – Can run T-SQL, WMI. Warnings will pop up in that case.
  • Violations are written to SQL Server error log and Windows application log.

Powershell and PBM

  • Can create a script to run these, handle results
  • $sl = <server list>
  • $cn = new-object Microsoft.SQLServer.Management.Sdk.Sfc.SqlStoreConnection(…)
  • $ps = new-object Microsoft.SQLServer.Management.DMF.PolicyStore($cn)
  • foreach ($s in $sl) { foreach ($p in $ps.Policies) { Invoke-PolicyEvaluation … } }