Hi Mates,
First and foremost I would like to inform you that I am a beginner when it comes to programming. My intention is just to share the idea for the below scenario.
In our environment as part of windows maintenance weekend we need to fail-over Always ON servers. So we have multiple instances running on each node so say for example on the NODES A and B we have instance-1 acting as primary replica on NODE 1 and instance-2 acting as primary replica on NODE 2. Whenever we reboot the nodes we need to ensure that there are running on their dedicated NODES back.
if there are many ALWAYS ON servers it is hard to check manually during this fail-over and
fail-back hence the below script can help us in identifying the servers which we might forget to fail them back to dedicated node.
So I have not concentrated in developing this as function and not considered port number as parameter as in our case we have browser services running also the variables what I used will look absurd. As I said it is just to share an idea you can use this and modify as per your needs.
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")
$server_list=get-content 'D:\servers.txt'
$results10=@()
$header = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>System Status Report</title>
<style type="text/css">
<!--
body {
background-color: #E0E0E0;
font-family: sans-serif
}
table, th, td {
background-color: white;
border-collapse:collapse;
border: 1px solid black;
padding: 5px
}
-->
</style>
"@
$body = @"
<h1>Server Status</h1>
<p>The following report was run on $(get-date).</p>
"@
foreach ($machine_name in $server_list)
{
try
{
$server1 = new-object ("Microsoft.SqlServer.Management.Smo.Server") $machine_name
$Server1.ConnectionContext.connect()
$sql1="select
SERVERPROPERTY('servername') AS [ServerName],
lip.state_desc as state,
ip_address,
AGC.name as AG_Name,
isnull(l.dns_name, @@SERVERNAME )+':'+(CAST(port as varchar(8))) as Listener,
CASE
WHEN role=1 THEN replica_server_name
ELSE (select replica_server_name from sys.dm_hadr_availability_replica_cluster_states RCS
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id where role=1)
END as active_node,
CASE
WHEN role=2 THEN replica_server_name
ELSE (select top 1 replica_server_name from sys.dm_hadr_availability_replica_cluster_states RCS
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id where role=2)
END as passive_node,
ARS.role_desc,
CAST(Getdate() as date) as [Timestamp]
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
LEFT OUTER JOIN sys.availability_group_listeners l ON l.group_id = ARS.group_id
LEFT OUTER join sys.availability_group_listener_ip_addresses lip on lip.listener_id=l.listener_id
outer apply (select * from sys.dm_hadr_cluster) as hadrc
WHERE replica_server_name = SERVERPROPERTY('ServerName')"
$mem = $server1.ConnectionContext.ExecuteWithResults($sql1)
$table1=$mem.tables[0]
foreach($row1 in $table1)
{
$item = New-Object -Type PSCustomObject -Property @{
'SERVERNAME' = $server1.Name
'ALWAYSONSTATE'= $row1.item("state")
'IP_ADDRESS'=$row1.item("ip_address")
'AVAILABILITY_GROUP_NAME'=$row1.item("AG_Name")
'ALWAYSON_LISTENER'=$row1.item("listener")
'PRIMARY_REPLICA'=$row1.item("active_node")
'SECONDARY_REPLICA'=$row1.item("passive_node")
'ROLE DESCRIPTION'=$row1.item("role_desc")
'TIMESTAMP'=$row1.item("Timestamp")
}
$results10=$results10+ $item
}
}
catch
{
Write-Host "SQL Server Not Available:$machine_name kindly check the servername as it doesn't exists" -foregroundcolor red
}
finally
{
$server1.ConnectionContext.Disconnect()
}
}
$results10 | ConvertTo-Html -head $header -body $body | foreach {
$PSItem -replace "<td></td>", "<td style='background-color:red'></td>"
} | Out-File D:\ALWAYSON_CHECKS.html
invoke-expression D:\ALWAYSON_CHECKS.html
if you see in the above image there is one cell under primary replica which is highlighted in RED COLOR means it is the one where the replica is not running on its intended node also under the last column by name Role description it is showing as secondary.
In case if you need any help kindly let me know.
Comments