Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

Operations Manager Shell

I've started using SCOM 2007 R2, which has given me a chance to try out the Operations Manager Shell. The Ops Mgr Shell is a customized PowerShell initialized through a console file and startup script. The important thing to note is that it's still regular PowerShell.
 
What's interesting about the Ops Mgr shell is the use of  both a provider and cmdlets. You can navigate groups and objects like a file system drive plus there are some nicely thought out cmdlets that can act within the current context of objects returned by the provider through pipeline input.  As someone coming from SQL Server PowerShell I couldn't help but think this is how the SQL Server Powershell host should work i.e. cmdlets integrated with provider. Although many of the Ops Mgr cmdlets allow you to specify paths and objects as parameters for their cmdlets, I found it much easier to use the provider in conjunction with cmdlets--navigating to a group or object and then executing cmdlets to get or change properties.
 
I'm more of a SCOM user than a SCOM administrator, so my use cases are simple and have nothing to do with administering SCOM. The only things I need to do is get a list of alerts for a particular group or alert and put groups or machines in maintenance mode to avoid alerts during planned maintenance. Here are some scripts I've used:
 
#Navigate to the SQL Server computer group
PS Monitoring:\sunfish1\Microsoft.SQLServer.ComputerGroup           
#Get all unresolved alerts for the current group           
get-alert -criteria "ResolutionState = 0" | Select Name, Description, MonitoringObjectPath, MonitoringObjectName, TimeRaised            
           
#Get alerts for where the alert name is logical disk frag and export results to CSV file
get-alert -criteria "Name like 'Logical Disk Frag%'" | Select Name, Description, MonitoringObjectPath, MonitoringObjectName | export-csv c:\users\u00\bin\DiskFrag.csv -noTypeInfo            
           
#Put an entire group in maintenance mode for one hour
get-monitoringobject | New-MaintenanceWindow -startTime:$(get-date) -endTime:$((get-date).AddHours(1)) -comment:"Testing PowerShell script"           
#Put the Z002 computer in maintenance mode for one hour           
get-monitoringobject | where {$_.Name -eq 'Z002.acme.com'} | New-MaintenanceWindow -startTime:$(get-date) -endTime:$((get-date).AddHours(1)) -comment:"Testing PowerShell script"            
           
#Note as a DBA two other groups you should look at:
Microsoft.SQLServer.InstanceGroup            
Microsoft.Windows.Clusters
 

A few tips

  • I find myself using the alert script just to export what I'm seeing in my Operations Console (GUI) to a CSV file. I'll then send the simple PowerShell generated CSV reports to my team to address. If you know of an easier way to do this through the GUI, let me know.
  • The maintenance mode scripts are not as useful, it's easier to use the GUI, right-click, and select maintenance mode. Even multiple computers can be selected. I suppose if I had a regularly scheduled maintenance, I would find more uses for a scripted maintenance window solution.
  • It is surprising what SCOM finds, the most common warning condition I've seen include databases with autoclose or autoshrink on. In an active environment with thousands of databases a few inherited databases from SQL Express installation are bound to exist with either autoclose or autoshrink on. Once identified these warning are easy to fix.
  • One other interesting warning is logical disk defragmentation. I had do a little research about this warning and posted a question to ServerFault. I won't repost here; see the ServerFault Q&A for detailed explanation. As part of the question I created a short script to retrieve logical fragmentation through WMI and PowerShell:

 $vols = Get-WmiObject -computername "Z002" Win32_Volume -filter "DriveType=3"           
$defragInfo = $vols | %{$_.DefragAnalysis() | add-member -membertype noteproperty vname $_.name -passThru}           
$defragInfo | %{$_.DefragAnalysis | add-member -membertype noteproperty DefragRecommended $_.DefragRecommended -passThru | add-member -membertype noteproperty vname $_.vname -passThru} | out-file ./tmp.txt

The script can be executed remotely, retrieves the logical disk fragmentation for computer, Z002 and sends the output to a text file. The fragmentation numbers produced by the script seem to match what Ops Mgr produces. This illustrates one of the cool things about PowerShell, with a little work you can use scripting to better understand how management tools like SCOM work!

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.