PowerShell - Find AlwaysOn Primary node Report

I was working on a powershell script which tells me which node is AlwaysOn primary replica. We have 38 servers which participate in AlwaysOn availability group technology. This script will help you to determine which server is a primary replica without logging into each server and find out.

NOTE:

I set up a path which is for error log at E:\PowerShell\GetAlwaysOnPrimary\

DBATestServer is our DBA server and DBA_ServerDW is DBA inventory database

SQLServers table is in DBA_ServerDW database. The table is already populated with a list of servers that we manage in our organization.

First, I will get a list of server names from SQLServers table. Then each server will be passed in for loop. I use invoke-sqlcmd command to all sql statements. Inside for each loop , I will get each server info if it is primary replica. Finally, I will omit all null results from the query and insert only returned data to a table AlwaysOnPrimary in DBA_ServerDW database. Thank you for reading my blog. Enjoy scripting!

$($errorlog = 'E:\PowerShell\GetAlwaysOnPrimary\Error.txt'

Clear-Content $errorlog

#Get Server List from SQL server inventory table, exclude name instances

$servername = invoke-sqlcmd -ServerInstance DBATestServer -Database DBA_ServerDW `

-Query "select servername from SQLServers

where SQLservers.Purpose like '%AlwaysOn%'"

foreach($server in $servername){

Try{

#convert system.object data type to String

$ServerN=$server.ServerName

$AG = invoke-sqlcmd -ServerInstance $ServerN -Database master `

                -Query "IF SERVERPROPERTY ('IsHadrEnabled') = 1

BEGIN

SELECT

  RCS.replica_server_name as ServerName

 ,AGC.name as AvailablityGroupName

  , ARS.role_desc as Role

 , AGL.dns_name as ListenerName

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"

$SName = $AG.ServerName

$GName = $AG.AvailablityGroupName

$Role  = $AG.Role

$LN    = $AG.ListenerName

if ($SName)

        {

                invoke-sqlcmd -ServerInstance DBATestServer -Database DBA_ServerDW `

                -Query  "INSERT INTO AlwaysOnPrimary(ServerName,AvailabilityGroupName,Role,ListenerName)

                        VALUES ('$SName','$GName','$Role','$LN')"

          }

}

Catch

{    Clear-Content $errorlog

     "Fail to get information $ServerN +' ' + $RunTime :$_" |Out-File $errorlog -Append

}

}