Hello.

We have several two-nodeSQL Server Clusters running inActive-Passive mode.
In this mode, the primary service of the SQL Server instance has a startup type ofManual on both cluster nodes.
On theactive cluster node, the service should be in theRunning state, and on thepassive cluster node, the service should be in theStopped state. This is the normal behavior of a service in a cluster.

However, the Invoke-IcingaCheckMSSQLHealth cmdlet does not take advantage of this specificity of the service in the cluster and returns an incorrect critical service status.

Current result on the active cluster node (hostname SRV-DB21):

Invoke-IcingaCheckMSSQLHealth -IntegratedSecurity -SqlHost DBCL22 -Verbosity 2[OK] MSSQL Health (SCVMM)\_ [OK] Connection Time: 0.003s\_ [OK] Service "SQL Server (SCVMM) (MSSQL$SCVMM)": Running| 'scvmm::ifw_mssqlhealth::connectiontime'=0.003s;; 'mssqlscvmm::ifw_mssqlhealth::state'=4;;40

Current result on the passive cluster node (hostname SRV-DB22)::

Invoke-IcingaCheckMSSQLHealth -IntegratedSecurity -SqlHost DBCL22 -Verbosity 2[CRITICAL] MSSQL Health (SCVMM) [CRITICAL] Service "SQL Server (SCVMM) (MSSQL$SCVMM)" (Stopped)\_ [OK] Connection Time: 0.098s\_ [CRITICAL] Service "SQL Server (SCVMM) (MSSQL$SCVMM)": Stopped is not matching threshold Running| 'scvmm::ifw_mssqlhealth::connectiontime'=0.098s;; 'mssqlscvmm::ifw_mssqlhealth::state'=1;;42

To determine whether a SQL Server instance is clustered and which cluster node is currently active, we can use the document:
https://learn.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN

First, we can check whether the SQL Server instance is clustered using a SQL query:

Select ServerProperty('IsClustered')

A value of "1" indicates that the instance is clustered.
A value of "0" indicates that it is a simple non-clustered instance.

This SQL query to get the ComputerNamePhysicalNetBIOS server property can return the name of the active node of the SQL Server cluster:

Select ServerProperty('ComputerNamePhysicalNetBIOS')

Example of returned data:

SRV-DB21

Then in PowerShell, using$env:computername, we can find out the name of the host on which the Invoke-IcingaCheckMSSQLHealth cmdlet is currently running.

Example of returned data:

SRV-DB21

If the host name obtained from the environment variable and the name of the active SQL Server node obtained from the SQL querymatch, then the cmdlet is running on the active cluster node and the SQL Server service should have theRunning state.

Otherwise, if the host name obtained from the environment variable and the name of the active SQL Server node obtained from the SQL querydo not match, then the cmdlet is running on the passive cluster node and the SQL Server service may have theStopping state.

Please change the logic of checking the state of the SQL Server service using this feature of working in a cluster