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
LainRobertson's avatar
LainRobertson
Silver Contributor
Sep 20, 2022

LauraC100 

 

Hi, Laura.

 

Here's some relevant articles on parameters:

 

 

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

  • 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