Inconsistent error running Set-SqlAvailabilityReplica cmd

I'm relatively new to powershell and I am hoping to get some help with an error I'm getting when trying to use Powershell to change the 'failover mode' on the availability replicas in an SQL Always On cluster.

In our test environment, I am able to make the change without error against the six Always On Availability Groups that we have set up. However, in production, I am consistently getting an error when running the Set-SqlAvailabilityReplica command against the three Always On Availability Groups that we have set up in that environment.

The commands I am running:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server('OurSQLServer')
$SqlServer.AvailabilityGroups[0].AvailabilityReplicas[1] | Set-SqlAvailabilityReplica -FailoverMode 'Automatic'

#$SqlServer.AvailabilityGroups[0].AvailabilityReplicas | select Name, FailoverMode | Format-Table -AutoSize
#$SqlServer.AvailabilityGroups[0].AvailabilityReplicas[1] | select Name, FailoverMode | Format-Table -AutoSize

The error I am getting:

Set-SqlAvailabilityReplica : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and
its properties do not match any of the parameters that take pipeline input.
At line:1 char:60
+ $SqlServer.AvailabilityGroups[0].AvailabilityReplicas[1] | Set-SqlAvailabilityRe ...
+                                                            ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: ([OurSQLServer]:AvailabilityReplica) [Set-SqlAvailabilityReplica], ParameterBindingException
    + FullyQualifiedErrorId : InputObjectNotBound,Microsoft.SqlServer.Management.PowerShell.Hadr.SetSqlAvailabilityReplicaCommand



I did compare the powershell versions between test and production using the $psversiontable command and the version info matches. So I'm not sure what else could explain the difference in behavior.

Any help is appreciated. Thank you.


  • This is not a PoSH version issue, because the error show is pretty direct.

    There is nothing ( aproperty name) in this...

    which matched anything in that (by property name)...
    | Set-SqlAvailabilityReplica -FailoverMode 'Automatic'

    If you do a Get-Member of each individually, you'll see what can and cannot be matched up.

    PoSH is a property / value match issue. If a matching property name does not exits on both sides, then no match. Since you at a DB-Type person. Think of this like Foreign key relationships between tables.

    See the details below for explanation and approaches, like calculated properties, etc.

    Learn About Using PowerShell Value Binding by Property Name

    Cool Pipeline Tricks, Redux
  • Thank you for your feedback. I understand how a property/value mismatch can be an issue. But what is really throwing me off is that this same command works fine in our test environment. In that environment, the command ran without error against 6 different availability groups. However, in production, it fails against the 3 availability groups that exist there. Same version of powershell, same version of SQL. So I'm not understanding what the difference is.
    But running get-member on an availability group in each environment and then checking for differences is a good suggestion. I will give that a try and hopefully, that gives me a clue.