There appears to be no QOS being collected for the Microsoft SQL Server Always On checkpoints.
- sqlserver probe configuration ('Monitor Always On' checkbox)
- need to know the procedure/take the required steps to enable Always On monitoring
1. First confirm with your DBA that the prerequisites for enabling SQL Server 'Always On' feature were already 100% completed.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/enable-and-disable-always-on-availability-groups-sql-server?view=sql-server-2017#Prerequisites
2. Is the Always ON Availability Group feature enabled on your MS SQL Server database?
Here are some ways to confirm it:
- Using SQL Server Management Studio
To determine whether Always On Availability Groups is enabled
In Object Explorer, right-click the server instance, and click Properties.
In the Server Properties dialog box, click the General page. The Is HADR Enabled property displays one of the following values:
True, if Always On Availability Groups is enabled
False, if Always On Availability Groups is disabled.
- Using Transact-SQL
To determine whether Always On Availability Groups is enabled
SELECT SERVERPROPERTY ('IsHadrEnabled');
The setting of the IsHadrEnabled server property indicates whether an instance of SQL Server is enabled for Always On Availability Groups, as follows:
If IsHadrEnabled = 1, Always On Availability Groups is enabled.
If IsHadrEnabled = 0, Always On Availability Groups is disabled.
***To enable or disable the AlwaysOn Availability Groups feature, use SQL Server Configuration Manager.***
3. If possible, please upgrade to sqlserver v5.42 GA or higher.
4. sqlserver probe configuration
Using the Infrastructure Manager (IM),
a. All THE AAG_* checkpoints will remain disabled in a profile until the "Monitor Always On" feature (checkbox in the specific connection profile), is enabled so please confirm that is selected in the connection profile.
b. Ensure that the function being monitored by the "aag*"-profiles is in fact used / configured on the SQL Server which is being monitored, otherwise no QOS values will be stored.
c. Ensure that the sqlserver probe checkbox, "Generate Status Only" is un-checked / deselected.
Please refer to the SQLSERVER metrics listed here:
https://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-metrics
You can also run some of the queries for the Always On feature to see if it returns any data. For example:
--aag_db_replica_synchronization_state:
SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.name, rtrim(b.replica_server_name) object, rtrim(d.name) database_name, c.synchronization_state, c.synchronization_state_desc from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_database_replica_states c,master.sys.databases d where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id and c.database_id = d.database_id
--aag_replica_operational_state:
SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.name, rtrim(b.replica_server_name) object, c.role_desc, c.operational_state, c.operational_state_desc from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_availability_replica_states c where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id and c.is_local != 0
--aag_replica_synchronization_health:
SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.name, rtrim(b.replica_server_name) object, c.role_desc, c.synchronization_health, c.synchronization_health_desc from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_availability_replica_states c where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id
If you're not getting AAG alerts, check the sqlserver_monitor.cfg for the parameter-> "is_AlwaysOn" - it should not be blank, so ensure it is set it to "1" as per the steps listed below: