Sql-server – Powershell SQL Server High Availability PrimaryReplicaServerName – inconsistent results

high-availabilitypowershellsql server

I developed a simple PowerShell script to list which High Availability replica is the current Primary. Seemed to work. Did a manual failover to test it, but results become inconsistent, often describing both replicas as Secondary.
Simple illustration code:

Import-Module -Name SQLPS -DisableNameChecking
"Server1:"
cd \sql\Server1\WSSAPPS\AvailabilityGroups
gci | select Name, LocalReplicaRole, PrimaryReplicaServerName
""
"Server2:"
cd \sql\Server2\WSSAPPS\AvailabilityGroups
gci | select Name, LocalReplicaRole, PrimaryReplicaServerName

If Server2 is Primary AND I run the code on the Server2, the results seem fine:

Server1:

Name                 LocalReplicaRole      PrimaryReplicaServerName             
----                 ----------------      ------------------------             
SP-STAGE-APPS               Secondary      Server2\WSSAPPS                  

Server2:

SP-STAGE-APPS                 Primary      Server2\WSSAPPS

But if I run exactly the same code on Server1 (cut and paste) with Server2 still as Primary I get different results:

Server1:

Name                 LocalReplicaRole      PrimaryReplicaServerName            
----                 ----------------      ------------------------            
SP-STAGE-APPS              Secondary       Server2\WSSAPPS                 

Server2:
SP-STAGE-APPS              Secondary       Server2\WSSAPPS

This is now reporting BOTH replicas have the role Secondary!
Can anyone explain why these results should differ?
Please don't say 'Just always run it on the primary then' The whole point is to give people the information as to which instances are primary! I've got 11 AGs on 12 replicas to monitor.
Once I understand what's happening I can mod my main script code accordingly.
Thanks

Best Answer

Whether you run that code while you are on the primary or secondary is irrelevant. When your path is SQLSERVER:\SQL\SERVERx\INSTANCE... you are technically running it against that server, it does not matter if you ran the command from the server or a random desktop in the domain.

The issue with it showing as secondary though is local. I do not get that on my servers. If, you had PowerShell and the provider opened on "Server1" when you failed it over then I would suggest opening a new PowerShell prompt and see if it shows the same thing. If it does then try from another computer all together.

Related Question