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
}
Labels:

Comments

Post a Comment

Popular posts from this blog

Script to move SQL Server System Database Files

One issue that can occur when using VM snapshots to back up a server with large database volumes is that the server gets paused when the snapshots are consolidated. We like to refer to this a "stunning" because it sounds worse, and it can cause really bad things if you are using clusters and/or availability groups. If a cluster node gets stunned for more than a few seconds, the other node will think that it needs to fail over. Additionally, when the stun is over, the node that was stunned comes back to life right where it left off, and if it was the owner when it was stunned, all this has to be sorted out, and honestly, after working with Microsoft Cluster for twenty years, you just don't want to put it in this situation unless there's no way to avoid it. So, your backup strategy changes, and you set your large volumes as independent disks so that they are ignored by the VM snapshots. This lets you backup the OS and any other small volumes without a stun. Your
1 comment
Read more

Maine Event - Days Eight, Nine, and Ten

Sunday morning brought more sunshine, so we puttered around the house a bit and then made a beeline for Popham Beach. The day went without much excitement other than Leigh finding a sand dollar about the size of a fifty cent piece. Emily once again had a fantastic time and even worked on her swimsuit modelling skills. We also flew my parafoil kite, which requires a good bit of wind to fly, but is extremely stable once it's up. Since we knew the secret of the beach, there was no mystery to the phenomena of the growing beach, but it was still super-duper. We knew this time to take our shoes to the island so we could explore a bit, so we tramped around it for quite a while. That was pretty much it for Day Eight. Day Nine didn't see much action of note until we headed to Portland to pick up Mamaw and Papaw Hinkle. After we loaded them up we stopped at L.L. Bean for a bit. They don't allow you to leave the state until you've made a purchase there. We drove down to the restau
Post a Comment
Read more