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.