Friday, June 24, 2016

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
}
}

No comments:

Post a Comment

How to add a Database to AlwaysOn Availability Group with four different options

To add a database to an existing AlwaysOn availability group, MS has given us four options to choose from Automatic seeding Full database an...