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

I’ve developed a stored procedure to run on SQL Server Agent Job, to monitor the synchronism status from AlwaysOn Availability Groups.
In case of “NO HEALTHY” status, the SP will send you an email providing details of the database.

Pre-Requirements

– DatabaseMail Instaled

So, to set up the alert properly, I execute the stored procedure above in a job task (every 5 or 10 minutes) on the primary instance, passing ‘Primary’ as parameter.

EXEC [pr_monitor_alwayson] 'PRIMARY'

On the “expected” secondary replica, I pass ‘SECONDARY’ as parameter using the same stored procedure

EXEC [pr_monitor_alwayson] 'SECONDARY'

Once you need to force a failover, you should just change the parameters in both instances.

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 with more details about each event that occur in AlwaysOn Availability Groups.

Please, let me know if you have any questions.

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

  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

    • Hmm…
      Are you passing the correct parameter on primary and secondary replica?
      It should be like this:
      EXEC [pr_monitor_alwayson] ‘PRIMARY’ –On Primary replica
      EXEC [pr_monitor_alwayson] ‘SECONDARY’ –On Secondary replica

      Like

  2. I’ve got it!
    What I usually do, is pretty much like the example above..
    I execute one job task on the primary instance, passing as parameter ‘Primary’

    EXEC [pr_monitor_alwayson] ‘PRIMARY’ –On Primary replica

    On the “expected” secondary replica, I pass ‘SECONDARY’ as parameter using the same stored procedure
    EXEC [pr_monitor_alwayson] ‘SECONDARY’ –On Secondary replica

    Once you need to force a failover, you should just change the parameters.

    Liked by 1 person

  3. Thanks a lot.
    I tested and its working fine if AG is running on y node triggering the alert
    with AG dashboard

    EXEC [pr_monitor_alwayson] ‘SECONDARY’

    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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s