I have developed a T-SQL script which will trigger the Dashboard report whenever there is an issue with any of the node either Primary or Secondary. To schedule this script, Place it in SQL agent job.
This job should be configured on all primary and secondary nodes. This job will run on all nodes and check whether there is any issue with any of the database(s) or node(s) on all primary and secondary nodes. If it founds anything wrong like database is in “not synchronizing” state, in such cases, this job will trigger the email to recipient(s). This email will carry the AG Dashboard report which will tell us about the status of all database(s) and node(s) belongs to the different AG’s.
This script is divided in two different parts. First part will check whether the current node is Primary, If current node is primary then execution will go to the second part otherwise it will complete without generating any report. In my environment, I have configured a 2 steps(separate step for each part) job which runs in every 15 mins.
Pls use this script and share feedback with me.