How to set alert when AlwaysOn High Availabilty database stop synchronizing

  • santosh.mane

    Mr or Mrs. 500

    Points: 593

    Hi,

    We are having our production database on AlwaysOn High Availability, it sometime stopped synchronizing with secondary DB node.

    We want to set alert and receive email when AlwaysOn High Availabilty database stop synchronizing with Secondary Node .

    Can you please advise How to set alert and receive email when AlwaysOn High Availabilty database stop synchronizing with secondary node?

    Appreciate your support and co-operation.

    Thanks & Regards,

    Santosh

     

  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • santosh.mane

    Mr or Mrs. 500

    Points: 593

    Please can someone help to resolve this issue

  • santosh.mane

    Mr or Mrs. 500

    Points: 593

    Please can someone urgently help to resolve this issue

  • as1981

    SSCrazy

    Points: 2375

    Edit to remove original reply. I misunderstood the question.

  • as1981

    SSCrazy

    Points: 2375

    Are you using a failover cluster or an availability group? If you are using an availability group then the dmv's here https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/always-on-availability-groups-dynamic-management-views-functions?view=sql-server-2017 might help you build some monitoring. I know they are available in 2016 but I am not sure if they are available in 2012.

    If the DMV's are available to you then you might be able to setup some SQL agent alerts, or scheduled tasks, to run queries to check the DMV results and alert you as necessary. I can't advise any details on that as I haven't used 2012 and don't have access to a test instance.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88082

    There are a whole set of agent alerts that you can define for AG.  I wrote the following script to add the alerts on my AOAG systems:

        Set Nocount On;

    Declare @alertName sysname
    , @thisErrorNumber varchar(6)
    , @sqlCommand nvarchar(max) = ''
    , @operatorName sysname = 'Database Administration';

    Declare @errorNumbers Table (ErrorNumber varchar(6), AlertName varchar(50));

    Insert Into @errorNumbers
    Values ('1480' , 'AG Role Change (failover)')
    , ('976' , 'Database Not Accessible')
    , ('983' , 'Database Role Resolving')
    , ('3402' , 'Database Restoring')
    , ('19406', 'AG Replica Changed States')
    , ('35206', 'Connection Timeout')
    , ('35250', 'Connection to Primary Inactive')
    , ('35264', 'Data Movement Suspended')
    , ('35273', 'Database Inaccessible')
    , ('35274', 'Database Recovery Pending')
    , ('35275', 'Database in Suspect State')
    , ('35276', 'Database Out of Sync')
    , ('41091', 'Replica Going Offline')
    , ('41131', 'Failed to Bring AG Online')
    , ('41142', 'Replica Cannot Become Primary')
    , ('41406', 'AG Not Ready for Auto Failover')
    , ('41414', 'Secondary Not Connected');

    Declare cur_ForEachErrorNumber Cursor Local fast_forward
    For
    Select *
    From @errorNumbers;

    Open cur_ForEachErrorNumber;
    Fetch Next From cur_ForEachErrorNumber Into @thisErrorNumber, @alertName;

    While @@fetch_status = 0
    Begin

    If Not Exists(Select *
    From msdb.dbo.sysalerts s
    Where s.message_id = @thisErrorNumber)
    Begin

    Execute msdb.dbo.sp_add_alert
    @name = @alertName
    , @message_id = @thisErrorNumber
    , @severity = 0
    , @enabled = 1
    , @delay_between_responses = 0
    , @include_event_description_in = 1
    , @job_id = N'00000000-0000-0000-0000-000000000000';

    Execute msdb.dbo.sp_add_notification
    @alert_name = @alertName
    , @operator_name = @operatorName
    , @notification_method = 1;

    Raiserror('Alert ''%s'' for error number %s created.', -1, -1, @alertName, @thisErrorNumber) With nowait;
    End

    Fetch Next From cur_ForEachErrorNumber Into @thisErrorNumber, @alertName;
    End

    --==== Close/Deallocate cursor
    Close cur_ForEachErrorNumber;
    Deallocate cur_ForEachErrorNumber;


    Additionally - you want to monitor for RTO and RPO.  This document outlines several methods available...I set my systems up to use policies to monitor for RTO and RPO.

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-2017

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • santosh.mane

    Mr or Mrs. 500

    Points: 593

    thank you for your reply.

    Please let me know if it will create any performance issue

     

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88082

    Alerts will not cause any performance issues.  Checking policies won't cause any performance issues.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • santosh.mane

    Mr or Mrs. 500

    Points: 593

    Hi Jiffrey,

    Thanks for the reply.

    Can you please share the steps as to how to set a job to run the code shared above and sent email when any error sysalerts.

    Appreciate your support and co-operation.

    Thanks & Regards,

    Santosh

     

     

  • santosh.mane

    Mr or Mrs. 500

    Points: 593

    Hi Jiffrey,

    Can you please share the steps as to how to set a job to run the code shared above and sent email when any error sysalerts.

    Appreciate your support and co-operation.

    Thanks & Regards,

    Santosh

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88082

    Alerts don't need a job... they will fire based on the condition.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply