Share via

[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.
13,361 questions
0 comments
Accepted answer
  1. Bjoern Peters 8,856 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
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,461 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.