SQL Server 2008 Policy-based Management

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 https://sqlblog.com/blogs/greg_low/ and https://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

• What can you do in SSMS with a group of servers?
• The idea of a Central Management Server
• List of Server Group, which servers are in each group
• Lives in MSDB, can be shared, query with
• Used for Queries, Object Explorer, Policy Evaluation
• Books Online: How to: Execute Statements Against Multiple Servers Simultaneously
• See https://msdn.microsoft.com/en-us/library/bb964743.aspx
• See https://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx

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

• SQL Server 2005: DDL triggers – AFTER triggers only, EVENTDATA, LOGON triggers (SP2)
• See https://msdn.microsoft.com/en-us/library/ms175941.aspx
• Many system stored procedures now fire DDL triggers, like sp_rename

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 https://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 … } }