0

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

  • Does Get-DbaAvailabilityGroup help you ? Study the code to get more info. – Kin Shah Mar 10 '17 at 15:41
  • I'll check this out @Kin, particularly as it was written by Shawn Melton who successfully answered below. – DiamondBeezer Mar 13 '17 at 11:34
2

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.

  • trying on a different server still gave inconsistent results. However opening another ISE session and running from there was always 100% accurate on all three servers. All part of the learning curve! And at least all the testing by manual failovers showed my Alerts are working! Thanks. – DiamondBeezer Mar 13 '17 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.