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)

}