8/21/2008 3:54:43 PM
Steve Wright
Moderator
Posts: 73
|
Following are the steps to set up automated performance alerting. Note that you need local Windows admin rights to access performance counters, and the SQL Sentry Server service will also need those rights to automate their collection.
1. Select which SQL Server you would like to host the placeholder job. In essence the placeholder job will be a standard SQL Agent Job that has one ‘waitfor delay’ step, so it imparts no additional load on the server. Note that you only need one job on one server, regardless of where you will ultimately be collecting performance data.
2. From the SQL Sentry Console, expand the node of the server you selected, right click on the Jobs node and select New > Placeholder Job…
3. Give the job a name such as ‘Disk Space Monitor Job’, optional description, and a duration of 5 seconds. Click OK
4. Schedule the job to run whenever you want to collect your disk space metrics. Maybe a daily check once at 8am, or you may want to check as often as every five minutes. It’s up to you. Realize that if your counter goes beyond the defined threshold, you’ll start getting emails every time the job runs which could be bad if it happens in the middle of the night and the job runs every five minutes! This can be mitigated however using Response Rulesets.
5. Once you set your schedule, the job will be created and you should see it show up under the Jobs node for that server. Expand the node for this new job. There should be three sub-nodes, including Performance Counters. This is where you will link specific counters to this particular job.
6. Double click on the Performance Counters node. You will see the counter selector, which should look familiar to you if you’ve added counters in Windows Perfmon. It’s the same process.
7. Enter the name of the server that you want to monitor. The server where this job resides will be displayed by default. Click Connect and the categories and counters will be listed. You may need to allow a few seconds for the initial connection after you click the Connect button.
8. For free disk space for example, select the LogicalDisk category. In this category you have both % Free Space, as well as Free Megabytes. Select the counter and the specific drive and click Add. You will notice the Performance Counter node for the job now has a plus next to it. If you expand it, you’ll see the counter you just added listed underneath.
9. Repeat steps 7 and 8 to add additional counters for any server you want to monitor.
10. Next you need to define minimum and maximum thresholds for these counters. Click on the first counter in the list in the Navigator pane, then click on the Settings tab on the right hand side of the Console. Enter your Minimum and/or Maximum threshold. Specifying 0, the default, effectively disables that setting. Repeat this process for each counter you added to the job.
11. Next we need to define the action to be taken when a threshold is exceeded, such as send an email with the details. To do this, click on the job in the Navigator pane. Next click on the General Actions tab on the right side of the screen. Click on the + next to Condition: Performance Counter: Threshold Min. Click on the box in the left most column next to Send Email so that it has a check box in it.
12. Click on the User or Group that you want to be the recipient of the email in the Select Targets box at the bottom. Repeat steps 11 and 12 for the Condition: Performance Counter: Threshold Max.
13. The final step to activate Performance Monitoring for this job is to right click on it in the Navigator pane and select Monitor Performance.
14. From now on, any time this job is scheduled to run, the counters you added will be collected, and should any of them exceed the thresholds you defined, an email will be sent to the targets you selected. Should you ever want to temporarily disable this behavior without having to delete all the counters, simply right-click on the job and select Stop Monitoring Performance.
I’m sure you can see from this example that there are many possibilities to automate actions based on any of the hundreds of performance counters that are available from Windows and SQL Server through SQL Sentry. Even more functionality like this is currently in development for future releases of SQL Sentry products.
edited by jhall on 8/3/2010
--
Steve Wright
SQL Sentry Support
|