Beyond the DemoWare – A complex (i.e. real world’ish) start to finish Policy Based Management SQL Server 2008 example, brought to us via its ExecuteSQL function.

Bart Duncan's SQL Weblog - Defining Complex Server "Health" Policies in SQL 2008

“Policy Based Management (PBM) is a new feature in SQL Server 2008 that allows you to define a set of policies that capture the intended state for a group of servers. For example, you could define a policy that says that your user databases should all have the auto update statistics database option enabled. (If you’re not yet familiar with PBM, you can read more about it in Books Online or in the PBM blog.)

In SQL 2008, the focus of PBM is primarily on static aspects of server management – policies covering things like schema requirements, or server and database configuration settings. However, there are certain more dynamic aspects of server state that are equally important, but much harder to monitor. Server “health” monitoring (e.g. uptime, responsiveness) is one example. I’m going to show you how you can use the ExecuteSql function to extend the normal capabilities of PBM by defining policies that ensure your servers are servicing queries effectively. You can use live Dynamic Management View (DMV) queries, or even query historical data that you are capturing in a Management Data Warehouse.

IMPORTANT: Before going on, read through this blog post for an overview of the PBM ExecuteSql function.

For an initial example, we’ll use a query that identifies excessive disk response time (>100ms average) for any of the files in a database:

…”

Bart (go Duncan’s!  ;) walks us through creating a more complex Policy Based Management (PBM) than you see in normal SQL Server 2008 demo’s. From the initial SQL creation all the way through to the SQL Health Monitoring Policies and Management Data Warehouse.

The cool thing about this demo is that it’s a pattern we can reuse. Pretty much if you can SQL it, you can PBM it.

These kinds of soup to nuts examples will come in handy once I get the “Go” to look into using SQL Server 2008 at work…