19 June 2012

The PoSh DBA – The Attributes of Advanced Functions

Once you pass that point of just hurriedly writing PowerShell scripts for immediate use and start to write PowerShell functions for reuse, then you'll want a robust set of parameters that allow functions to work just like cmdlets.

PowerShell advanced functions provide some powerful magic. In the previous article we covered some “features” that can help us to write better code, and now we are going to focus in on the magic of those features that are to do with the parameters that we pass to advanced functions.

We already know that, when we put the [CmdletBinding()] attribute in a function, it changes it into an advanced function, thereby allowing options that you don’t have in normal functions – see The PoSh DBA: Grown-Up PowerShell Functions. What I didn’t emphasize then is that all magic comes at a price. In this case, the price is that you must write code that uses the magic correctly; you must be more precise and cautious, just like any magician. To start with, the function’s parameters should be formally defined. We lose something by this: When we write a function without the [CmdletBinding()] attribute , we can be less strict, and pass more parameters than we have defined in the function, because the $args variable stores them.

If we now call our informal function :

But if, without due thought, we add just the [CmdletBinding()] attribute to this without any option…

…And try to run it using more parameters than we’ve declared…

Yes. It is an error in the positional parameters. You’ve just dropped the rabbit from the hat.

The CmdletBinding arguments

There are 3 arguments that are concerned with the parameters that can be passed to PowerShell functions. These are :

  • SupportsShouldProcess
    • By setting this property to $True (the default is $False), you are asking the function to be able to support the ShouldProcess method. By doing this, you allow the function to implement the Whatif common parameter that displays what the function would do, but without actually performing the operation . The Confirm option is also enabled by this property.
  • ConfirmImpact
    • In this argument, you will configure the impact level (“HIGH”, “MEDIUM” or “LOW”) at which the action that you are performing in your function should be preceded by a prompted confirmation request such as “Are you sure about this?”. This prompt would only be displayed if the impact level in the argument is equal to, or higher than, the value of the global $ConfirmPreference  shell variable (which defaults to ‘HIGH). The default value of the argument is ‘Medium’ and, of course, this option only make sense if you specify SupportsShouldProcess option as well.
  • DefaultParameterSetName
    • The DefaultParameterSetName argument specifies the name of the parameter set that Windows PowerShell will attempt to use when it cannot determine which parameter set to use. (for more information, use about_Functions_CmdletBindingAttribute)

So how should we code these?

Both SupportsShouldProcess and ConfirmImpact are part of the advanced functions confirmation methods, or as I like to call them, the “License to Kill” methods.

The License to Kill Options – Performing Highly Dangerous Operations

SupportsShouldProcess Argument

First License to Kill parameter – “My Name is If… -WHATIF”

We know that there is no such thing as a small mistake in a DBA’s job. When it happens, it is always a mistake of HUGE proportions.

Fortunately we can use the -whatif common parameter to help reduce mistakes, if our functions support it. This does nothing more than to show you what it would do without actually running the command in the function you are executing. It is something like asking the function: “What am I about to do?” rather than asking “Holy Saints. Do I have a backup?” after the function does something unexpected.

To illustrate this, we’ll create two advanced functions, Get-MSSQLTable and GET-MSSQLProcedure. The former outputs the SQL Server table objects that you specify, and the latter outputs stored procedure objects.

There is not much that to say about them. They are simple functions.

Now, we will write a more dangerous function that drops the objects. Now, you’ll probably have noticed that this is not a pair of related functions; one to drop Tables and other to drop Procedures. This is a function to drop a database object no matter what it is, just as long as the object has the drop method, or will accept being dropped.

The first name that I thought of was Drop-SQLObject, but Drop it is not an approved PowerShell verb, so I replaced the ‘Drop’ verb with something similar and approved. Remove-SQLObject. (You can get a list of approved verbs by using the Get-Verb cmdlet).

My first step towards using this function was, of course, to enable the -whatifand -confirm parameters by adding SupportsShouldProcess=$true in the CmdletBinding attribute:

And I created it so that it receives an SMO object as a parameter and drops the object inside a process block, but I encountered a problem with Foreach enumerators in the Pipeline, and to retrieve the Database Objects (tables, stored procedures and so on). In SMO you need to use enumerators, as I’ve shown in the “foreach$procin $_.StoredProcedures” and “foreach$table in $_tables” in the functions above. This will cause problems if you drop an object in the enumeration. This script won’t work.

Basically, Enumerators can be used to read the data in the collection, but they cannot be used to modify the underlying collection. If you try to run the script, you get an error:

To work around to this limitation, I used an Array $MyObject and populated it completely in the process block: In the End block, I effectively dropped the items of this object one by one. The final code (before I implemented -whatif processing) is:

The code that will eventually allow the -whatif argument to work properly is in the line 14 :

So if the’ -whatif' parameter is supplied, then the parameter passed to theShouldProcess method will be displayed, but the code in the scriptblock that follows the IF statement won’t be executed. Otherwise, the scriptblock after the IF statement will be executed. . In this case I am displaying the type of the object (table,stored procedure…etc.) and the Schema and Name of the object so you will be clear about what will be executed if you were to leave out the -Whatif.

In my case, because I had to call the drop method on the database object in the end block and not in the Process block, you might think, as I did, that I would need a variable to hold a flag as to whether I’d passed -whatif or not: Maybe Something like this?

Wrong. Just by passing the -whatif as a parameter, no operation or method will be performed in all the code of the function. I do not need to worry.

The function requires that an SMO object is passed to it. This required validation. I therefore had to add this rather daunting line that checks that the parameter is an SMO object and that it is droppable:

Don´t worry, I will cover the parameters’ metadata, and the ValidateScript attribute, in the next article. For now it is good to know that the $SmoObject parameter accepts only SMO Objects that can be dropped.

Now it is time to see the magic:

Do not be scared if all that you see displayed is ….

… because your tables still will be in the Database ThePoshDBA. No operation was performed; it is just saying to you what it would do.

And of course to effectively drop the tables, you need only remove the -whatif parameter.

Bear in mind that the problem I faced by using the Foreach enumerator when deleting the objects is a particular problem for this function, and it is unlikely that you would ever need to write your -whatif implementation the same way, that is, by using the begin and end blocks of the function. The usual way to implement -whatif processing is:

In fact, you can take advantage of using Enumerators. Let’s go a bit off-topic to look at a nice trick. Have you noticed that, for the cmdlet get-eventlog, you can either use an array as the parameter for the Servers or pass it in the pipeline? In this case when a parameter is defined as string[] you can do this …

… instead of this …

You can implement the same functionality in your Function. Let´s see the part of the code of Chad Miller´s Get-SqlWmi; (thanks to him for teaching me this trick). This function uses the WMI ManagedComputer cmdlet to get port, instance and service account WMI information for all SQL instances on a computer.

By using the foreach enumerator , the parameter $Computername as string[]and of course understanding the ValueFromPipelineByName and ValueFromPipelineByValue, you can simulate the same thing that Get-EventLog does.

You can call the Get-SQLWmi by passing in an array directly :

…or through the pipeline by value :

…or by through the pipeline name :

Don’t worry my friends, in the next part of these series, we will cover the ValueFromPipeline property and the related attributes.

But, back to our subject, according to the Don Jones, there is something else to consider as well:

“Don’t forget that -confirm and -whatif are also passthrough parameters. That is, if your function declares SupportsShouldProcess=$True, and someone runs the function with -whatif or -confirm, those will be passed to any other cmdlets WITHIN YOUR FUNCTION that also support -confirm and -whatif. Since your change isn’t being made by a cmdlet, you can’t take advantage of that, so the If construct and using $psCmdlet.ShouldProcess() is indeed the right way to go”.

Second License To Kill parameter- “Sir, you asked for a martini shaken not stirred. Do you -CONFIRM ?”

Sometimes even if we really are sure about an action, why not ask again? For this case we have the common parameter -confirmand as it is also enabled by SupportsShouldProcess we can use it as well.

And a prompt will be displayed to allow you to confirm your murder. No problems, we have license to kill.

ConfirmImpact Argument

To finish the ‘License To Kill’ options, there are some actions that are so dangerous that I want to prompt the user with the same -confirm question, but I want to do it every time that the function is called. I don’t want the user to be obliged to explicitly pass the -confirm parameter.

In this case, we can use the ConfirmImpact option just adding in the [CmdletBinding()] code :

As we see in the beginning of this article, The default is ‘ Medium’ but there are a choice of four impact levels ,’None’, ‘Low’, ‘Medium’ or ‘High’. We’ll code our function to specify ‘High’:

This operation can be so potentially catastrophic that I am declaring that the impact is HIGH. Every time that I call the function I’ll be asked to confirm the operation.

‘Ha Laerte’, I hear you say, ‘this function is generic. I need this confirmation all the time, but not for all objects. I need confirmation for my tables, but not for the stored procedures. Is there some way for me to suppress this confirmation?’

‘Sure!’, I reply, ‘just pass the parameter -confirm:$false‘.

Technically we can create a new parameter -force to suppress this prompt, but again, in the words of the Don Jones :

-Force is usually used to override a permissions problem, read-only, or something else; using it to suppress auto-confirm is a bit nonstandard, but it is comprehensible.

Colleagues, we are about the business of learning how to write advanced code in PowerShell. It is time to be more than just “comprehensible”; right ?

DefaultParameterSetName Argument

There are cmdlets that cannot be called with two or more parameters from different Parameter Sets at the same time. Some parameters depend on others in the same set. I confess that this concept was a bit difficult to understand for me, but after a talk with the Jedi Council (Chad, Ravi, Shay, Jeffery), the apple fell on my head. I felt the gravity of the situation.

Yes. It is a privilege to me have the power to invoke the Jedi Cmdlets as Invoke-ChadMiller, Invoke-Ravikanth, Invoke-ShayLevy, Invoke-JefferyHicks, Invoke-Boe, and so on.

(Ed: Focus Laerte, let’s get back to an example )

The DefaultParameterSetName in the GroupSet1t means that if you have some positional parameters with default values, and the user doesn’t specify any parameter names, then the default set will be used.

The DefaultParameterSetName is bound to the ParameterSetName option that you can specify in the parameter set metadata. There is no sense in using the one without the other.

As you can see, the parameters P1 and P2 are from GroupSet1,and P3 and P4 are from GroupSet2. This means that  P1 or  P2 cannot be passed together with P3 or P4; but all of them are in the same Function.

I guess that you know the Get-Process cmdlet of course. I can get a process by ID OR by Name, not both. Without using multiple distinct parameter sets, you’d need something like Get-ProcessByID and Get-ProcessByName – Thanks to Shay Levy for this example.

In my case, if I Try …

…or …

But If I try …

Other great examples are in the SQLPSX - Invoke-SQLRestore. Take a look at the code : – The parts with “…” was truncated by me to fit on the page.

The parameters $dbname, $action, $stopat, $realocatefiles, $force, $norecovery, and $keepreplication are parts of the Restore ParameterSet that is the DefaultParameterSetName. The $FileListOnly is from FileList, $Sqlserver and Filepath are not part of any parameter set.

Why is this useful? Well, if I specify the FileListonly in a restore backup , there is no need to use $action, $stopat or the entire Restore set. I just want FileListOnly or, in a T-SQL example a Restore FileListOnly. The same applies to FileListParameterSet. But using either Parameters Set in this function also requires a SQL Server Connection and a File path. This is why these options do not belong to any Parameter Set.

One last example: Do you remember when I said that “if you have some positional parameters with default values, and the user doesn’t specify any parameter names, the default set will be used”. Let’s see how this works. In the first function, the DefaultParameterSetName is “First” and in the Second ParameterSetName is “Second” :

Now let’s run each function without any parameters:

Do you see how this works? I am not passing any parameter to them and the parameter set default was that specified at DefaultParameterSetName

That is it my friends. In this article we covered the famous [CmdletBinding()] attribute and demonstrated how some of its magic is done. In the next article in this series, we will look at the wonderful ways that parameters can be validated with advanced functions.

References and Acknowlegements

I would like to thank some gentlemen who spared no effort to share their knowledge, either to me or to the community, and who took part, directly or indirectly, in the creation of this article. To my good friends Chad Miller, Shay Levy, Ravikanth Chaganti; and for his articles, books and blog, Sir Don Jones .

A special thanks to Sir Jeffery Hicks, Sir Phil Factor and Sir Bob Beauchemin that kindly gave your time to tech review and proofread the article.

A huge thanks to my Tech Editors Chis Massey and Andrew Clarke. The real magic is what these guys do for me

Books:

  • Bruce Payette´s Windows PowerShell in Action, Second Edition

Blogs:

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 14417 times – thanks for reading.

  • Rate
    [Total: 43    Average: 4.9/5]
  • Share

Related articles

Also in Database Administration

The SQL Server 2016 Query Store: Forcing Execution Plans using the Query Store

The SQL Server 2016 Query Store can give you valuable performance insights by providing several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. However, it can also directly affect the performance of queries by forcing Execution Plans for specific queries.… Read more

Also in Powershell

PowerShell Desired State Configuration: LCM and Push Management Model

PowerShell's Desired State Configuration (DSC) framework depends on the Local Configuration Manager (LCM) which has a central role in a DSC architecture. It runs on all nodes that have PowerShell 4.0 or above installed in order to control the execution of DSC configurations on target nodes. Nicolas Prigent illustrates the role of the LCM in the 'Push' mode of configuring nodes.… Read more

Also in SQL

Relational Algebra and its implications for NoSQL databases

With the rise of NoSQL databases that are exploiting aspects of SQL for querying, and are embracing full transactionality, is there a danger of the data-document model's hierarchical nature causing a fundamental conflict with relational theory? We asked our relational expert, Hugh Bin-Haad to expound a difficult area for database theorists.… Read more

Also in The Posh DBA

The PoSh DBA: Assigning Data to Variables Via PowerShell Common Parameters

Sometimes, it is the small improvements in a language that can make a real difference. PowerShell is able to introduce extra common parameters that can be used by any Cmdlet or advanced function. When -PipelineVariable was introduced in V4, we all wondered what it could be used for. Laerte experimented and discovered that it could be a real convenience in every-day scripting with the pipeline.… Read more
  • Shogan

    Great
    Hi Laerte,

    Cracking stuff! Great read and must for those interested in PoSh. (Anyone who isn’t, should be!)

    Cheers,
    Sean

  • laerte

    Thanks
    Hey Man, glad you liked it 🙂 Thanks my friend

  • cletocs

    How to check CPU/Mem/swap/Volume Free space on AIX / Linux using powershell?
    I am new to powershell scripting, am able to find scripts to find cpu/mem/diskspace for window.

    But could not find much on Linux/Aix using powershell.

    How to check CPU/Mem/swap/Volume Free space on AIX / Linux using powershell?

    Thanking you all for all the resources you have provided to us, which is very helpful.

  • Laerte Junior

    Re- How to check CPU/Mem/swap/Volume Free space on AIX / Linux using powershell?
    Cletocs, In fact I am a SQL Server DBA, then the world outside Windows it is not my knowledge. I believe you have answers posting on forums such as Stackoverflow. I’m sorry my friend.

  • Shogan

    Great
    Hi Laerte,

    Cracking stuff! Great read and must for those interested in PoSh. (Anyone who isn’t, should be!)

    Cheers,
    Sean

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up

See what's happening behind the scenes

Take a peek at the bowels of the ship – the lower decks – the actual servers of SQL Server Central itself.

See what's happening