New! 2023.06.22: Now supports Distributed Network Names (DNN)!

Make sure you have the newest version.

Version History:

• 2024.03.28: v1.0.2.22 – Added ability to parse multiple associated IP addresses. Fixed discovery issue. UNdiscovery of stale AGL works correctly now. Added one related database name to alert info to help service ticket automation.
• 2023-06-23: v1.0.2.1 – Fixed minor DNN discovery issue.
• 2023-06-22: v1.0.2.0 – Added DNN support
• 2023-05-25: v1

Download:

Requirements:

• SCOM 2016 (minimum)
• Minimum version 7.0.15.0 of the following MPs:
  Microsoft.SQLServer.Core.Library
  Microsoft.SQLServer.Core.Views
  Microsoft.SQLServer.Windows.Discovery

Discovery

This MP contains a single discovery workflow which targets the MSSQL on Windows: Local DB Engine class and uses two PowerShell modules. The first module will query the SQL instance to obtain information about all known SQL Availability Groups of which the instance is a member. The first module passes the query results to the second module which identifies any AG Listeners that are local, then outputs discovery data for the AG Server Role, AGs, and local Listeners. The default frequency of this discovery is relatively infrequent because there are mechanisms in place that will trigger discovery as needed. There is an event detection rule to identify when a SQL listener (IP:port) is activated or deactivated on a SQL server which will trigger AG discovery immediately on the affected node. Additionally, the monitor will detect when a target listener is no longer associated with the SQL instance and trigger a discovery (details below).

Monitoring

This MP contains a single unit monitor: SQL AOAG Local Listener Connection Test Monitor

This monitoring workflow is best described in the following steps: 

1. Query the SQL instance and obtain info for all known availability groups (AG)s for which the instance is affiliated.

2. The AG info is used to identify each AG listener as either local or remote.

• Remote: a property bag is returned with a value indicating the “not local” status and the name of the listener. The agent does not test the remote listener.
• Local: the listener is tested with a very simple query which validates the endpoint. Query success or failure is indicated in the property bag along with the listener name.

3. The monitor type filters the property bags by listener name and status to determine health state.

Health state outcome is very straightforward for local listeners; either the query was a success (healthy) or it was not (critical).

For listeners flagged as remote, if the monitor target listener name matches the listener name in the property bag, this indicates that the discovery is outdated because the listener (IP address resource) is no longer owned by the instance and has moved to a different cluster node. This scenario produces a warning health state outcome. There is no alert for the warning state. There is an automatic recovery attached to the warning state of the monitor which triggers the AG discovery immediately on the outdated node. Immediately the listener should get undiscovered on its previous owner/host.

Between the monitor recovery action and the event detection, discovery should be able to maintain accurate ownership information for the AGs and listeners.

Listener Test Task

Discovery Trigger Task

A discovery task exists for the AG Server Role instances to force discovery if you suspect that listener ownership is not updated for some reason.

Class Graph

(provide by SCOMHelper)