How to set alert when AlwaysOn High Availabilty database stop synchronizing

Question

Post reply

How to set alert when AlwaysOn High Availabilty database stop synchronizing

santosh.mane

SSChasing Mays

Points: 611
More actions
August 18, 2019 at 3:24 am

#3672109

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

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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

santosh.mane SSChasing Mays Points: 611 More actions · Answer 2

santosh.mane

SSChasing Mays

Points: 611

August 21, 2019 at 5:25 am

#3673005

Please can someone help to resolve this issue

santosh.mane SSChasing Mays Points: 611 More actions · Answer 3

Please can someone urgently help to resolve this issue

as_1234 SSCrazy Points: 2859 More actions · Answer 4

Edit to remove original reply. I misunderstood the question.

as_1234 SSCrazy Points: 2859 More actions · Answer 5

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 SSC Guru Points: 90303 More actions · Answer 6

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

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

santosh.mane SSChasing Mays Points: 611 More actions · Answer 7

thank you for your reply.

Please let me know if it will create any performance issue

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

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

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

santosh.mane SSChasing Mays Points: 611 More actions · Answer 9

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 SSChasing Mays Points: 611 More actions · Answer 10

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 SSC Guru Points: 90303 More actions · Answer 11

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

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

atafakhari@gmail.com Grasshopper Points: 11 More actions · Answer 12

Hi Jeffrey,

Thank you for the script. However, I recommend changing the @delay_between_responses to at least 120 (which is equal to 2 minutes). Otherwise, you will receive thousands of emails for a simple restart on one of the servers.

Thanks,

Ata

This reply was modified 9 months ago by atafakhari@gmail.com.