By: Edwin Sarmiento | Comments (2) | Related: 1 | 2 | 3 | > PowerShell
Problem
I have been hearing about Microsoft's scripting language called Windows PowerShell which is extensively used nowadays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA.Solution
Windows PowerShell is an extensible command-line shell and associated scripting language built on top of the .NET Framework v2.0. It was released in 2006 and is currently available for Windows XP SP2/SP3, Windows Server 2003, Windows Vista and is included in Windows Server 2008. PowerShell will be included as a common engineering criteria (CEC) in future releases of Microsoft server products making it a must to learn how to use it. Administrators - and DBAs included - have been using scripting to automate administrative tasks with scripting languages like DOS batch, VBScript, Perl, and a few third-party tools like KiXtart and WinScript. Windows PowerShell complements the administrators' scripting toolkit to easily manage and administer Windows workstations and servers and other Microsoft server products as they are being built using the .NET Framework. Although it is designed for operating systems, Windows PowerShell can be used to administer SQL Server 2005 instances and higher as Server Management Objects - the object model used to manage SQL Server 2005 - are built using the .NET Framework, thus, exposing the object model in PowerShell. In fact, SQL Server 2008 ships with its own PowerShell snap-in.This series of articles will be dealing with introducing Windows PowerShell, its language semantics and how we can write scripts. Once we have a good grasp of Windows PowerShell, we will progress with using it to administer SQL Server.
Depending on the version that you would like to work with, you can download Windows PowerShell from the Microsoft Download Center. The publicly available released version of PowerShell is v1.0 and this is the same version that gets installed along with SQL Server 2008 (v2.0 is available as a community technology preview CTP and is the version that I am currently using). Make sure you already have Microsoft .NET Framework v2.0 (and SQL Server 2005 client tools for managing SQL Server with PowerShell) installed with the latest service packs on your machine before installing Windows PowerShell. After completing the installation, you are now ready to launch Windows PowerShell. You can do this by running the powershell.exe executable from either your command-line or from the Run command. This will open up a command shell similar to DOS, but with a PS prompt before each line.
You might notice that the font color might be different from the usual DOS environment that you're familiar with as I have configured the PowerShell environment differently so as not to get confused between the two.
Navigating the system using the PowerShell command-line
The best way to learn Windows PowerShell is to translate the common tasks that we do using our command-line like listing files and file system properties. PowerShell provides access to these as well as other Windows resources like the registry but with a twist - it exposes these additional resources so that they look like drives, giving you a similar approach when working with them. To display all of the resources in your system, you can use the Get-PSDrive cmdlet
Notice that the drives are not just limited to letters. HKLM, for example, maps to the HKEY_LOCAL_MACHINE portion of the registry. To illustrate, we can navigate to the SQL Server registry key using the usual cd and dir command we use in DOS and list all of the subkeys under that.
You can explore all of the resources available as exposed by PowerShell by just using those familiar DOS commands.
Using as much lifeline as we can get
Being new to the environment, we need all the help we can get to make the most out of our learning experience. The most important cmdlet that we can ever learn from Windows PowerShell is the Get-Help cmdlet. This displays help about Windows PowerShell cmdlets and concepts. If you want to know what a particular cmdlet does, simply pass that as a parameter to the Get-Help cmdlet as shown in the example below. Let's say we want to know what the Get-PSDrive cmdlet does
Get-Help is just one of the cmdlets available in Windows PowerShell. But how do we know what else is available for us? You can use the Get-Command cmdlet to display a list of all the Windows PowerShell cmdlets
These two cmdlets will provide most, if not all, of the help we will need when working with Windows PowerShell. But, that's not all. In case you have forgotten about the exact name of the cmdlet but remember the first letter of the second part of the name, you can use the Tab key to use the auto-completion feature similar to how it works with DOS. Let's say you would like to display a list of cmdlets that start with Get-H, you can type the first part and use the Tab key to search for what you're looking for. This will display cmdlets such as Get-Help, Get-History and Get-Host..
So, what exactly is a cmdlet?
We have been talking about and using cmdlets in our examples but have not really defined what a cmdlet is. Cmdlets (pronounced as command-let) are miniature commands similar to those of command-line tools that perform specific tasks inside Windows PowerShell. They are named using a standard naming convention with the verb-noun format. This naming standard makes it easy to "guess" a cmdlet that performs a specific task, like the cmdlet to retrieve all the process running in Windows is named Get-Process. You can use the built-in PowerShell cmdlets or create your own that can be installed to extend its functionalities, similar to what Exchange Server 2007 and SQL Server 2008 have done.
Aliases
Aliases are alternative names assigned to a cmdlet which make it easy for us to type a cmdlet without entering its full name. PowerShell has its own list of built-in aliases, like the one we already used - dir and cd. The dir command (together with the ls alias which is similar to that of Unix) and cd command are aliases for the Get-ChildItem and Set-Location cmdlets, respectively. To list all of the available built-in aliases, run the Get-Alias cmdlet
You can also create your own aliases if you want to call other cmdlets a different name or maybe define your own functions and call them using your user-defined aliases.
Next Steps
You have just scratched the tip of the iceberg with Windows PowerShell. Throughout this series, we will be exploring the power of Windows PowerShell and will progress to write a simple PowerShell script to manage SQL Server
- Download and install Windows PowerShell
- Check out the Windows PowerShell Owner's Manual from Microsoft TechNet
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
