Skip to main content

SQL Diagnostics Manager - Script to Disable Maintenance Mode on All Servers

We use Idera's SQL Diagnostics Manager and there is a defect where monitored servers are sometimes not taken out of maintenance mode as specified by the schedule. As a workaround, I wrote the following script that runs each morning to log which servers are in maintenance mode, and disables maintenance mode on all servers. The use of the -MMRecurring switch on Set-SQLdmMonitoredInstance disables maintenance mode and keeps the maintenance mode schedule enabled.
add-pssnapin sqldmsnapin
$LogFile = "D:\Jobs\DisableMaintenanceModeAll\MMEnabledLog.txt"

# Log any servers that are currently in maintenance mode
$MMEnabled = invoke-sqlcmd -serverinstance  -Query "USE SQLdmRepository; SELECT InstanceName FROM MonitoredSQLServers WHERE MaintenanceModeEnabled = 1"
if( $MMEnabled.ItemArray.Count -gt 0 ) {
 add-content -Value " " -path $LogFile
 add-content -Value "================================================" -path $LogFile
 add-content -Value " " -path $LogFile
 get-date -F "G" | add-content -path $LogFile
 $MMEnabled | out-string | add-content -path $LogFile
}

#Create the SQLdm drive
new-sqldmdrive dm  SQLdmRepository
cd dm:\
cd .\Instances\

#Get the list of monitored servers
$Servers = get-item * | select InstanceName

#Iterate through the list of monitored servers and disable maintenance mode on them.  The -MMRecurring switch
#disables maintenance mode, but leaves the schedule in effect.
foreach($Server in $Servers){
 Set-SQLdmMonitoredInstance (Escape-SQLdmName -Name $($Server.InstanceName)) -WindowsAuthentication -MMRecurring
}

Comments