Forum Discussion

LauraC100's avatar
LauraC100
Copper Contributor
Sep 20, 2022

Parameterizing in a PowerShell Script

I am new to PowerShell and I am at a loss.  Working with Idera I have put together this script   #To place the Instance in Maintenance Mode   #To enable the SQLDM provider, type the following wit...
sql server
Windows PowerShell
LauraC1002085's avatar
LauraC1002085
Copper Contributor
Sep 22, 2022
This is the code that I put together. I hope this is ok. A lot of information. Many ways to do it.

$RepositoryDatabaseInstance=$args[0]
$MaintenanceModeInstance=$args[1]


#--------------------------------------------------------------------------------------------------------------------

#To place the Instance in Maintenance Mode


#To enable the SQLDM provider, type the following within PowerShell
add-pssnapin sqldmsnapin

#To verify the snapin is registered
get-psprovider

#To register the SQLdm drive, type the following within PowerShell. USCTAPD660022\DEV01 is the instance where the Repository Database sits
#Need to know what Repository instance the instance is registered on
#Currently in Production there are 5: usctapp660208\prod01, usctapp660209\prod01, usctapp660210\prod01, usctapp660211\prod01, usctapp660271\prod01
New-SQLdmDrive dm $RepositoryDatabaseInstance SQLdmRepository


#Change to the dm drive
cd dm:\

#Sets maintenance mode to Until further notice. Schedule collection will not occur. USCTAPD650328\DEV01 is the instance to be placed on maintenance mode.
Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name $MaintenanceModeInstance) -MMAlways

#--------------------------------------------------------------------------------------------------------------------

LainRobertson's avatar
LainRobertson
Silver Contributor
Sep 26, 2022

LauraC1002085 

 

Hi, Laura.

 

Since this is not a Microsoft module, I've made some assumptions, but here's a safer version of what you've done. I've omitted the wealth of inline comments for brevity.

 

This version uses named parameters so the script doesn't break if the parameters are passed in in a different order.

 

I've omitted the Get-PSProvider commandlet as this carries no value inside the script. It's purely a visual confirmational step shown in a couple of web articles but it isn't actually doing anything.

 

According to Idera's documentation (which is rather crude, I must say), the Set-SQLdmMonitoredInstance can accept a string array for the Path parameter, which I've reflected in the $Instances variable definition ([string[]] is an array of string values; [string] is just a single string value.)

 

 

Set-SqlMaintenanceMode.ps1

[cmdletbinding()]
param(
    [parameter(mandatory=$true)][string] $Repository
    , [parameter(mandatory=$true)][string[]] $Instances = @()
)

Add-PSSnapin sqldmsnapin -ErrorAction:Stop;
New-SQLdmDrive dm $Repository SQLdmRepository;
Set-Location -Path dm:\ -ErrorAction:Stop;
Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name $Instances) -MMAlways;

 

Again, I cannot test this as I do not use Idera but you would run this basic script something like the below examples:

 

Example 1: Set a single instance.
Set-SqlMaintenanceMode -Repository "myserver\myinstance1" -Instances "dbhost1\instance1";
 
Example 2: Set multiple instances.
Set-SqlMaintenanceMode -Repository "myserver\myinstance1" -Instances "dbhost1\instance1", "dbhost1\instance2", "dbhost5\instance1", "dbhost5\instance2";

 

Cheers,

Lain

Resources