Parameterizing in a PowerShell Script

[LauraC100]

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 within PowerShell

add-pssnapin sqldmsnapin

#To verify the snapin is registered

get-psprovider

#To register the SQLdm drive, type the following within PowerShell. AAAAAAA000000\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:  BBBBBBB000000\prod01, CCCCCCC000000\prod01, DDDDDDD000000\prod01, EEEEEEE000000\prod01, FFFFFFF000000\prod01

New-SQLdmDrive dm AAAAAAA000000\DEV01 SQLdmRepository

#Change to the dm drive

cd dm:\

#Sets maintenance mode to Until further notice. Schedule collection will not occur.  ZZZZZZZ999999\DEV01 is the instance to be placed on maintenance mode.

Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ZZZZZZZ999999\DEV01) -MMAlways

I need to parameterize where the values AAAAAAA000000\DEV01 and ZZZZZZZ999999\DEV01 are.  Can someone provide me with a web page that shows an example.  They want me to save this as a ps1 file and then call it passing in the two parameters.

[#2]

@LauraC100 

Hi, Laura.

Here's some relevant articles on parameters:

• about Parameters Default Values - PowerShell | Microsoft Learn
• about Parameters Default Values - PowerShell | Microsoft Learn
• about Functions Advanced Parameters - PowerShell | Microsoft Learn
• about Parameter Sets - PowerShell | Microsoft Learn
• about CommonParameters - PowerShell | Microsoft Learn

And here's a basic stub showing how to declare parameters.

[cmdletbinding()]
param(
    [parameter()][string]$MyParam1
    , [parameter()][int]$MyParam2
    , [parameter()][bool]$MyParam3
    , [parameter()][switch]$MyParam4
)

# Do stuff with said parameters.

Cheers,

Lain

[#3]

Hi

Maybe this powershell module can make your life easier.
Check this out.

https://dbatools.io/download/
https://dbatools.io/commands/

[#4]

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

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

[#5]

@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.)

• PowerShell Cmdlets for SQL Diagnostic Manager - SQL Diagnostic Manager 11.1 - Product Documentation ...

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