PowerShell in SQL Server 2008 – Part I

Hello Geeks,

PowerShell is a very new feature which is being included in Microsoft SQL Server 2008. If you don’t know much about it, then you don’t have to worry anymore. I will give some heads up:

SQL Server 2008 introduces support for Windows PowerShell. Windows PowerShell is a powerful scripting shell that lets administrators and developers automate server administration and application deployment. The Windows PowerShell language supports more complex logic than Transact-SQL scripts, giving SQL Server administrators the ability to build robust administration scripts. Windows PowerShell scripts can also be used to administer other Microsoft server products. This gives administrators a common scripting language across servers.

While before the introduction of PowerShell, most administrators used command-line batch files, VBScript, or some third-party proprietary application designed for this function.

The Batch files had somewhat limited functionality, and significant security problems. It also suffers significant limitations with regard to iterative processes.

The VBScript was a better solution to the problem of iterative processes and provided interfaces to many of the server products offered, but it lacked consistency and was reliant on the COM object model. But the introduction of .NET Framework rendered the COM object model (obsolete).

PowerShell addresses these limitations through a strong security foundation that prevents command-line hijacking by only allowing the user (by default) to run digitally signed scripts, and by forcing the user to specify the directory for each command run from the script environment. It also supports the .NET Framework (2.0 and later), so the functionality built in the framework is available in this scripting environment.

Microsoft has recently designated PowerShell as a part of its common engineering criteria (CEC) for all server products. This means that an administrator who’s responsible for Exchange or Active Directory will probably have spent time learning PowerShell, and scripts written to manage SQL Server using PowerShell can be understood by administrators without specific SQL Server knowledge. Companies will be able to run more efficiently with this common scripting language and administrators with skills in PowerShell will be more valuable to their companies.

Language Features:

A cmdlet (command-let) is a command-line utility built into PowerShell to provide some functionality. These cmdlets use a verb-noun naming convention, so it’s fairly easy to understand what they’re doing. Microsoft has provided about 130 built-in cmdlets with the default installation of PowerShell (1.0), and additional cmdlets are installed depending on various server products that may be running.

   

Cmdlets are frequently aliased, i.e., a different command can be entered to run the cmdlet, rather than using its own name.  Like, when browsing a directory in the PowerShell cmdlet, to view the contents of the current directory is Get-ChildItem. We can also use the commands that we have used in the DOS operating system (dir command) and in the UNIX (ls command). All these three command will perform the same function.

One of the features of UNIX shell scripting environments is the capability to ‘‘pipe’’ the results of one command into another command’s input buffer. This is a feature of shell scripting that makes it so powerful – the ability to string multiple commands together to provide information quickly.

PowerShell provides this ability, but differs from Unix shell scripting in that the Unix pipe sends text from one command to another, whereas PowerShell pipes objects from one cmdlet to another. Unix scripts must parse the text from one command using commands such as grep, awk, and sed to format the text in a form the next command expects. PowerShell’s objects are understood by the receiving cmdlet and no such parsing is required.

Here’s an example of using a series of cmdlets, the get-process command is used to return a set of all processes on the system, pipe the results into a sort on the size of the workingset, or the amount of memory each process is using, in descending order, and then select just the first 5, returning the biggest memory hogs on the system:

1_SQL_Server_PowerShell_in_SQL_Server_2008_Part_I

Well in next segment I would like to deal with the commands and the variables that we use in Windows Powershell and some more examples.

Hope you like it.

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published.