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.
New-SQLdmDrive dm TRON4\TEST1 SQLdmRepository
Change to the dm drive (Figure 1).
Now use the Set-SQLdmMonitoredInstance cmdlet to set a monitored server into maintenance mode until further notice (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.
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.
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.
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.
CREATE SCHEMA DBA AUTHORIZATION dbo;
CREATE TABLE DBA.MaintenanceMode(
MMEnabledOn datetime null,
LoginName nvarchar(128) null);
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.
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).
ORDER BY InstanceName;
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.