[Powershell] How to health check 2 AG's on one server.

PeterVR 26 Reputation points
2022-08-23T14:35:45.46+00:00

I have 3 servers, running two Availability Groups.
Node a is the active node for both AG's, node b is a synchronous replica, node s is an asynchronous replica on a remote site.

One AG is listening to the default port 1433, the other uses port 1435.

I'm trying to script a health monitoring tool for all of my AG's. This one cluster with 2 AG's only returns results for the AG that listens on 1433.

This is what I get:
Situation:
• hardware:
servers: sqlGREEN3a (=primary node), sqlGREEN3b, sqlGREEN3s
• first AG:

  • listener: sqlGREEN3
  • AG name: sqlGREEN3_AG
    • second AG:
  • listener: sqlRED
  • AG name: sqlRED_AG

All of this works perfectly. But the monitoring fails:

234132-ps-1.gif

Here, of course, i Expected the Name sqlRED_AG and primary replica sqlGREEN3a\sqlRED.

Obviously, the health checks also only give the GREEN results.

234020-ps-health.gif

How can I retreive the RED AG health status in Powershell?

For the sake of completeness: Microsoft.SqlServer.Management.Smo.Server("sqlGREEN3") gives the same result as Microsoft.SqlServer.Management.Smo.Server("sqlGREEN3a") or Microsoft.SqlServer.Management.Smo.Server("sqlRED")

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,605 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bjoern Peters 8,781 Reputation points
    2022-08-23T16:22:22.76+00:00

    I didn't have had such a problem check the health state so just a guess... maybe it is something with your (connect) rights...

    The AG itself runs (and connects) with the service-user which (may) has different (connect) rights then your (monitoring) user...

    Maybe something went wrong during the setup of the second AG and it is missing some grants or someone explicitly revoked some users those connect rights...

    PS: I tried to provoke your error but was not "successful". I was always able to get all two AGs and their health status.


1 additional answer

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,436 Reputation points
    2022-08-24T08:03:39.573+00:00

    Hi @PeterVR ,

    Welcome to Microsoft Q&A!
    There are two examples in this document:
    Example 2: Evaluate the health of all availability group

    Get-ChildItem "SQLSERVER:\Sql\Server\InstanceName\AvailabilityGroups" | Test-SqlAvailabilityGroup  
    

    This command gets all availability groups that have availability replicas in the specified location in the SQLSERVER: provider. The command passes them to the current cmdlet by using the pipeline operator. That cmdlet evaluates the health of each availability group.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.