Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















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

MSSQLTips author John Grover By:   |   Read Comments (2)   |   Related Tips: More > 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.

SQL Server Management Studio 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.

Create New Condition called Standard Alerts Present

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'')'

Now comes the fun part, adding T-SQL into the condition in the Advanced Edit screen

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.

Click "OK" to save the T-SQL function and evaluate the 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".

In SQL Server Management Studio, create a 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.

Use the Check Condition drop down to select the condition in the Create New Policy screen

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.

Evaluate Policies in SQL Server Management Studio and Warning Message

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.

Evaluate Policies

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).

Results from the Policy Execution - Failed with a value of 0 and expected 4

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


Last Update: 3/14/2013


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

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Thursday, March 14, 2013 - 10:43:54 PM - Brady Read The Tip

Nice first tip John. Welcome to the MSSQLTips.com community!


Friday, May 17, 2013 - 6:44:20 PM - Marsha Read The Tip

In the Advance Edit screen, can you add the quotes around numeric in the ExecuteSQL() statement.

marsha  



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.