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.
Start, Run, "Powershell"
Load the SQLdm snapin.
Add-PSSnapin SQLdmSnapin
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).
 |
| 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;
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.
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(
GO
CREATE SCHEMA DBA AUTHORIZATION dbo;
GO
CREATE TABLE DBA.MaintenanceMode(
InstanceName nvarchar(256) null,
MMEnabledOn datetime null,
LoginName nvarchar(128) null);
GOMMEnabledOn 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.
SELECT InstanceName FROM MonitoredSQLServers
WHERE Active = 1 AND MaintenanceModeEnabled = 0
ORDER BY InstanceName;
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;
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.
