Microsoft SQL 2012 roles - alert when role moves from one node to another

WW-6729 371 Reputation points
2020-09-16T16:05:19.737+00:00

Hello!

Is it somehow possible to get an alert when roles move from one sql node to another? Maybe by using SCOM or Powershell script?

We have geo-cluster of SQL 2012 and from time to time roles move from first to secondary node and that causes us some trouble.

Thank you!

Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,417 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,755 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,381 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Crystal-MSFT 43,221 Reputation points Microsoft Vendor
    2020-09-17T01:36:03.317+00:00

    @GK-6729, From your description, it seems that you want to monitor SQL server failover. If there's any misunderstanding, feel free to let us know.

    Based on my research, I find an article describe the steps to monitor SQL Server failover events. We can read it as a reference.
    https://solutioncenter.apexsql.com/how-to-monitor-sql-server-failover-events-automatically/
    Note: Non-microsoft link, just for the reference.

    Hope it can help.

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. SChalakov 10,261 Reputation points MVP
    2020-09-17T08:37:47.317+00:00

    Hi @GK-6729,

    Are you using the SQL Server Version Agnostic Management Pack? If not, I would recommend installing it, as this is the supported MP for monitoring SQL Versions from 2012 and newer.
    So if you use it, you should actually get alerts, as there is a rule (Enabled by default), which does cover this.
    The name of the rule is "MSSQL on Windows: Database Replica Role Changed" and it fires and Informational alert:

    Microsoft.SQLServer.Windows.EventRule.DatabaseReplica.RoleChanged (Rule)
    https://systemcenter.wiki/?GetElement=Microsoft.SQLServer.Windows.EventRule.DatabaseReplica.RoleChanged&Type=Rule&ManagementPack=Microsoft.SQLServer.Windows.Monitoring&Version=7.0.20.0

    It fires when it detects the event with the ID 1480.

    I hope I could bring some clarity here.

    (If the reply was helpful please don't forget to upvote or accept as answer, thank you)
    Best regards,
    Stoyan

    0 comments
  3. WW-6729 371 Reputation points
    2020-10-23T05:54:47.707+00:00

    Thank you guys! I will try with StoyanChalakov method. I'm trying to have unified monitoring system by using SCOM.

    0 comments