return to OCLUG Web Site
A Django site.
June 30, 2009

» Windows PowerShell for the SQL Server DBA

Most people think Windows administrators make a living with their right-hand—you know, right-clicking and left-clicking the user interface to get things done. While anybody can do that in Windows, the real value comes in when you no longer need to rely so much on the user interface but instead write scripts. Lower total cost of ownership is achieved when the administrative costs are kept low, and this is where Windows PowerShell comes in.

I’ve spent a fair amount of time writing VBScript scripts to administer Windows servers and workstations and automating repetitive tasks. One reason for me moving into Windows PowerShell is its roots in the Microsoft .NET Framework, as I have done a fair amount of .NET programming. But what is Windows PowerShell anyway?

Windows PowerShell is an extensible command-line shell and an 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-learn for Microsoft server administrators.

Administrators (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’ existing 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. And since SMO is compatible with SQL Server 2000, you can administer SQL Server 2000 instances using Windows PowerShell. SQL Server 2008 even ships with its own PowerShell snap-in.

No wonder it makes sense to learn a thing or two about Windows PowerShell. Besides, I’ve seen Windows administrators being “forced” to do SQL Server DBA tasks even without knowing what T-SQL is. Windows PowerShell makes it a level playing field.

I will be posting a series of blog posts on getting started with Windows PowerShell, and how any Windows administrator can use it for their day-to-day tasks. In the process, I’ll also cover how to use Windows PowerShell for administering SQL Server instances.

June 2, 2008

» Emacs Keybindings in Bash

Or, How to Be a Command-Line Commando

Does it surprise you to learn that I’m a Linux guy? I’ve been using Linux, to the exclusion more-or-less of everything else, since about 1999. In the past, I’ve done a little programming and some junior system administration. I’m even LPI-certified.

With this background, I’m quite comfortable working in the shell (AKA the command-line), the natural habitat of the sysadmin[1]. I frequently open a shell to do some quick work, and when I do, I use GNU’s Bash, which is the default on most Linux distributions. (I believe it’s also the default shell in Mac OS X.)

One of Bash’s features is editable command-line history, which makes your current command-line and its entire history available to you as an editable buffer. That offers a great way to streamline your work in the shell.

I suspect, however, that many shell users don’t even know about this better way. And it baffles me that many SAs I have seen in action — including some of Pythian’s own — don’t use this. They almost seem to prefer unnecessary effort — smashing away at their keyboards, repeating themselves, deleting with the Backspace key, scrolling, forwarding their cursor one character at a time, copying and pasting with the mouse, and so on. That’s a lot of elbow grease.

With Bash, or any other shell that uses the GNU readline library, you can use the following Emacs-like key-chords to make your life better. The point of this (as with so many things sysadmins and programmers do) is to save you effort, viz. typing. These aren’t all of them; they’re the ones I use:

Keys Effect
CTRL-P go to the Previous command in your history
CTRL-N go to the Next command in your history
CTRL-R Reverse-search through your history
CTRL-S Search forward through your history
CTRL-A Move the cursor to the beginning of the line
CTRL-E Move the cursor to the end of the line
CTRL-W delete a Word backwards
ALT-D delete a word forwards
CTRL-F move the cursor Forward 1 character
CTRL-B move the cursor Backward 1 character
ALT-F move the cursor Forward 1 word
ALT-B move the cursor Backward 1 word
ALT-_ undo

It takes a little learning to get these under your fingers, but it’s worth it. Too often, easy-to-use GUIs get us started quickly, and then leave us handicapped in our work. Spending the time to learn how to use capable tools pays off.

If you don’t want the default Emacsisms, there’s a vi-mode too — try set -o vi. I am a vi guy (in addition to a Linux guy), so I don’t mind modal editing, except that in the shell, there’s no way to tell which mode I’m in at any moment. Some commando out there might have put together a fancy custom PS1 prompt that shows vi-modes, but I really haven’t gone searching.

If you’d like a more thorough read about this, try the Bash Emacs Editing Mode (readline) Cheat Sheet or “man readline”.

1. As it happens, knowing how to use Unix text tools such as grep, sed, and wc (to name just three) is very helpful to a writer, too.