Like Part 1 and Part 2, I use a similar query to find latency in AlwaysOn Availability Groups. The queries will return one line for each DB in the group. Part 1 , we looked at a simple query to grab the current status of the replica. Part 2, we looked at populating a Global Temp table every minute to gain a historical perspective of our AG latency. This query will look into creating an email notification when certain criteria is met. It will send the current status to a specific set of email recipients.
The example will be around specific criteria set as a step used in a SQL Agent job. If you use something like this, you need to tweak it to you organization’s needs. I do suggest that you set the job to a frequency that will not overflow your inbox with notices.
Send an email from server when latency for a specfic database datbase is over x number of minutes.
- Alter If statement > value for specific needs. This example fires of fthe email when latency is over 60 minutes.
- Alter “database_id = DB_ID(‘MyAGDatabase’)” to specify datbase to monitor
- Alter “@recipients=N’[email protected];” to specify recipients. I suggest using a email distribution list over listing spefic emails.
The example is pretty simple and intended for demonstration purposes.