DBA > Articles

The PoSh DBA - Towards the Re-usable PowerShell Script

By: Laerte Junior
To read more DBA articles, visit http://dba.fyicenter.com/article/

How would you improve a PowerShell script in order to make it more versatile? How can you ensure that it is adaptable to the many similar tasks for which you are likely to need a script? In a nutshell, you turn into functions those common component tasks that you’ve written as scripts, and from these simple functions develop advanced functions. In doing so, you make the leap from PowerShell Zero to PowerShell Hero. In this article, I’ll walk you through a practical example and demonstrate the steps along the way
Before we get too deep into the details, let’s start by providing a few general rules for developing in PowerShell.
The General Rules
There are the rules that I use in my day-to-day work in PowerShell. They’ve worked well for me over the years, but I’m not saying that they are carved in stone.
Learn the language
You need to treat PowerShell as a serious .NET language. Like C#, F# or VB, it is impossible to know what the language can do, such as its features or commands without understanding the language and its paradigms? You can cut and splice other people’s scripts but you must have a good feel for the way that the language works before you can proceed any further
Use The Help
For PowerShell, the help system is the first thing you must reach for: it must be your best friend. The designers of the language intended it to be well-used.
Use the PowerShell Community
The PowerShell community is unique, because it has people who have come from a wide range of IT backgrounds. They bring their experience and wisdom with them. They will know more than you. The combination of skills multiplies the speed at which the language develops. Read their posts, download their script and learn from them. Keep It Simple
Do not use Aliases, except for deliberate obfuscation.
Write with consideration
Do not try to cram all your scripted process into one line. In the Shared and Corporate environment other people will maintain your code and will not necessarily have the same PowerShell knowledge as you. Be kind in your code.
When do not use PowerShell
Once you’ve gained confidence in PowerShell, there is a temptation to use it for everything. It is definitely not the solution for everything in SQL Server, because we already have a great deal of power in Transact SQL. One of the major skills for a Database person that uses PowerShell is to know when not to use PowerShell.
PowerShell is the primary script language for Windows-based systems, providing a solid platform for automation, provisioning servers and so on. In a SQL Server, it is no different. You can do practically everything with PowerShell, via SMO and DACFx, but it worth it the time and effort?
The answer to this question will hit you square in the face if you ever try to create a table using PowerShell and SMO. ‘Ha Laerte, but I can use T-SQL called by PowerShell for that.’ Yes! You can but unless you need to automate, scale out, or repeat this operation, it is better to use SSMS and T-SQL. Oh Boy! Script or Function?
All script files are actually functions. You can call them from another script by referring to their filepath, and also provide parameters to them.
Technically speaking, a function is similar to a script variable, which is really an anonymous function or ‘closure’. What we refer to as a function is only a script to which you added a function keyword, its name, some parameters, some keywords and voooalla !!! We have a function that can be called by name.
A working IT professional will, if writing a single-purpose solution, generally write a script. I did this in my latest article on automating SQL Server Best Practices Review in Word. This is because:
It´s a single solution
Whoever will use should not be required to know anything about PowerShell, so If I had written a module with a lot of functions, this person would have had to perform an extra step to copy the module function to its correct path.
It does not need to be fully-reusable

If, however, you will use your script for more than one purpose, with flexible outputs, especially if you will use it in other scripts then make it reusable. You’ll need to switch to using a function and add this function to your function module. If you are unsure how a set of scripts will develop, but it is likely that they will be extended and enhanced, you will need to use functions.

Full article...

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/