First Post

So, belatedly, I become a blogger.

It's not that I don't like to write. I like to write very much. Too much, really. Anyone who has been the victim of one of my emails or read one of my postings on the old Swynk SQL DBA list can vouch for that.

The primary thing that has always held me back from blogging is a simple question: "What can I say about SQL Server that others have not already said?". There are a wealth of great blogs and articles discussing SQL Server and there is little point in piling on.

The one thing that I know better than any other DBA that I have come across is Powershell. (It's true that I don't get out much.) I have had a few people comment on how quickly I can slap together reports, push changes to databases and do other tasks. A key reason that I can do this is Powershell.

So, we'll push on with powershell for a while and see how the page hits work out. There may be a few side trips along the way, as I have a few useful scripts written in Transact-SQL and in VBScript and my main 'thing' is actually performance engineering.

First a little background. I am a long-time (ten years) SQL Server DBA but, let's face it, Transact-SQL isn't appropriate for everything. In prior lives, I worked as a developer and wrote in a variety of languages (C and various dialects of BASIC being the most famous, PL/1 and HPL possibly being the least famous). Transact-SQL has always felt like a second class language to me. The most obvious places where TSQL falls down are file manipulation, exporting data, error handling, program automation through COM, WMI, use of SQL-DMO, coupling and the like. Sometimes TSQL isn't appropriate just because there isn't any SQL Server in the picture. Using TSQL to write a report of free disk space on a group of servers, for example.

(I have also written a fair amount of code in "batch language", the native shell language of dos and windows. Such scripts are often syntactically mindbending, in a lovecraftian sense, the are hard to debug and should not be mentioned in polite company.)

In the late nineties, I started using VBScript in conjunction with Windows Scripting Host (WSH). This allowed me to write scripts to do things like search directory hierarchies to delete old files, script out SQL Server objects, fix job step output file paths, write change scripts, check disk space and do all sorts of other useful things. WSH is a good way to write small scripts that can manipulate files, perform simple-but-repetive tasks, automate programs through COM. WSH still has it's uses because powershell is not widely deployed yet.

A few years ago, with the advent of .Net and the tendency towards deprication of COM, I started looking around for a new language. I've tried several different things. For me, Jscript isn't any more useful than VBscript. I do not think that I have ever written anything longer than 10 lines long in bash, though I was a long time user of cygwin's bash and I maintain a couple of linux systems at home. I have worked on a few small things in perl, but I just never felt comfortable with it. Lots of smart people use python (and ruby), but it's more non-Microsoft stuff to install on a new computer.

I started using powershell about two years ago. At first, I used it simply as a replacement for the old Windows command shell. As I learned more of the language, I found myself using it more and more with my daily tasks. It was very easy to get started and to increase my skills as time went on and I discovered how to exploit powershell's features. In short order, I was writting scripts. Then I started using WMI, then COM, then .net objects. It's easy to get started and just go along at your own rate.

I'm not going to spend much time cheerleading for powershell. In fact here are the bullet points for my whole speech:
I believe that powershell is the best thing Microsoft has done in a long while.
I think that if you are a SQL Server admin or any sort of network or Active Domain administrator, and you have any tendencies towards scripting (as opposed to point-and-click use of GUI-based admin tools), you should be at least competent in powershell.
If you are working in Microsoft's universe, fighting Microsoft's tide is usually unsuccessful.
Microsoft seems to be trying its' damndest to get you to use powershell.

I am not going to try to teach powershell basics because there are plenty of good resources online. I don't own any powershell books, so I can't really make any suggestions. I'm used to teaching myself things, often under pressure, but a book or books might work well for you.

I am not going to show you lots of gee-whiz syntax or how to write terse and incomprehensible one-liners because I don't see much use in that, as far as trying to teach goes. It's fun as an intellectual exercise. It's pretty easy to turn everything into a one-liner, if you hide it in a function.

What am I going to do? A few examples:

I have a script that can report information on storage (sizes, free space, percent free, etc.) quickly, from a command line. With some powershell trickery, I can easily run this report on one server or dozens. The great thing about this is that the output from each server is coalesced into a single collection of queryable objects. I can dump the output to the screen, or stick it in a Excel file or a table.

I have a script that can run an ad-hoc SQL query on any instance or group of instances I choose. Similarly, I can coalesce the report into a single collection. This script can be fed instance names through a pipeline, so they can be stored in any form of permanent storage.

I intend to split my postings between functions in my profile and scripts that do more complicated things. I also intend to keep the length of posts down to something shorter than this one.