Contrary to popular belief, it’s not all about Powershell for me. Yeah, I do a lot with it and want to share that knowledge, but I’m still a SQL Server DBA and there are many challenges I encounter day to day where I use my other skills. Policy Based Management(PBM) is one of those areas where I need to apply this knowledge and solve problems.
A Dirty Window
My current shop makes heavy use of PBM for alerting and monitoring our environment. For more on using PBM, check out stuff by John Sterrett(@johnsterrett) or this book. It’s a very useful tool, but takes some getting used to. We have policies in place to alert on data file free space, last backups, database owners, and a host of other checks for the health and configuration of our environment. So many, in fact, that identifying what has failed can be a chore when things go wrong.
With PBM, it is very easy to know when a policy fails, but a lot more difficult to see exactly what has failed and why. Usually, the easiest way to see a failure is in SQL Server Management Studio (SSMS):
As you can see, this doesn’t tell us much. The next stop is the PBM history, which you can access by right clicking on the Management -> Policy Management node in SSMS and selecting ‘View History’:
The result opens up the SQL Server log viewer. As this is a simple example, it may not be clear to you, but the log viewer is a chore for history. If you have more than a few policies, you will have a lot of records to navigate and load, making troubleshooting extremely convoluted. When you’re a DBA looking to fix a problem, this is problematic. Fortunately, there is another way.
I Can See Clearly Now
Most of the information in SQL Server is stored in views. The SQL Server team has gone to great lengths with the Dynamic Management Objects (DMVs) to make sure most of the information displayed through the GUI is stored somewhere behind the scenes as a queryable object. With PBM, these system views are found in MSDB with the name dbo.syspolicy*. I dug in and found the following views that had the information I was looking for:
The query pattern from there is pretty typical: Show the most recent set of history records for a policy. The result is the following query:
;with last_pbm_execution as ( select policy_id ,max(history_id) as history_id from msdb.dbo.syspolicy_policy_execution_history where end_date > GETDATE()-1 group by policy_id ) select p.name ,h.end_date ,case h.result when 1 then 'Success' else 'Failure' end result ,d.target_query_expression ,d.exception ,d.exception_message ,d.result_detail from msdb.dbo.syspolicy_policies p join msdb.dbo.syspolicy_policy_execution_history h on (p.policy_id = h.policy_id) join last_pbm_execution lpe on (h.policy_id = lpe.policy_id and h.history_id = lpe.history_id) left join msdb.dbo.syspolicy_policy_execution_history_details d on (h.history_id = d.history_id) order by p.name
This query gives me a quick, easy to read report for the server of each policy and what the last result was. In the event of a failure, the target_query_expression, exception, exception_message, and result_detail columns give me the info about why the policy failed, helping me to fix it.
As DBAs we need quick answers and need to spend as little time as possible wading through logs of what went right just to get at what went wrong. This can be exacerbated when a tool doesn’t have good reporting right out of the box. Within SQL Server, we can overcome this by understanding the system views and the information stored there. That knowledge will help us get those quick answers, accelerating our troubleshooting and allowing us to make effective use of our tools.