Here is some Powershell code, that I set up in a SQL job, to run every 30 minutes. I have a list of Instances in an AdminServer. I use that, to determine which servers to check. You will have to change the AdminServer and AdminDB, or hardcode the list, in the script. I also use a proxy, for connection on remote servers.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Initialize Arrays
$FailedConnections = @()
#Retrieve Instance list from database.
$ServerList = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "AdminServer"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $ServerList.Databases.Item("AdminDB")
$ds = $db.ExecuteWithResults("SELECT [InstanceName] FROM [AdminDB].[dbo].[SQLServerInfo] WHERE AllowConnection = 1 AND CheckConnection = 1 ORDER BY InstanceName")
# Loop through all instances
FOREACH ($InstanceRow in $ds.Tables[0].Rows)
{
Try
{
$Instance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceRow.Item("InstanceName")
IF(!$Instance.Product)
{
$FailedConnections += "" | SELECT @{Name='InstanceName';Expression={$InstanceRow.Item("InstanceName")}}, @{Name='Message';Expression={"Connection failed to instance"}}
}
ELSE
{
Try
{
IF(!$Instance.JobServer.ServiceAccount)
{
$FailedConnections += "" | SELECT @{Name='InstanceName';Expression={$InstanceRow.Item("InstanceName")}}, @{Name='Message';Expression={"SQL Server Agent is not accessible"}}
}
}
Catch
{
$FailedConnections += "" | SELECT @{Name='InstanceName';Expression={$InstanceRow.Item("InstanceName")}}, @{Name='Message';Expression={"SQL Server Agent is not accessible"}}
}
}
}
Catch
{
$FailedConnections += "" | SELECT @{Name='InstanceName';Expression={$InstanceRow.Item("InstanceName")}}, @{Name='Message';Expression={"Connection failed to instance"}}
}
}
# Set html style
$a = "<style>"
$a = $a + "<!-- "
$a = $a + " TD{font-family: Arial; font-size: 8pt;} "
$a = $a + "--->"
$a = $a + "BODY{background-color:peachpuff;}"
$a = $a + "TABLE{font-family: Arial;font-size: 8pt;width:100%; height:75%; border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:PaleGoldenrod}"
$a = $a + "</style>"
IF ($FailedConnections)
{
# Send Email listing failed SQL Server jobs.
$smtpServer = "smtpserver"
$smtpFrom = "From@your.com"
$smtpTo = "To@your.com"
$messageSubject = "SQL Server Connection Failure Report"
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true
$message.Body = $FailedConnections | SELECT @{Name='Instance Name';Expression={$_.InstanceName}}, Message | ConvertTo-HTML -head "<H2>SQL Server Connection Failure Report</H2>" -body $a
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
}