sqlserver probe - SQL Server Always On - QoS metrics not being collected

book

Article ID: 130138

calendar_today

Updated On:

Products

DX Infrastructure Management NIMSOFT PROBES

Issue/Introduction

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

Cause

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

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

Environment

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

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 Groupds feature, use SQL Server Configuration Manager.*** 

3. If possible, please upgrade to sqlserver v5.42 GA.

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

Attachments

1558688214251000130138_sktwi1f5rjvs16fk1.png get_app