SCOM does not alert when the "sql server agent" stops stops on a server with multiple instances installed

Brian W 121

Like the title says, on a server with multiple SQL instances installed, SCOM does not alert when one of those instanstances of "SQL Agent Service" stops. On single instance servers the monitor works just fine and will alert when the service stops. I'm using the latest SQL MP (7.4.0) on SCOM 2022 UR2. Anyone have any ideas?

1 answer

XinGuo-MSFT 14,536 Reputation points

2024-04-29T07:59:01.8933333+00:00

Hi,

It seems like you’re experiencing an issue with SCOM not alerting when the “SQL Server Agent” service stops on a server with multiple SQL instances.

Here are a few things you might want to check:

The Management Pack for SQL Server provides several SQL Server agent alerting rules. By default, these rules are enabled in agent monitoring mode, but disabled in mixed monitoring mode.

In agent monitoring mode, the management pack automatically discovers standalone and clustered instances of SQL Server across all managed systems that run the System Center Operations Manager agent service.

In **Mixed monitoring **mode supports SQL on Windows only. In this monitoring mode, the management pack places its seed on each computer that has the System Center Operations Manager agent. This seed is then used to automatically discover all SQL Server on Windows instances.

For more information, please refer to the link below:

https://learn.microsoft.com/en-us/system-center/scom/sql-server-management-pack-monitoring-modes?view=sc-om-2022#configuring-mixed-monitoring-mode
Please sign in to rate this answer.
Brian W 121 Reputation points

2024-04-29T12:16:28.9466667+00:00

I enabled mixed mode, and I see no change. What I find interesting is that the SQL objects are discovered, but they don't show up in health explorer on the windows computer object as a monitored object.

Brian W 121 Reputation points

2024-04-30T10:01:55.9566667+00:00

Also, after enabling mixwed mode I'm getting these errors on my MS.

Log Name: Operations Manager

Source: SQL Server Discovery MP Windows

Date: 4/30/2024 5:55:09 AM

Event ID: 4221

Task Category: None

Level: Error

Keywords: Classic

User: N/A

Computer: T000000SCOM0001.TAMCENTRAL.ASP

Description:

Management Group: "CS-SCOM"

Module: Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.DatabaseDiscovery

Version: 7.4.0.0

Error(s) was(were) occurred:

Message: An error occurred during discovery.

---------- Exception: ----------

Exception Type: Microsoft.SQLServer.Module.Helper.Sql.SmartConnectException

Message: Cannot connect to the target Sql Server instance.

Connection log:

Failed to connect to data source 'VTAM0CRSQLP0001.TAMCENTRAL.ASP\FAX': A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

Error number:-2146893007

Failed to connect to data source 'VTAM0CRSQLP0001.TAMCENTRAL.ASP,63004': A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

Error number:-2146893007

Source: Microsoft.SQLServer.Module4.Helper

Stack Trace:

at Microsoft.SQLServer.Module.Helper.Sql.SqlConnectivityHelper.<SmartConnectAsync>d__8.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<GetNewOpenedConnectionInternalAsync>d__46.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<InitConnectionAsync>d__47.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandInternalAsync>d__41`1.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandWrapperAsync>d__45`1.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.DatabaseDiscovery.<FillListsOfClassesAndRelationsAsync>d__19.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.Base.DataItemHelper.<GetDiscoveryDataAsyncStatic>d__6.MoveNext()

State:

The configuration properties are:

ManagementGroupName = CS-SCOM

Publisher = SQLDiscoveryWindows

ConnectionString = VTAM0CRSQLP0001.TAMCENTRAL.ASP\FAX

InstanceEdition = Express Edition

InstanceName = FAX

InstanceVersion = 12.1.4100.1

MachineName = VTAM0CRSQLP0001.TAMCENTRAL.ASP

MonitoringType = Mixed

NetbiosComputerName = VTAM0CRSQLP0001

Login =

ConnectionString = VTAM0CRSQLP0001.TAMCENTRAL.ASP\FAX

DatabaseClassId = 722594ba-bc11-45d5-81a2-a6059ada4682

DatabaseReferencesUserResourcePoolClassId = 505d3154-32c8-4242-48e1-c0531ce8dcae

DefaultPoolClassId = 0fca59ad-48dc-6118-5665-3fdddc183bbc

DiscoverySourceManagedEntityId = 76fdc790-684c-2e16-3b63-e99ed3420f66

DiscoverySourceObjectId = f8651819-6311-a818-3e20-86dc7fe0817f

ExcludeList =

InstanceName = FAX

LocalDatabaseClassId = 0122e31e-8872-ec01-8993-63a4b2eb3d01

MachineName = VTAM0CRSQLP0001.TAMCENTRAL.ASP

SqlExecTimeoutSeconds = 60

SqlTimeoutSeconds = 15

TimeoutSeconds = 300

UserDefinedResourcePoolClassId = c5d88380-0c94-93ce-5379-885862735019

Password = ********

Error(s):

An error occurred during discovery.

---------- Exception: ----------

Exception Type: Microsoft.SQLServer.Module.Helper.Sql.SmartConnectException

Message: Cannot connect to the target Sql Server instance.

Connection log:

Failed to connect to data source 'VTAM0CRSQLP0001.TAMCENTRAL.ASP\FAX': A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

Error number:-2146893007

Failed to connect to data source 'VTAM0CRSQLP0001.TAMCENTRAL.ASP,63004': A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

Error number:-2146893007

Source: Microsoft.SQLServer.Module4.Helper

Stack Trace:

at Microsoft.SQLServer.Module.Helper.Sql.SqlConnectivityHelper.<SmartConnectAsync>d__8.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<GetNewOpenedConnectionInternalAsync>d__46.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<InitConnectionAsync>d__47.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandInternalAsync>d__41`1.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandWrapperAsync>d__45`1.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.DatabaseDiscovery.<FillListsOfClassesAndRelationsAsync>d__19.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.Base.DataItemHelper.<GetDiscoveryDataAsyncStatic>d__6.MoveNext()

Event Xml:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

<System>

<Provider Name="SQL Server Discovery MP Windows" /> <EventID Qualifiers="0">4221</EventID> <Level>2</Level> <Task>0</Task> <Keywords>0x80000000000000</Keywords> <TimeCreated SystemTime="2024-04-30T09:55:09.737722800Z" /> <EventRecordID>60155697</EventRecordID> <Channel>Operations Manager</Channel> <Computer>T000000SCOM0001.TAMCENTRAL.ASP</Computer> <Security />

</System>

<EventData>

<Data>Management Group: "CS-SCOM"

Module: Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.DatabaseDiscovery

Version: 7.4.0.0

Error(s) was(were) occurred:

Message: An error occurred during discovery.

---------- Exception: ----------

Exception Type: Microsoft.SQLServer.Module.Helper.Sql.SmartConnectException

Message: Cannot connect to the target Sql Server instance.

Connection log:

Failed to connect to data source 'VTAM0CRSQLP0001.TAMCENTRAL.ASP\FAX': A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

Error number:-2146893007

Failed to connect to data source 'VTAM0CRSQLP0001.TAMCENTRAL.ASP,63004': A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

Error number:-2146893007

Source: Microsoft.SQLServer.Module4.Helper

Stack Trace:

at Microsoft.SQLServer.Module.Helper.Sql.SqlConnectivityHelper.<SmartConnectAsync>d__8.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<GetNewOpenedConnectionInternalAsync>d__46.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<InitConnectionAsync>d__47.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandInternalAsync>d__41`1.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandWrapperAsync>d__45`1.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.DatabaseDiscovery.<FillListsOfClassesAndRelationsAsync>d__19.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.Base.DataItemHelper.<GetDiscoveryDataAsyncStatic>d__6.MoveNext()

State:

The configuration properties are:

ManagementGroupName = CS-SCOM

Publisher = SQLDiscoveryWindows

ConnectionString = VTAM0CRSQLP0001.TAMCENTRAL.ASP\FAX

InstanceEdition = Express Edition

InstanceName = FAX

InstanceVersion = 12.1.4100.1

MachineName = VTAM0CRSQLP0001.TAMCENTRAL.ASP

MonitoringType = Mixed

NetbiosComputerName = VTAM0CRSQLP0001

Login =

ConnectionString = VTAM0CRSQLP0001.TAMCENTRAL.ASP\FAX

DatabaseClassId = 722594ba-bc11-45d5-81a2-a6059ada4682

DatabaseReferencesUserResourcePoolClassId = 505d3154-32c8-4242-48e1-c0531ce8dcae

DefaultPoolClassId = 0fca59ad-48dc-6118-5665-3fdddc183bbc

DiscoverySourceManagedEntityId = 76fdc790-684c-2e16-3b63-e99ed3420f66

DiscoverySourceObjectId = f8651819-6311-a818-3e20-86dc7fe0817f

ExcludeList =

InstanceName = FAX

LocalDatabaseClassId = 0122e31e-8872-ec01-8993-63a4b2eb3d01

MachineName = VTAM0CRSQLP0001.TAMCENTRAL.ASP

SqlExecTimeoutSeconds = 60

SqlTimeoutSeconds = 15

TimeoutSeconds = 300

UserDefinedResourcePoolClassId = c5d88380-0c94-93ce-5379-885862735019

Password = ********

Error(s):

An error occurred during discovery.

---------- Exception: ----------

Exception Type: Microsoft.SQLServer.Module.Helper.Sql.SmartConnectException

Message: Cannot connect to the target Sql Server instance.

Connection log:

Failed to connect to data source 'VTAM0CRSQLP0001.TAMCENTRAL.ASP\FAX': A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

Error number:-2146893007

Failed to connect to data source 'VTAM0CRSQLP0001.TAMCENTRAL.ASP,63004': A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

Error number:-2146893007

Source: Microsoft.SQLServer.Module4.Helper

Stack Trace:

at Microsoft.SQLServer.Module.Helper.Sql.SqlConnectivityHelper.<SmartConnectAsync>d__8.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<GetNewOpenedConnectionInternalAsync>d__46.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<InitConnectionAsync>d__47.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandInternalAsync>d__41`1.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandWrapperAsync>d__45`1.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.DatabaseDiscovery.<FillListsOfClassesAndRelationsAsync>d__19.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at Microsoft.SQLServer.Module.Helper.Base.DataItemHelper.<GetDiscoveryDataAsyncStatic>d__6.MoveNext()

</Data>

</EventData>

</Event>

Brian W 121 Reputation points

2024-04-30T15:27:35.3+00:00

Because of the error above I chose to disable mixed mode, but not all my engines are changing.
Sign in to comment