A Million Little Things: Remediation
Most database performance monitoring tools on the market notify you if something has gone wrong. However, you still have to resolve the issue and, depending on the problem, possibly log the issue in a help desk system.
What if there were a way to be proactive rather than reactive so that you and your company could be more effective and efficient? Read on to find out how you can save enough time to watch a John Oliver video on YouTube. (A disclaimer about watching YouTube at work can be found here.)
At SentryOne, we believe that we have the best alerting mechanism available right now to help you find and solve your Microsoft Data Platform issues. We provide the possibility of remediation of these problems right away.
With any of our conditions (alerts), which include General, Failure, Audit or Advisory conditions, you currently have several ways to remediate a performance issue. In this post, we are going to focus on the following four "remediation actions:"
- Execute Job
- Execute PowerShell
- Execute Process
- Execute SQL
Adding an Action
Before we get into the four remediation actions, let's take a look at how to select an action. The following section has been lovingly copied and pasted from the SentryOne help documentation:
To add a new Action, select the desired node in the Navigator pane.
Select Add in the Conditions pane (View > Conditions), which will open the Action Selector window.
Expand the applicable object and condition. Use the check boxes to select which Actions should be taken in response to the condition being met. Select OK to save your changes.
Execute Job
Remediation actions are typically going to be more specific than the general alerts that you want to set up. Usually, I recommend you set things at the "All Targets" level in the Navigator window. In this example, I'm selecting a particular target (LondonSrv07) and limiting the events that could trigger the action. Once you have set the scope by choosing the level of hierarchy you are interested in, you can click the Add button on the Conditions tab.
As there are several different condition groups available, make sure you choose the right group for the type of alert you are interested in.
The available condition groups are:
The screenshot below shows how you can select the action you require from the list of available actions. In this case, "Execute Job" has been chosen.
When the User clicks OK, that alert and action combination will be added in, as shown in the screenshot below.
The purple box highlights that this condition is considered to be an explicit condition (i.e., it has not been inherrited from a parent object) because I chose for this job to run for only this instance of SQL Server.
The box in red is the target that this job will run on. In this example, I chose to run a job on the same target that was having a problem. However, it is possible to run this action on any other target being monitored by SentryOne that can support this type of action.
The box in blue highlights the job I chose from a list of jobs from the instance LondonSrv07.
Execute PowerShell
Executing a PowerShell script as a remediation step is extremely powerful. It opens up all kinds of possibilities to integrate with other systems or fix potential issues. There's a great post on how to use the PowerShell Action. If PowerShell floats your boat, then prepare yourself for the voyage of a lifetime.
Execute Process
The Execute Process remediation step has the same first few steps as the Execute Job workflow. Once the action has been added, the view shown in the screenshot below will be displayed as part of the Conditions pane.
You can then choose the target you want to fire the action on and enter the commands you want to fire in the Command Text field.
Execute SQL
The Execute SQL remediation step has the same first few steps as the Execute Job workflow. Use the server list to select a server and enter the desired T-SQL statement in the T-SQL Command Text field.
Selecting the Target option executes the T-SQL statement against the server that triggered the condition. Please note that the T-SQL Command Text field has no parsing capability. For this reason, I would suggest that you write the code in an editor such as SSMS and paste the code into the T-SQL Command Text field.
You can also use system parameters to do some very cool things. I remember working with one of our partners to help them clear out a poisoned message in Service Broker using system parameters. For more information about the system parameters that can be passed through, check out the system parameters help documentation.
Other Integration Points
I would be remiss not to mention integration with other platforms. Although it is not a remediation step per se, it will add great value.
If enterprise integration is your thing, we provide details on how to setup SNMP Traps and use the SNMP action to integrate with other platforms such as Splunk—see Enterprise Integration: SQL Sentry, Splunk, and SNMP.
Conclusion
As you can see, these remediation steps are extremely powerful, especially if you tie them to Advisory Conditions, a framework we offer in which you can create your own specific alerts.
Imagine a world in which you can check for performance problems and then have that issue fixed for you. No need to imagine—download a trial of SQL Sentry today.