I've spent a good deal of time researching how to monitor if a SQL Instance is on it's preferred owner or not. Identifying a cluster failover is easy enough using the default application templates provided by Solarwinds but my company wants to take this an extra step and receive alerts when a SQL Instance is no longer on its preferred owner. I have found a way to accomplish this but believe there may be a more simple way to accomplish this.
My Method
Node Monitoring
- Monitor the VIP of a SQL instance
- Create a new custom property, we'll call this SQL_Preferred_Owner
- For the monitored VIP node, edit the custom property SQL_Preferred_Owner (Text w/ dropdown) to reflect the Preferred Owner
Alert Trigger Condition
- Create a Double Value Comparison
- Condition: Node SQL_Preferred_Owner is not equal to Node System Name (Node)
This will check the monitored VIP's System Name (located under Node Details) and compare it to the custom property SQL_Preferred_Owner. If they do not match, it will send an alert to the recipients of the alert. My only problem with this is that the System Name only updates after a Rediscovery scan, which by default is 30 minutes and is a global property for all NetObjects, it is not something we can edit per-node. 30 minutes is too long, I can drop it down to 5 minutes but I'm concerned about the stress it may inflict on a SQL DB as the amount of NetObjects will continue to increase as we add more systems.
NOTE: I have attempted to use Powershell scripts and have tested some default ones Solarwinds have provide before trying to implement my own. The tests always came back with these results and yes I have checked to make sure I am on Powershell v2 and have WinRM enabled and have confirmed that these commands run just fine when on the local server running the script. I know this is a time to open a ticket with Solarwinds but am hoping to find a way to accomplish this task w/o using scripts.
Output: ==============================================
Message: The term 'Get-ClusterGroup' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
Errors: ==============================================
import-module : The specified module 'failoverclusters' was not loaded because no valid module file was found in any module directory.
At line:2 char:3
+ import-module failoverclusters;
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ResourceUnavailable: (failoverclusters:String) [Import-Module], FileNotFoundException
+ FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand
I know my solution works, I have tested it, but I'm curious if anyone else has come across a better solution that doesn't require adjusting a global default setting or spending more time troubleshooting the Powershell issues.
