Creating custom T-SQL conditions and policies for SQL Server Policy Based Management

By: John Grover   |   Updated: 2013-03-14   |   Comments (2)   |   Related: > Policy Based Management

Problem

In SQL Server, the built in conditions and policies are a great place to get started with monitoring your environment, but there are no facets for some aspects of SQL Server I want to monitor. How can I check on my environment using Policy Based Management? Check out this tip to learn more.

Solution

Policy Based Management (PBM) provides the flexibility for you to write custom conditions that can be evaluated on anything for which you can write a T-SQL statement.

SQL Server's Policy Based Management provides a selection of built-in facets, conditions, and policies which are a great starting point for monitoring your SQL Server infrastructure, but they are just a starting point. PBM also gives you the ability to create conditions that will monitor anything you can access with a T-SQL statement. I'm assuming you've already spent some time with PBM, if you need a primer on the basics see some of the links at the end of this tip to help you get started.

Configuring Custom Policy Based Management Conditions

One way DBAs keep an eye on what is going on with their servers is by using SQL Server Alerts that will automatically notify them of events that require their attention, but PBM has no facet for alerts. When I set up a new server I always define a group of standard alerts, but what if I forgot one? I want to ensure all my alerts have been defined by writing a custom condition using T-SQL. In this example we're going to verify that four common alerts have been defined: "Disk Full", "Filegroup Full", "Tlog Full", "Tlog unavailable". We'll create a condition, include it in a policy, and execute the policy against a server to show that it works.

To create a custom condition fire up Management Studio, connect to the server you want to monitor (or your Central Management Server) expand the Management branch, the Policy Management branch, and right-click the Conditions branch to create a new condition.

First, we'll give the condition a name. We are going to look for our group of standard alerts so we'll call it "Standard Alerts Present". Alerts are an instance-level object so for this condition will use the Server facet, but you can write a custom condition against any of the facets available in PBM.

Now comes the fun part, adding T-SQL into the condition. The T-SQL statement will go in the Field column of the expression pane using the ExecuteSql function. This function has two arguments: the first is the return type of the query; the second is the T-SQL query itself. Valid return types are numeric, string, Boolean, datetime, array, and GUID. The ExecuteSql function's return value is the first column of the first row of the query. Note that the query string must be provided in single quotes which means any single quotes in the query itself will need to be escaped by adding another single quote before it. Our query is simply going to count the alerts that match the list of standard names listed above. You can substitute any alerts you've defined for your site. Place the cursor in the Field column and click the ellipsis button (...) to bring up the advanced editor. Scroll down the Functions and properties selector and click on ExecuteSql(), this will place a template of the function in the editor. Replace "string return Type" with "numeric", and replace "string sqlQuery" with this SQL (note that the single quotes have all been escaped).

'SELECT COUNT(*) from msdb.dbo.sysalerts 
where name in (''Disk Full'', ''Filegroup Full'', ''Tlog Full'', ''Tlog unavailable'')'

Click "OK" to save the T-SQL function and return to the Create New Condition form. This query is going to return a single row with one column. I have four alerts so the expected return value is 4. Make sure the Operator selected is "=" and enter 4 as the Value. Click "OK" to save the new custom condition.

Now that the condition has been created to check for the existence of four alerts we'll need to create policy to execute it. Back in the SQL Server Management Studio Object Explorer, expand the Management branch, the Policy Management branch, and right-click the Policy branch and select "New Policy".

You can call the policy whatever you like, I've chosen to name the policy after the condition it will check. Use the Check Condition drop down to select the condition. The conditions are grouped by facet so you'll need to scroll down until you get to the Server facet and then find the "Standard Alerts Present" condition. For this tip we won't assign a target and we'll define the policy to run on demand, targets and scheduling are beyond the scope we are going to cover. Click OK and we are ready to test our new policy.

Right-click on the policies branch and select Evaluate. In the Evaluate Policies dialog scroll down the list of policies until you find our new "Standard Alerts Present" policy. Note the caution icon next to it, this is not because it is new, it is because the condition contains plain T-SQL code. The power and flexibility of using T-SQL comes at the cost of security, any arbitrary code could be wrapped in a condition and wreak havoc with your server! Never, ever execute a condition or policy, or any kind of code, from an untrusted source without first checking it out very carefully, but you don't need me to tell you that! Check the box next to the new policy and click Evaluate to run the policy against the instance you are currently logged into. Management Studio will warn you again about running untrusted code, if you're satisfied the T-SQL is safe, click run.

In this case I can see I am out of compliance on my server so I'd better wrap this up quickly and get back to work. If you click on the view detail link in the Target Details pane you will see the field and value comparison that failed for the target.

The field is our custom T-SQL, we set up the condition to expect a result of 4 and the field returned 0- all of the standard alerts are missing from this instance (yes, boss, I'm right on that, right now).

I've tried to give a very simple example of how you can use Policy Based Management to monitor just about any attribute of your SQL Server infrastructure. This just scratches the surface of the flexibility and power of this built-in tool that will help you keep your databases under control.

Next Steps

• Most of what I've learned about PBM is from the very excellent book from Apress "Pro SQL Server 2008 Policy-Based Management" by Simmons, Stasiuk, and Segarra. There are also some very good tips on this site which will help you make this tool even more useful: Using Policy Based management in SQL Server 2008 by Ray Barley which will help you get started, and Evaluating Policies on Multiple Instances Using Central Management Server by Svetlana Golovko which will help you leverage CMS and PBM together.

About the author

John Grover is a 25-year IT veteran with experience in SQL Server, Oracle, clustering and virtualization.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips