All right, I’ll admit it. I’m a secret PowerShell user. I lap up everything that Chad Miller writes; I think SQLPSX is great; Lee Holmes’s PowerShell Cookbook is there, well-thumbed beside my comfy chair; I lurk around the http://poshcode.org/ site. I find it so great to be able to fire up batch jobs that would otherwise have sentenced me to hours of drudgery. I am, after all, the sort of un-reconstituted, unreformed, geek who would rather sweat blood over a recalcitrant PowerShell script, to get it just right than bore myself rigid doing repetitive jobs in SSMS, in the same amount of time.
I can’t believe that I’m that unusual, but I’ve learned over the years not to write or talk too much about PowerShell scripting since it is a topic that is likely to empty a roomful of DBAs even faster than the words "XSD" or "XPath". I realise that the rather bizarre, human-hostile nature of the PowerShell language is likely to put off the timorous; there is a certain amount of pain attached to the acquisition of PowerShell skills. However, this is more than compensated for by that surge of pleasure when a script fires off successfully and saves hours of work.
I think it is my fondness for automating routine work that makes me worry that Server Management Objects (SMO) is not getting the necessary love and nurture from Microsoft. Maybe I’m over-reacting to the slow rate at which SMO is being developed. By its very nature, it is a conservative system whose essential virtue is its ability to access and manipulate servers and databases, regardless of version. Change may be exciting, but it is likely to break existing scripts.
The utility applications in SMO are patchy. Scripting works well, and obviously has to be kept up to date because SSMS uses it, but the Transfer Database utility is comically slow, and is obviously there just for backward compatibility with DMO. There is obvious scope for adding a range of common tasks that irk the busy DBA but, for some reason, the SQL Server team never seem to put much energy into the scripting side. SQLCMD is frustratingly lightweight, and SQLPS is a poor, shrivelled specimen compared with the community-based SQLPSX. The scripting examples on MSDN for SQL Server aren’t nearly as good as one might hope and expect.
Powershell and SMO together can weave powerful magic for the DBA. I just hope the SQL Server team gives priority and energy to developing and supporting the tools that can make this form of scripting so rewarding.