none
SQL Server Alerting Only Half Working RRS feed

  • Question

  • SCOM 2012 R2 and SQL 2019.

    I have a SQL Server that is not alerting on certain things. For example, if I write an alert to the event log that I have created a bespoke rule on then SCOM alerts. If  there is a backup or login failure it doesn't.

    I have reinstalled multiple times, taken off AV, renamed old SCOM folders and tried everything I can think of including rebooting everything.

    These alerts all work on other similar SQL Servers so I know SCOM itself is OK, there's just something about this particular server - any ideas please?

    Monday, November 18, 2019 4:11 PM

Answers

  • Hi. Well I resolved it. Odd one. The RunAs SQL Server account (login) I use on the troublesome SQL Servers is set to British English language in SQL. If I change the login to English (which is US) and reboot then it works.  So the monitors that look after backup failures don't like the British English format.

    • Marked as answer by Birley Friday, November 22, 2019 11:31 AM
    Friday, November 22, 2019 11:31 AM

All replies

  • Hi,

    For example, if I write an alert to the event log that I have created a bespoke rule on then SCOM alerts. If  there is a backup or login failure it doesn't.

    What does this mean? Do you mean that we've created a rule based on Event ID, for testing purpose, we manually generates an Event ID, however, the expected alert hasn't been triggered? If there is any misunderstanding, please kindly correct me.

    In addition, you mentioned that the problematic server is SQL Server 2019 and similar SQL Servers are working. Are the similar SQL Servers also SQL 2019?

    Also, do we use MP version 7.0.15.0 in our environment?
    Microsoft System Center Management Pack for SQL Server
    https://www.microsoft.com/en-us/download/details.aspx?id=56203

    Hope the above information helps.

    Regards,

    Alex Zhu
    -----------------------------------------------
    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Tuesday, November 19, 2019 1:35 AM
  • Besides Alex Zhu suggestion ,pls also check your OS version.

    SQL 2019 supports windows OS
    Windows Server 2019 and Windows Server 2016

    Operations Manager 2012 R2 Windows Agent supports OS
    Windows Server 2016

    If you install SQl 2019 into windows 2019, SCOM 2012 R2 cannot support on monitoring this machine.

    Roger
    Tuesday, November 19, 2019 3:00 AM
  • Apologies, I meant SQL2017 with Windows server 2016. Long day!

    Yes, Rules and SQL Agent alerts seem to fire, but the monitors don't.

    For example, we can see network traffic happening to the SCOM server after a rule fires based on an event, but nothing happening after a backup fails, which also writes to the event log.

    I can confirm that everything works as expected on another server with the same W2016/SQL2017 build

    Tuesday, November 19, 2019 9:33 AM
  • Hi,

    Do you mean that when there is a failure, for example, database backup, we observed the failure manually, however, the monitoring system (operations manager) didn't trigger an alert? In operations manager, we use rules/monitors to help judging the monitored objects' health and triggers the alert. However, we need to configure which monitors/rules are applied to which monitored objects.

    Since we've similar database server to compared with, we may check the rule/monitor which generates the alerts and check if it has also applied to the (problematic) server. Is there any overrides? Or the (problematic) server has been added to a specified group?

    Hope the above information helps.

    Regards,

    Alex Zhu
    -----------------------------------------------
    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Wednesday, November 20, 2019 6:13 AM
  • Hi Alex. Yes, I can trigger a fake backup error that writes to the SQL and event logs. SCOM is not picking it up. No overrides. No groups. I can see that the server has been picked up by SCOM and it's registering the correct version and it's all green.

    How do I check that a rule/monitor has been applied to the server please? 

    Wednesday, November 20, 2019 9:30 AM
  • Hi,

    you can check the effective monitornig configuration of a server, using this script:

    SCOM: Export Effective Monitoring Configuration with PowerShell

    You mentioned that rules are workig, only your configured monitors aren't. Can you please post a couple of screenshots with their config and how do you target the monitos. Let's see how we can help.

    Anpther thing: Please check the OPerations Manager Event log on the agent side and look for events rearding failed workflows.

    Post here and we will do our best to help.

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Wednesday, November 20, 2019 11:26 AM
    Moderator
  • Hi. Well I resolved it. Odd one. The RunAs SQL Server account (login) I use on the troublesome SQL Servers is set to British English language in SQL. If I change the login to English (which is US) and reboot then it works.  So the monitors that look after backup failures don't like the British English format.

    • Marked as answer by Birley Friday, November 22, 2019 11:31 AM
    Friday, November 22, 2019 11:31 AM
  • Hi. Well I resolved it. Odd one. The RunAs SQL Server account (login) I use on the troublesome SQL Servers is set to British English language in SQL. If I change the login to English (which is US) and reboot then it works.  So the monitors that look after backup failures don't like the British English format.

    Hey,

    cool, thanks for sharing :)

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Friday, November 22, 2019 12:05 PM
    Moderator