I need a script to monitor always on primary failover to secondary, that should trigger a mail alert.
I have created a script but the problem is it works fine , but since the job runs on both primary and secondary server.Since when it runs on secondary it gives out mail alert.I want it to run only in primary not when the server is secondary.
if exists(select is_local, role_desc from sys.dm_hadr_availability_replica_states where role = 1 and role_desc = 'PRIMARY') begin
print 'This server [' + upper(@@servername) + '] is the primary.' end
Can you pls help how can i supress it in secondary server , currently i have to disable the job .
Thanks in advance.
Answer by anthony.green ·
1480 is another message_id to track, this tracks the failovers (
The %S_MSG database "%.*ls" is changing roles from "%ls" to "%ls" because the mirroring session or availability group failed over due to %S_MSG. This is an informational message only. No user action is required.)
19406 tracks a replica going into offline suspended failover states
theres 377 messages which are for availability group alerting, not all of them will trigger a failover
Answer by sandy_1006 ·
Thank you for your reply.
The real issue is when ever there is a failover , the events no# are always different so our alerts does not work .For example we had a cluster issue the error code generated was 19476 and once we had a quorum issue the eror code was 32465 all the tiime the errors codes generated are different.So the alerting mechanism fails to give alert.