PowerShell Basics for DBAs

As the DB field gets more mature and as DBAs become more seasoned, so do companies. So companies are starting to keep more and more data, which in turn means more and more servers, which in turn means that DBAs can no longer manage things the way they used to. For example, it’s no longer acceptable to run through your morning checklist to make sure that servers are behaving and that backups are ok. This is where PowerShell comes into play. DBAs can now not only manage multiple servers at once, but they can do it very easily with often times very little coding.

Familiar concepts

PowerShell is easy because it presents you with things as drives. For example, if you wanted to access your C-drive you would use “CD C:” in PowerShell, just the same as you would in DOS. However, if you wanted to access your registry as well, you would also access it like a drive by typing “CD HKLM:” for example. Or if you wanted to access SQL Server via PowerShell, you’d type “CD SQLSERVER:”. So you can see that the concept is very simple… everything can be accessed as a drive.
Now, let’s say that you want to connect to a SQL Server, and to a specific DB. You’d simply type:
>cd SQLServer:\SQL\ServerName\Default\Databases\DBName
And with everything exposed as a drive path, you already understand the structure of everything you’ll do in PowerShell. It also makes it really easy to switch between servers, or databases, or any other objects because all you have to do is replace the name of the object and leave the rest the same.
Now that I’ve explained how PowerShell is organized let’s take a quick look at how you get information about the different drives you can work with. The command for finding drives presented to you is ‘psdrive’ and you type it at the PowerShell prompt like this:
>psdrive
And when you do, you’ll get output that looks something like this:

Of course, your output will look slightly different depending on what you have loaded on your box, but a lot of it will be the same too. So looking around here you can see several drives listed.  I’ll go through some of the more interesting ones:

• Variable – here you can see all the built-in and user-defined variables for your active session.
• Function -- here you can see all the built-in and user-defined functions for your active session.
• Env – here you can see all the environment variables and values for your box.
• HKCU – HKey_Current_User registry hive.
• HKLM – Hkey_Local_Machine registry hive.

And you can work with each of these just like you would the local file system.
Another major aspect of PowerShell is the cmdlet. These are the same as commands in DOS only they’re much easier to work with. The thing that makes them so much easier is their consistency. Cmdlets are always in the verb-noun format… always. Some good examples of cmdlets are get-service, out-file, get-process, format-table, and get-content. In the coming articles these cmdlets (and more) are going to become your best friends.

To use a cmdlet you have simply to type the name of it at the prompt and pass it whatever parameters it needs. Sometimes a cmdlet doesn’t need any parameters to give you a default output. Take get-service for example. It works all by itself. So if you type get-service at the prompt like this:
>get-service
You’ll get output that looks like this:

But what if you wanted to pass it a parameter? That’s the other thing that makes cmdlets so easy to work with. They all accept parameters in the exact same way. To pass a parameter to a cmdlet simply type “-“ followed by the parameter name, then a space and the value you want to pass in. Here’s an example using get-service:
>get-service –computername Server1
See, no more wondering if you have to use a dash or a slash, and no more wondering if you have to put a space between the parameter or not. They all work the same. Of course, for all string inputs you’re welcome to use double or single quotes if there aren’t any spaces in the string it isn’t necessary. All cmdlets handle errors in the same way too. Each one has a set of common parameters that they all support: debug, errorAction, ErrorVariable, and WarningAction, just to name a few. This means that error handling and debugging are built into every cmdlet so functionality will be the same across the board no matter if you’re working with SQL Server, IIS, Exchange, or anything else. It’s all the same.