Surveiller les noeuds d'un cluster SQL via un script powershell

Pour voir le script, cliquer sur lire la suite.

Max # FCI per node = CEILING (# total FCI / # physical nodes)

# sendmail function
function sendmail{
param($msgText)
Write-Host "Sending Email"

# ***Change to your SMTP server name
$smtpServer = "your.smtp.server.com"
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)

# ***Change "From", "To" and "Subject" fields to your own
$msg.From = "[e-mail]"
$msg.To.Add("[e-mail]")
$msg.subject = "Too many SQL instances on $env:COMPUTERNAME"
$msg.body = $msgText

# Sending email
$smtp.Send($msg)
}

# main logic
$nTotSQLInst = Get-WMIObject Win32_Service | where {($_.Name -LIKE "MSSQL$*") -or ($_.Name -eq "MSSQLSERVER") } | Select -ExpandProperty Name
$nWSFCNode = get-clusterNode | select-object Name
$nSQLInst = Get-WMIObject Win32_Service -Filter {Name LIKE '%MSSQL$%' and State LIKE 'Running'} | Select -ExpandProperty Name
$maxSQLInst = [math]::ceiling($nTotSQLInst.count/$nWSFCNode.count)
If ( $nSQLInst.count -gt $maxSQLInst ) {
$CR = "`r`n"
$InstList = $null
for ($i=0; $i -lt $nSQLInst.count; $i++) {$InstList = $InstList + $nSQLInst[$i] + $CR}
$msgText = "There are " + $nSQLInst.count + " SQL instances in node $env:COMPUTERNAME :" +
$CR + $CR + $InstList + $CR + "The max # of instances allowed is $maxSQLInst"
sendmail($msgText)
}

C:apps>powershell .SQLInstBalMonitor.ps1

source