Managing Alwayson Availability Groups

The following queries can be combined to aid management of Availability Groups

select * from sys.dm_hadr_cluster
select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster_networks
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.dm_hadr_availability_group_states
select * from sys.availability_replicas
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
select * from sys.dm_tcp_listener_states

SQL Queries in PowerShell

OK, So Backup guys needed to run a SQL Query to find out which Node of an Availability Group is hosting the Primaries.

So two things here, I needed a SQL Script to give me that information and then a way of running a SQL Script in Powershell and returning the result.

I found the following two scripts online.


$ServerName = "."
$DatabaseName = "master"
$Query = "SELECT HAGS.Primary_Replica FROM Sys.dm_hadr_availability_group_states HAGS INNER JOIN sys.availability_groups AG ON AG.group_id = HAGS.group_id WHERE AG.name = 'AAG_Name'"

#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30

#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

This script is wonderful, it can be used to run any SQL Statement in powershell and return the results in a Table.