Idera SQL Diagnostic Manager (or as I call it "SQLdm") is a great monitoring and performance tuning tool that I was introduced to a few years ago.  There are no server-side agents, so it's a very lightweight tool and good for monitoring all SQL Servers in your environment.  The  GUI is great for managing servers except when I need to put all monitored servers in maintenance mode.  If I'm only monitoring a few servers, it's not that big of a deal.  However, if I'm monitoring a hundred servers, then using the GUI to place them all in maintenance mode could take 20 or 30 minutes.  Luckily for us, Idera has included a Powershell plugin for managing SQLdm.  If you're not familiar with Powershell, then hop on over to Microsoft site to start learning before you get left behind.  http://technet.microsoft.com/en-US/scriptcenter/dd742419.aspx.  For the rest of this post, I'll assume you have some basic knowledge of how Powershell works.

You can run the SQLdm Powershell commands from any workstation that has the SQLdm console installed.  For this example, I am running it directly from the repository server.

Start up a Powershell session in the Command Prompt window.

Load the SQLdm snapin.

Using the New-SQLdmDrive cmdlet, create a SQLdm drive, specifiing drive name,  the SQL Server that hosts the repository and the repository database name.

Change to the dm drive (Figure 1).

Figure 1

Now use the Set-SQLdmMonitoredInstance cmdlet to set a monitored server into maintenance mode until further notice (Figure 2).

Set-SQLdmMonitoredInstance (Escape-SQLDMName -name TRON4\TEST2) -MMAlways

Figure 2

Simple enough right?  Yes, but it's probably just as slow to key in that same command for each server in your inventory as clicking in the GUI.  Now comes the real "power" in Powershell.  You can programmatically loop through all servers in your inventory to enable maintenance mode.

Start by getting a list of monitored servers from the SQLdm repository.  Run the following TSQL code against the SQLdmRepository database.

SELECT InstanceName FROM MonitoredSQLServers
WHERE Active = 1
ORDER BY InstanceName;

In order to combine that server list with the SQLdm cmdlets, we need to add the SQL Server Powershell snapin.

Add-PSSnapin SqlServerCmdletSnapin100

Then you can use a foreach loop and the Invoke-Sqlcmd cmdlet to create a simple script to enable maintenance mode by looping through the results of that TSQL query.

foreach ($db in Invoke-Sqlcmd -query "SELECT InstanceName FROM MonitoredSQLServers WHERE Active = 1 AND MaintenanceModeEnabled = 0 ORDER BY InstanceName;" -database SQLdmRepository -serverinstance TRON4\TEST1 ) { #Enable MM for the selected server Set-SQLdmMonitoredInstance (Escape-SQLDMName -name $db.InstanceName) -MMAlways }

You can use the same script to disable maintenance mode on all servers just by changing "-MMAlways " to "-MMNever ".

In this example, all of the monitored servers are named instances, so that's why we must use the Escape-SQLDMName cmdlet.  It's allows you to specify server names that contain special characters such as: \*/:<>|?[].  If all of your monitored servers are using the default instance then you won't need to use this cmdlet.

Now let's build on this example by adding some more logic and features to our script.  Let's say you are monitoring a total of 5 servers, and 1 of them (TRON4\TEST1) is always in maintenance mode for some odd reason.  When you run this Powershell script to enable maintenance mode, then all 5 servers will be placed in maintenance mode.  Consequently, when you run the script to disable maintenance mode, it will disable it for all 5 including the 1 server that you didn't want it disabled.

In order to prevent the TEST1 instance from being disabled, we need to create a table to store the data when we execute the script.

You can create this table directly in the SQLdmRepository database, but it can be placed in any database.  When I need to customize a vendor database, I always create my own schema.  This helps to prevent any issues when you upgrade to the next version from the vendor.

USE SQLdmRepository;
GO
CREATE SCHEMA DBA AUTHORIZATION dbo;
GO
CREATE TABLE DBA.MaintenanceMode(

InstanceName nvarchar(256) null,
MMEnabledOn datetime null,
LoginName nvarchar(128) null);

GO

When we execute the Powershell script, it first checks the DBA.MaintenanceMode table for any existing rows.   If the table is empty, then the script will enable maintenance mode for all servers not currently in maintenance mode.

SELECT InstanceName FROM MonitoredSQLServers 
WHERE Active = 1 AND MaintenanceModeEnabled = 0 
ORDER BY InstanceName;

When it's done processing each one, it writes the server name to the DBA.MaintenanceMode table.
The next time the script is executed, it sees the DBA.MaintenanceMode table is not empty and selects the servers to process from that table (Figure 3).

SELECT * FROM DBA.MaintenanceMode 
ORDER BY InstanceName;

Figure 3

The script processes each server and then deletes that row from the DBA.MaintenanceMode table.

What you have is a pure toggle script that only enables maintenance mode for servers not currently in that state, keeps track of that list of servers, and then disables maintenance mode for only that list of servers.  In our example, TRON4\TEST1 server will always remain in maintenance mode.

Finally, create a SQL Agent job that executes this Powershell script, so the next time you need to enable maintenance mode for your entire environment you can just run the job.

The entire Powershell script is below.  Please feel free to modify it as you see fit for your environment.

########################################################################################### # # File Name: SQLdmToggleMM.ps1 # # Applies to: Idera SQL Diagnostic Manager v7 # ########################################################################################### #Clear screen CLEAR function LoadSnapins { # Load SqlServerCmdletSnapin100 Add-PSSnapin SqlServerCmdletSnapin100 # Load SQLdmSnapin Add-PSSnapin SQLdmSnapin } function ToggleMM { #Create a SQLdm drive New-SQLdmDrive dm TRON4\TEST1 SQLdmRepository | Out-Null #Change to the SQLdm drive cd dm:\ Write-Host #Get row count of DBA.MaintenanceMode. #This will determine if MM needs to be enabled or disabled for all servers. $Count = Invoke-Sqlcmd -query "SELECT COUNT(*) AS 'RowCount' FROM DBA.MaintenanceMode;" -database SQLdmRepository -serverinstance TRON4\TEST1 if($Count.RowCount -eq 0) { #Table DBA.MaintenanceMode is empty, so enable MM for all servers. Write-Host "DBA.MaintenanceMode table is empty so enabling MM for all servers" foreach ($db in Invoke-Sqlcmd -query "SELECT InstanceName FROM MonitoredSQLServers WHERE Active = 1 AND MaintenanceModeEnabled = 0 ORDER BY InstanceName;" -database SQLdmRepository -serverinstance TRON4\TEST1 ) { #Enable MM for the selected server Set-SQLdmMonitoredInstance (Escape-SQLDMName -name $db.InstanceName) -MMAlways | Out-Null #Save MM information in DBA.MaintenanceMode $Server=$db.InstanceName Invoke-Sqlcmd -query "INSERT DBA.MaintenanceMode SELECT '$Server',GETDATE(),SUSER_NAME();" -database SQLdmRepository -serverinstance TRON4\TEST1 Write-Host "Maintenance Mode Enabled for:" $db.InstanceName } } else { #Table DBA.MaintenanceMode is not empty, so disable MM for all servers listed in this table. Write-Host "DBA.MaintenanceMode table is not empty so disabling MM for all servers" foreach ($db in Invoke-Sqlcmd -query "SELECT InstanceName FROM DBA.MaintenanceMode ORDER BY InstanceName;" -database SQLdmRepository -serverinstance TRON4\TEST1 ) { #Enable MM for the selected server Set-SQLdmMonitoredInstance (Escape-SQLDMName -name $db.InstanceName) -MMNever | Out-Null #Remove server from DBA.MaintenanceMode table $Server=$db.InstanceName Invoke-Sqlcmd -query "DELETE FROM DBA.MaintenanceMode WHERE InstanceName = '$Server';" -database SQLdmRepository -serverinstance TRON4\TEST1 Write-Host "Maintenance Mode Disabled for:" $db.InstanceName } } } function Main { Write-Host Write-Host "=============================================================" Write-Host " 1. Loading Snapins" Write-Host "=============================================================" LoadSnapins Write-Host Write-Host "=============================================================" Write-Host " 2. Looping Through All Servers to Toggle MM" Write-Host "=============================================================" ToggleMM Write-Host Write-Host "=============================================================" Write-Host " Toggle MM Completed Successfully" Write-Host "=============================================================" } #Clear any previous errors $Error.Clear() #Go to main section Main