sqlserver probe - SQL Server Always On QoS metrics not being collected / no AAG alarms

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

There appears to be no QOS being collected for the Microsoft SQL Server Always On checkpoints.

Environment

- UIM v9.0.2
- sqlserver v5.41-TB2 or higher

Cause

- sqlserver probe configuration ('Monitor Always On' checkbox)

- need to know the procedure/take the required steps to enable Always On monitoring

Resolution

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

Additional Information

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:

1. Open sqlserver_monitor.cfg and search for "is_AlwaysOn"
2. Update this file ("is_AlwaysOn = 1") with value 1 in all places.
3. Restart the probe