Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Is Microsoft neglecting SMO?

By Phil Factor,

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 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.

Phil Factor.

Total article views: 495 | Views in the last 30 days: 1
Related Articles

Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO.


SQL Powershell Script to check Server Uptime Remotely

SQL Powershell Script to check Server Uptime Remotely


Performing an INSERT from a PowerShell script

This code demonstrates how to do an INSERT into SQL Server from a PowerShell script using an ADO.NET...


Script Replication with PowerShell

I promised you some PowerShell scripts this year and this post is the first one.  I can see the valu...


How to create Policies using PowerShell scripts

This article explains how to create policies with user defined conditions in MS SQL 2008 using Powe...

database weekly    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones