IDERA SQL Inventory Manager performs Health Checks on your SQL Server instances to alert you about the availability, performance, and other critical issues of your environment.
The following table describes what type of metrics are measured, their status, when alerts are raised, their criticality levels, the collection frequency for each metric, and if email alerts are sent.
| Metric | Category | Status | Alert raised when/threshold | Collection Interval | Email Alert |
|---|---|---|---|---|---|
| Instance status | Availability | up down | critical: instance down | Runs every half minute | Yes |
| Instance response time | Availability | response time ok slow response time | warning: 2000 ms critical: 5000 ms | Runs every half minute | Yes |
| Database status | Availability | normal suspect offline inaccessible emergency mode | critical: suspect, offline, inaccessible, emergency mode | Runs every day | Yes |
| Database never backed up | Disaster recovery | backed up never backed up | critical: never backed up | Runs every day | No |
| Database not backed up recently | Disaster recovery | backed up recently in the last 7 days no full backup in 7 days or more no differential backup in 7 days or more | critical: no full backup in 7 days or more warning: no differential backup in 7 days or more | Runs every day | No |
| Databases with no CHECKDB integrity check | Disaster recovery | CHECKDB never run | critical: CHECKDB never | Runs every day | No |
| Databases with no recent CHECKDB integrity check | Disaster recovery | run CHECKDB in the last 7 days no recent CHECKDB in more than 7 days no recent CHECKDB in more than 30 days | no recent CHECKDB in more than 7 days no recent CHECKDB in more than 30 days | Runs every day | No |
| Drives at risk | Storage capacity | below 75 % capacity between 75% and 90% above 90% | warning: between 75% and 90% critical: above 90%, server drive is at risk of being full soon | Runs hourly | Yes |
| Databases at risk | Storage capacity | below 75 % capacity between 75% and 90% above 90% | warning: between 75% and 90% critical: above 90% | Runs hourly | Yes |
| Database Auto shrink enabled | Configuration check | enabled disabled | warning: enabled | Runs every day | No |
| Instance xp_cmdshell enabled | Configuration check | enabled disabled | critical: enabled | Runs every day | No |
| Database Tempdb files not all the same size | Configuration check | same size not the same size | warning: not the same size | Runs every day | No |
Refresh collection
IDERA SQL Inventory Manager allows you to refresh these metrics at any time from the instance view. For more information about this option, please refer to Viewing instance information.
Definitions
Availability Data
Availability Data refers to the instance status (up or down), instance response time, and database status (normal, suspect, offline, inaccessible or emergency mode).
Capacity data
Capacity data refers to storage capacity of your drivers and databases of your environment. IDERA SQL Inventory Manager lets you know if your drives or databases are at risk of being full soon.
CHECKDB Checks the logical and physical integrity of all the objects in the specified database. If corruption has occurred for any reason, the DBCC CHECKDB command will find it, and tell you exactly where the problem is.
Configuration Data
The following configuration data is collected:
- Auto Shrink SQL Server automatically shrinks databases to remove unused space. In some cases shrinking databases may not be the best option for your environment since it may cause fragmentation. This configuration check allows you to know if the Auto Shrink option is enabled or disabled.
- Xp_cmdshell Xp_cmdshell is essentially a mechanism to execute arbitrary calls into the system using either the SQL Server context (i.e. the Windows account used to start the service) or a proxy account that can be configured to execute xp_cmdshell using different credentials. When xp_cmdshell is enabled, the user could escalate his/her privileges to sysadmin. It is important to be aware which instances have the xp_cmdshell option enabled for security reasons. This configuration check allows you to know if xp_cmdshell is enabled or disabled in an instance.
- Tempdb files It is important to configure the files at the same initial size and with the same growth settings so SQL Server can write the data across the files as evenly as possible. The data files for tempdb should all be the same size and large enough to handle the workload of 24 hours a day. This helps SQL Server distribute the work evenly across the tempdb data files. This configuration check allows you to know if tempdb files are the same size or not.
Disaster recovery
Disaster recovery is a process to help you recover information if disaster occurs. Disaster recovery planning refers to the preparation that must occur in response to a disaster. IDERA SQL Inventory Manager provides Health Checks that give you information of your latest backups, alerting you if any of your databases have not been backed up recently.
For more term definitions, refer to Definition of terms.