Updating SQL Elements health check intervals

by Vicky HarpJan 30, 2015

Periodically when I am speaking to users of SQL Elements I receive a request related to changing the interval at which health checks are run for individual servers. The reasons are varied: some people want to get health checks more frequently, but quite often they actually want them less frequently as they want more time to respond to what they see. The product does not expose any functionality for changing these intervals right now, but that does not mean it cannot be done. It is all exposed within the SQL Elements repository, and here are the scripts you’ll need if you want to modify it yourself.

All of the health checks and availability checks are run on an internal job schedule. The availability check interval is stored in the table [dbo].[product_config] and is universal for all monitored instances. All other intervals are stored in [dbo].[job_schedules] and can be configured on a per-instance basis.

Here is the SQL necessary to view your current settings.

use IderaElementsRepository;

select 
	Name,
	Value
from dbo.product_config
where Name = 'Availability.CheckIntervalSeconds' ;

select 
	name,
	job_description,
	run_every
from 
	dbo.job_schedules js
	inner join dbo.instances i 
	on js.instance_id = i.instance_id;

These settings can be changed with simple update scripts. The following template can be used to update the major health check settings for your entire environment. I would not recommend changing other job types beyond those listed here – behavior may be unexpected, especially around discovery.

use IderaElementsRepository;

update dbo.product_config
set Value = {value in seconds}
where Name = 'Availability.CheckIntervalSeconds';

update dbo.job_schedules 
set run_every = {value in seconds}
where job_description = 'InstanceAvailability';

update dbo.job_schedules 
set run_every = {value in seconds}
where job_description = 'DatabaseAvailability';

update dbo.job_schedules 
set run_every = {value in seconds}
where job_description = 'GatherInstanceCapacity';

update dbo.job_schedules 
set run_every = {value in seconds}
where job_description = 'GatherInstancePerformance';

You can certainly make less sweeping changes, as well. You can edit for individual instances quite easily, or join over to the tag table in the Idera Core repository in order to make changes to an entire tag at a time.

Feel free to keep letting me know if this is something you’d like to see changed within the product, but in the meantime, hopefully this helps you to customize your Elements installation for your particular needs.

Updating SQL Elements health check intervals

Tags