Share via

Script Powershell to check Always on status

  • Article

Question

Saturday, October 25, 2014 1:19 PM

hi

I am looking for a script or help to write a script that would give me some information about my state of cluster failover and state groups availability group.

I wish to know my machine node that owns the current host server but also I want to know the state of my availability groups, for example, which is the host and owner and if the group is currently running over)

I have look into get-cluster ... but I did not succeeded to find it's ..

thanks

Eric

All replies (3)

Saturday, October 25, 2014 2:13 PM ✅Answered

I found this in the msdn library:

http://msdn.microsoft.com/en-us/library/hh510210(v=sql.110).aspx

Test-SqlAvailabilityGroup

Assesses the health of an availability group by evaluating SQL Server policy based management (PBM) policies. You must have CONNECT, VIEW SERVER STATE, and VIEW ANY DEFINITION permissions to execute this cmdlet.

For example, the following command shows all availability groups with a health state of "Error" on the server instance Computer\Instance.

Get-ChildItem SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups ` 
| Test-SqlAvailabilityGroup | Where-Object { $_.HealthState -eq "Error" }

Test-SqlAvailabilityReplica

Assesses the health of availability replicas by evaluating SQL Server policy based management (PBM) policies. You must have CONNECT, VIEW SERVER STATE, and VIEW ANY DEFINITION permissions to execute this cmdlet.

For example, the following command evaluates the health of the availability replica named MyReplica in the availability group MyAg and outputs a brief summary.

Test-SqlAvailabilityReplica ` 
-Path SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MyAg\AvailabilityReplicas\MyReplica

Test-SqlDatabaseReplicaState

Assesses the health of an availability database on all joined availability replicas by evaluating SQL Server policy based management (PBM) policies.

For example, the following command evaluates the health of all availability databases in the availability group MyAg and outputs a brief summary for each database.

Get-ChildItem SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MyAg\DatabaseReplicaStates ` 
 | Test-SqlDatabaseReplicaState

Monday, October 27, 2014 6:58 AM ✅Answered

Hi Eric,

In addition to Guido’s post, you can execute the following Windows PowerShell scripts to check SQL Server AlwaysOn availability groups state. For more details, please review this blog: Monitor an AlwaysOn Availability Group with PowerShell.

1. To get  the synchronization state of the availability group databases.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |

  Out-Null
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("servername")
$SqlServer.AvailabilityGroups["AvailabilityGroupsName"].DatabaseReplicaStates |

  Select-Object AvailabilityReplicaServerName,

    AvailabilityDatabaseName,

SynchronizationState
  1. To get the operational and connection states of the availability group databases.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |

  Out-Null
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("servername")

$SqlServer.AvailabilityGroups["AvailabilityGroupsName "].AvailabilityReplicas |

  Where-Object {$_.Name -eq $SqlServer.Name} |

  Select-Object Name,

    Role,

    OperationalState,

    ConnectionState

Thanks,
Lydia Zhang

Wednesday, November 5, 2014 9:47 AM

thanks for this.

now i go try to understand