Monitoring your SQL Mirroring

Here's an alert for mirroring. Just uncomment the last line to see how far behind all your mirrors are.


Monitoring Availability Groups

I needed a way to monitor our Availability Groups (AG's) and get an idea of when one had fallen behind. Sometimes we'll have the problem where redo doesn't get applied to the secondary and it can cause issues with the primary running itself out of space with a logjam.

There's a plethora of DMV's to provide insight into AG's. Spinning up a custom solution was easier than I anticipated.

Here's a query to output the names of your databases and how many minutes behind they are. If it's less than 90 minutes (or whatever threshold you define for your environment) then it'll return NULL and zero respectively.

I ran into a few issues where it would return NULL for the minutes and in every case it turned out to be a problematic AG. So for occasions when the query can't calculate the lag time of redo being applied to the secondary I defaulted to '1-1-1900' which will still trigger the alerting threshold of 90 minutes unless we slip into some sort of time warp.

(This query is meant to be plugged into your favorite monitoring tool - hence the version number check.)