PowerShell Script to Check Remote Server Ping and Trigger SQL AOAAG Failover if remote server is down

Hi,

our requirement is to have a powershell script that checks ping status of a remote server and trigger or execute MSSQL AOAag Failover using powershell script.

to begin with I have this below script that pings the remote server, so if reply fails then we want to execute db failover. how to do? the script will run on MSSQL AOAAG itself

=============

Test-Connection-ComputerNamens1-xms1.dm.com

if fails

then execute db failover.

=================================

August 31st, 2015 8:24pm

There are different ways to achieve your goal.

use the below script to find the active node of the cluster.

# Set cluster name 
$cluster_name = "SQL Virtal instance name"; 
# Load SMO extension 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null; 
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $cluster_name; 
# Get server properties 
$properties = $srv.Properties 
$owner_node = $properties.Item("ComputerNamePhysicalNetBIOS").Value; 
$is_clustered = $properties.Item("IsClustered").Value 
if($is_clustered) 
{ 
    Write-Host "The current active node of $cluster_name is $owner_node."; 
} 
else 
{ 
    Write-Host "$cluster_name is not a clustered instance of SQL Server."; 
}

use this command to fail over.

The following example manually fails over the MyAg availability group to the secondary replica with the specified path.

Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\SecondaryServer\InstanceName\AvailabilityGroups\MyAg

 
Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 11:51pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics