In WFC a shared disk device has a dependency on a cluster resource like SQL Server. With the introduction of CSV (Cluster Shared Volumes) this dependency isn’t there any longer because we assume that a CSV is always there. That’s right (sometimes), but if a SAN failure happens, the CSV or several volumes presenting the CSV will be gone and SQL Server will start again. Ths brings SQL Server in an unpredictable situation.
So I would like to have a PowerShell-Script (running on every node on the cluster) monitoring the CSV and stopping the group hosting the SQL resources if CSV failures happen. CSVMonitor is a prototype … to show that this approach will generally work.
Need a help in creating PowerShell script for the above requirement.
You have lot's of options to address you use case. You have to look and decide what fits you personal skill set and long term use.
PowerShell has cmdlets for *-ClusterSharedVolume
You can use PowerShell DSC (Desired State Configuration) for this sort of effort, or write a single script to monitor for this service sate, and associate that with and event registration to take actions needed.
There several documents on MS site on what DSC is and how to use it as well as training videos on YouTube. The same goes for creating Windows Event monitors and using SQL monitoring scripts. You could also just use Group Policy Preferences to monitor service state and restart the service. You can use policy to set the service to always restart directly.
Just do a search for
or 'powershell monitor sql'
or 'powershellgallery ClusterSharedVolume'
or 'powershell dsc sql server'
or 'powershell wmi sql server'
or 'powershell wmi event sql'
or 'powershell monitor ClusterSharedVolume'
You will get a long list of hits on the various ways to achieve what you are after.
There are pre-built examples out there to get you started, as you will see from the search example above that you can use/tweak for ruse.
For example -
Cluster Shared Volumes Cleanup Script'gallery.technet.microsoft.com/Cluster-Shared-Volumes-e41ecc41'
PSTip Monitoring Cluster Shared Volume (CSV) availability'powershellmagazine.com/2014/02/26/pstip-monitoring-cluster-shared-volume-csv-availability'