How to Monitor AlwaysON – Primary / Secondary Replicas on SQL Server

Last month I developed a procedure to run in a SQL Server Agent Job, to monitor the synchronism status of the AlwaysOn Availability Groups.
In case of “NO HEALTHY” status, the SP will send an email providing details of every databases.

Pre-Requirements

– DatabaseMail Instaled

Ex:
EXEC [pr_monitor_alwayson] ‘PRIMARY’ –On Primary replica
EXEC [pr_monitor_alwayson] ‘SECONDARY’ –On Secondary replica

Related Links:

Monitor Availability Groups (Transact-SQL)

In the next post, I’m going to provide scripts of alerts that I usually configure to complement details about each event that occur in AlwaysOn Availability Groups.

I hope this helps you. If you have any doubt, please let me know.

Advertisements

6 comments

  1. Syntax does not seem to be correct. Getting an error:
    Msg 102, Level 15, State 1, Procedure pr_monitor_alwayson, Line 20
    Incorrect syntax near ‘;’.
    Msg 102, Level 15, State 1, Procedure pr_monitor_alwayson, Line 39
    Incorrect syntax near ‘;’.

    Like

      1. Hi. No, it supposed to send just when there is something wrong. You should change the parameter according to witch server your.
        Ex: EXEC [pr_monitor_alwayson] ‘PRIMARY’ –On Primary replica
        EXEC [pr_monitor_alwayson] ‘SECONDARY’ –On Secondary replica

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s