One of the more innovative components in SQL Diagnostic Manager is called alert actions and responses. This is the part of SQL Diagnostic Manager that allows you to configure rules to react to conditions on your managed servers. These rules can be as simple as sending out email notifications when a problem occurs. Or they can perform complex reactions to mitigate issues when they happen by executing T-SQL [Transact-SQL] commands or running PowerShell commands or running SQL Server agent jobs. To get to alert actions and responses, simply open SQL Diagnostic Manager go to the Tools menu. And select Alert Actions and responses. This brings up a dialog box that will show you the alert rules or alert responses that have already been configured inside your SQL Diagnostic Manager installation. By default, SQL Diagnostic Manager comes with a Newsfeed status update rule as well as a SQL Diagnostic Manager service status rule. All the rest of the rules can be added as needed. You can have as many alert rules as you like for your environment. And they can do anything from the action providers tab. So in response to any condition or change in condition on any of your SQL instances, you can run a variety of different action providers starting from sending out email notifications. So if a server changes status and goes down or it is not responsive you might want to send out email notifications to your help desk. But you can also run lots of other different options. One of the options available is to run a T-SQL [Transact-SQL] script. So if you want to rebuild indexes, for example, you could have a rule set up that that did that when indexes are fragmented rebuild my indexes. You can also tick off SQL agent jobs. Or if you have System Center in your environment you have the ability to forward events or alerts. Also, you can call executables and batch files. The PowerShell action provider does just that. It allows you to execute any PowerShell command against a monitored instance. The Newsfeed action providers pushes information from our alert system to the Newsfeed built-in to SQL Diagnostic Manager. You also the abilities you fire off SNMP [Simple Network Management Protocol] traps to any other SNMP [Simple Network Management Protocol] console. So if you have another product in your environment that uses SNMP [Simple Network Management Protocol] and you want to interact or integrate SQL Diagnostic Managers or learning with it you can certainly do that. The event log action provider allows events instead of being emailed or forwarded or reacted to you simply copies events from SQL Diagnostic Manager into the event log. Also, there are components within SQL Diagnostic Manager that can be turned on and off as needed. So we can turn on query waits as an action provider. We can also turn on the query monitor component within SQL Diagnostic Manager. Or if you would like to get recommendations for settings or index optimization recommendations or query optimization recommendations. In that case, we can turn on prescriptive analysis and get recommendations for your SQL environment. To configure your alert rules, go to alert responses. The first tab here at the bottom of the screen. You will have a button that you can add new rules, edit old rules, copy existing rules, or remove or delete rules that have already been configured. You will also note an import and export option. This is here if you want to add rules from for example our community. If you were going to “community.idera.com”. And you found an alert rule. Or if you had a colleague that had SQL Diagnostic Manager in their environment. And you wanted to use their alert rules. You could not port rules that had already been configured. Adding your rules is pretty simple. You just hit the Add button. This will bring up a dialog box. It is a three-step dialog box. You start by naming the rule. I am going to use some of the newer features to create an escalation rule. So configuring a rule. We start with step one. Step one is adding the conditions in which you want a rule to execute. Lots of different options here. You can have a rule that just affects a specific SQL instance. If that is something you would like. You can also have a rule that affects the sub-set of all your SQL instances by just executing against a certain tag. A tag is listed over here on the left-hand side. You can also pick specific metrics that you want to execute a rule for. So for example, if you wanted to have a specific response for blocking or deadlocking you can pick just one metric. You also have the ability to pick multiple metrics. For example, maybe I would like a rule that would go into effect when CPU is high as well as page life expectancy being low. I might want to put multiple metrics into a rule. And to have the execution occur. And the newer features that I talked about. I can also add the condition called an alert rank. This allows me to create rules for response specific to a rank of any given metric. So if I have got let us say one hundred or two hundred different metrics that I am monitoring. And some of them are very important. And have a high priority or a high rank. I can have an alert response that only goes into effect when high-ranking metrics go into a warning or critical condition. I can also add the condition that if a metric has not changed its severity in a certain period of time that can execute a rule. And then lastly I can add the condition of a time period. So for example, if I just want a rule to go into effect at night for midnight to six in the morning. I can add that condition. For the case of this escalation rule, I am going to add where alert rank value. And I am going to also add the condition of where metric severity is unchanged for a specific period of time. After you have added the conditions that you want for your rule. You then go to step two which is select actions. Here you can pick any or all of the action providers that are listed. For our purposes today I am just going to have this rule send an email to the escalations team. Then hit OK. Step three this is where you fine-tune your rule. In the case of this escalation rule, I am just going to have a rule that is created that executes when critical problems on any given metric have not changed severity in the last hour. So here I will put this, sixty minutes. And because I have got this ruleset to run against any metric that goes into a critical condition. I will also say I only care about metrics that have a certain rank. In this case, I am going to say greater than rank twenty. And it is just that simple. Once I hit OK that rule goes into the rules list. I can turn that rule on and off as needed. I can always go back in and edit or update that rule or make changes to that rule. And again I can have as many rules as I deem necessary for my environment. Some examples of rules that I think are great are things like the ability to automate the killing of blocking. I can go in and say if there is a blocking problem on any given monitored server kill the lead blocker. I can also do things like get recommendations. Like I said earlier maybe I want to get recommendations for different settings that I want to update in my environment or index optimization recommendations or query optimization recommendations or underlying settings. If I can get more out of my SQL environment or SQL Server by changing settings. This is a great example of things you can do with alert actions and responses. The more you work with alert actions and responses the more powerful your SQL Diagnostic Manager installation will be. And the more proactive you can be about reacting to problems that occur in your environment. Thanks so much for listening to this video. If you have any questions please feel free to reach out to your account manager. Or if SQL Diagnostic Manager is new to you can download it and trial it from “www.idera.com”. Thank you.
Topics : Database Diagnostics,Database Monitoring,Database Performance,SQL Query Performance,
Products : SQL Diagnostic Manager for SQL Server,