August 18, 2019 at 3:24 am
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
August 19, 2019 at 4:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 21, 2019 at 5:25 am
Please can someone help to resolve this issue
August 22, 2019 at 9:53 am
Please can someone urgently help to resolve this issue
August 22, 2019 at 10:49 am
Edit to remove original reply. I misunderstood the question.
August 22, 2019 at 6:50 pm
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.
August 22, 2019 at 9:06 pm
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.
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
August 27, 2019 at 2:39 pm
thank you for your reply.
Please let me know if it will create any performance issue
August 28, 2019 at 2:17 am
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
August 28, 2019 at 12:21 pm
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
September 6, 2019 at 9:03 am
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
September 6, 2019 at 12:54 pm
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
February 27, 2024 at 4:23 am
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply