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