question

sandy_1006 avatar image
sandy_1006 asked

sql server always on failover monitoring

Hi Experts,

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
else
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'',
@body='',
@subject ='',
@profile_name =''


END

Can you pls help how can i supress it in secondary server , currently i have to disable the job .

Thanks in advance.

always-onsql server 2017availability-groups
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Use alerts instead of a job.

There are specific alerts for AG's which will tell you when states of replicas change. 19406 I believe is the right alert to capture

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sandy_1006 avatar image
sandy_1006 answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.