You’ve found a metric that you want to use in your SQL Server environment. What should you do next? Getting a metric working in SQL Monitor is easy, and we’ve provided this short walkthrough to help you get started.
In this scenario, you’ve found TJay Belt’s Disabled backup jobs example metric. After reading through the summary, you’ve decided it’s a metric that you want to start monitoring in SQL Monitor v3.2. Here’s what you need to do:
- Install cookies – If you want to download metrics automatically, you must install cookies and make sure cookies are enabled in your Internet browser. If you arrived at this site directly from a link in SQL Monitor, you’ve installed cookies already. If you didn’t, go to the Configuration > Custom metrics page in SQL Monitor, and click on one of the links to sqlmonitormetrics.red-gate.com.
- Install metric – Click the Install button near the top of this page. SQL Monitor’s Create custom metric wizard is displayed in a new tab, with fields automatically populated with the example metric settings.
- Update settings – Certain settings in this metric must be tuned to match your SQL Server environment, and notes are provided below each setting that needs updating:
- Take a look at the Enter the T-SQL used to collect data setting. There’s a note below the query explaining that specific job names must be replaced with names appropriate to your system. So replace: [sql]’%backup%'[/sql] with the name of your own backup jobs, and: [sql]’Daily Backups.Subplan_1′[/sql] with the name of any jobs you want to exclude.
- Now look at the note for the Select instances to collect from setting. As advised, turn on the check boxes for the instances you want to run the query against.
- There is also a note for the Define alert thresholds setting. Every alert on this site contains a note about how the thresholds settings are intended as guideling values. For TJay’s metric, he suggests raising a high alert once disabled backup jobs are detected, but you might prefer to raise this at a lower alert level if you don’t believe it deserves top priority.
- Test metric – To make sure the updated metric will work as expected, click the Test metric collection… button. The dialog displays a list of the MSDB databases for the instances you’ve selected. Click the Test metric collection button. SQL Monitor runs the query once against each selected database. If the test does not complete successfully, see the section called Test metric collection on Red Gate’s Support Center.
- Create metric – In the Create custom metric wizard, click Next until the Summary page is displayed. Now click the Create metric and alert button. The new metric is listed on the custom metrics page: <screenshot>
You’ve now got this example metric working in your environment, and can check the graph on the Analysis page in SQL Monitor to see collected values plotted as data points.Custom metric walkthrough,
Disabled backup jobs
This metric investigates backup jobs, searching for any that may be disabled inappropriately.
If any of your backup jobs have been disabled inappropriately, you may not be aware of their state. This may cause you grief if backups do not occur properly. Ensuring that your backup jobs are functioning as desired is paramount. Monitoring them is vital to the health of your topology.