Finding Primary Replicas for SQL Server 2012 AlwaysOn Availability Groups with PowerShell

Problem

We have numerous SQL 2012 Availability Groups in our environment with more projected to come online. The issue we are faced with is that we want to find which SQL Instance (cluster node) is hosting the Primary Replica. You can use SSMS to find the information either with the DashBoard or by expanding each Availability Group. This will show you the information but when you get over 2-3 Availability Groups then it can be problematic to have all the tabs in SSMS for each Dashboard or to have each instance open in SSMS. A better method is to turn to PowerShell to help us find a solution to the issue. We can use the system DMVs for Availability Groups to create a T-SQL query and then use PowerShell to iterate through a list of SQL Servers and query them in turn storing any results in a dataset object. We will use the .NET tool chest to connect to and run T-SQL against our servers returning results only for Servers in Availability Groups and filtering for Primary Replicas.

Solution

My solution uses PowerShell to execute a T-SQL script against a list of SQL instances and return the cluster node holding the Primary Replica for the Availability Group. It will run against SQL 2005 to SQL 2012 (not tested on SQL 2014, but no reason it would fail) however since we are focused on SQL 2012 and higher we should keep the list of instances to the versions supporting Availability Groups only. The list is embedded in the PowerShell but we could use a file or a SQL table to feed the SQL instances into the script to be iterated through. I will leave that to those who need it, the modification needed being changing the input method to place the instance list into a variable as the collection of objects to iterate through.

The parts we require are the T-SQL to find whether a cluster node’s instance is holding a Primary Replica and the PowerShell to create a collection of SQL instance objects, connect to the instance, run the T-SQL code and return the results into a dataset. Lastly we print the dataset to the screen showing the Primary Replica for each Availability Group. We could upload the results to a SQL table or write to a file on disk, we could even email it, whatever the best method for dispensing the information to those who need it.

T-SQL to Find if the SQL Instance is a Primary Replica

We will use several system DMVs to create a T-SQL query that will determine if the local SQL instance on the cluster node is the Primary Replica for an Availability Group.

IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
SELECT
   AGC.name -- Availability Group
 , RCS.replica_server_name -- SQL cluster node name
 , ARS.role_desc  -- Replica Role
 , AGL.dns_name  -- Listener Name
FROM
 sys.availability_groups_cluster AS AGC
  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
   ON
    RCS.group_id = AGC.group_id
  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
   ON
    ARS.replica_id = RCS.replica_id
  INNER JOIN sys.availability_group_listeners AS AGL
   ON
    AGL.group_id = ARS.group_id
WHERE
 ARS.role_desc = 'PRIMARY'
END

The results of the above query against a SQL instance which is the Primary Replica is shown below.

That is all that we need to run on an instance to see if there are any Primary Replicas for a Availability Group on that instance. Since any Availability Group should be composed of two or more SQL instances (degenerate case of one node cluster ignored) we have to check all the SQL instances in the WSFC that make up the cluster hosting the Availability Group. PowerShell to the Rescue!

PowerShell script to iterate against a collection of SQL instances and run T-SQL code

The PowerShell script runs on version 2 and higher and does not use any add-ins or Providers just the .NET functionality in PowerShell itself. The part that is new to most people will be the .NET OLDEB functionality that uses the OleDbDataAdapter to run a T-SQL command and take the results and populate a dataset.

## Setup dataset to hold results
$dataset = New-Object System.Data.DataSet
## populate variable with collection of SQL instances
$serverlist='DBS04A','DBS04B','DBS05A','DBS05B','DBS06A','DBS06B'
## Setup connection to SQL server inside loop and run T-SQL against instance 
foreach($Server in $serverlist) {
$connectionString = "Provider=sqloledb;Data Source=$Server;Initial Catalog=Master;Integrated Security=SSPI;"
## place the T-SQL in variable to be executed by OLEDB method
$sqlcommand="
IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
SELECT
   AGC.name
 , RCS.replica_server_name
 , ARS.role_desc
 , AGL.dns_name
FROM
 sys.availability_groups_cluster AS AGC
  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
   ON
    RCS.group_id = AGC.group_id
  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
   ON
    ARS.replica_id = RCS.replica_id
  INNER JOIN sys.availability_group_listeners AS AGL
   ON
    AGL.group_id = ARS.group_id
WHERE
 ARS.role_desc = 'PRIMARY'
END
"
## Connect to the data source and open it
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
$connection.Open()
## Execute T-SQL command in variable, fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
#$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
}
## Return all of the rows from dataset object
$dataSet.Tables | FT -AutoSize

We can examine the script parts by functionality. The first section is where we create the dataset to hold results from T-SQL queries and create the collection of SQL instances to iterate through.

## Setup dataset to hold results
$dataset = New-Object System.Data.DataSet
## populate variable with collection of SQL instances
$serverlist='DBS04A','DBS04B','DBS05A','DBS05B','DBS06A','DBS06B'

The next section is where we loop through the collection of SQL instances in the variable, first setting up the connection string then using a variable to hold the T-SQL command to be executed. Finally we connect to the SQL instance, execute the T-SQL and take any records returned and place them in the dataset object. After the loop is finished going through the collection of objects we dump the rows to the command console screen.

## Setup connection to SQL server inside loop and run T-SQL against instance 
foreach($Server in $serverlist) {
$connectionString = "Provider=sqloledb;Data Source=$Server;Initial Catalog=Master;Integrated Security=SSPI;"
## place the T-SQL in variable to be executed by OLEDB method
$sqlcommand="
IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
SELECT
   AGC.name
 , RCS.replica_server_name
 , ARS.role_desc
 , AGL.dns_name
FROM
 sys.availability_groups_cluster AS AGC
  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
   ON
    RCS.group_id = AGC.group_id
  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
   ON
    ARS.replica_id = RCS.replica_id
  INNER JOIN sys.availability_group_listeners AS AGL
   ON
    AGL.group_id = ARS.group_id
WHERE
 ARS.role_desc = 'PRIMARY'
END
"
## Connect to the data source and open it
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
$connection.Open()
## Execute T-SQL command in variable, fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
#$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
}
## Return all of the rows from dataset object
$dataSet.Tables | FT -AutoSize

At this point we are done and can read the list on screen. Alternatively, we could write to a file on disk, push the data into a SQL table or even email it.

Code to email a file on disk as an attachment

If you want an example of mailing it then here is a code snippet that will mail a file on disk as an attachment.

$from = New-Object System.Net.Mail.MailAddress "SQLAvailGroupPrimary@mydomain.com"  
$to =   New-Object System.Net.Mail.MailAddress "DBAHoncho@mydomain.com"  
  
# Create Message  
$message = new-object  System.Net.Mail.MailMessage $from, $to  
$message.Subject = "Availability Group Primary List"  
$message.Body = "Availability Group Primary List for our SQL Servers"
$Attachment = New-Object Net.Mail.Attachment('c:\temp\SQLAvailGroupPrimary.txt', 'text/plain')
$message.Attachments.Add($Attachment)
  
# Set SMTP Server and create SMTP Client  
$server = "smtp.mydomain.com"  
$client = new-object system.net.mail.smtpclient $server  
  
# Send the message  
"Sending an e-mail message to {0} by using SMTP host {1} port {2}." -f $to.ToString(), $client.Host, $client.Port  
try {  
   $client.Send($message)  
   "Message to: {0}, from: {1} has beens successfully sent" -f $from, $to  
}  
catch {  
  "Exception caught in CreateTestMessage: {0}" -f $Error.ToString()  
}

Examples of the results from Dashboard and PowerShell script

The images below show what we can find from the Dashboard or SSMS and what is returned from the PowerShell script. Choose Dashboard on the Availability Group node rather than one level down we will get the information we want but for each cluster we would need a tab for the Availability Groups it holds.

If we go to a single Availability Group node.

If we go directly into the Replicas node of the Availability Group tree we can also see the Primary and other Replicas but have to do this for every Availability Group.

Finally, we have the results of the PowerShell script. It displays all the Primary Replica information for as many Availability Groups as you have setup.

Next Steps

We can get more data from the T-SQL, we just need to decide what we want and how to get it.  If there is another DMV that has other information than the ones already used we can use them. We could also use a text list or SQL table to hold instances to be used in the collection to be looped through. We can design any method to feed the objects in the collection as long as it pulls in the SQL 2012 instances in the Availability Group. Finally, we can remove the WHERE clause and return all Replicas in the T-SQL code.

If you notice in the PowerShell code the variable holding the T-SQL code is inside the loop but is does not have to be inside the loop. We can place it outside the loop but leaving it inside would allow us to use variables to modify the T-SQL string.

• Learn more about how to Monitor SQL Server AlwaysOn Availability Groups here: Monitor SQL Server AlwaysOn Availability Groups
• To read more about Configuring Alerts for SQL Server AlwaysOn Availability Groups: Configuring Alerts for SQL Server AlwaysOn Availability Groups

Brian P ODwyer

Brian is a Senior BI Analyst at Cook Children’s Health Care System where he has been for the last 12 years working in both Infrastructure and Enterprise Application support before moving into a BI role. Brian has worked with SQL server since version 2000 over the years and has much experience integrating SQL Server with Third Party and Microsoft Applications. Brian has worked with SQL in depth since version 2005 and holds numerous SQL Server Certifications MCTS, MCITP, MCDBA and MCSE and certifications in Exchange, SharePoint and Windows Server. Brian started out as an Actuarial Analyst and moved into Computers in the DOS days. Brian specializes in integrating the various parts of the Enterprise into solutions to solve problems that have no simple answer or easy framework.