SQL Server Management Objects: Finally ready for prime time

PowerShell gives DBAs a reason to begin using SMO for real, so now we'll find out about all the gory details

I was talking to a good friend at Microsoft last week, telling him about the problems I've been having with SQL Server Management Objects (SMO). I uncovered a few bugs both last year and this year -- well, not everyone might consider them bugs, but they're inconsistencies at least, and I think a couple of them are bugs. Anyway, he asked me why I'm now beginning to find them, given that SMO has been around for so long. I thought for a minute and said, "It's all PowerShell's fault."

Let me explain. Traditionally, SQL Distributed Management Objects (DMO) and SMO have been complicated enough to code that many DBAs stayed away from them. It's not that they're vastly more complicated than anything else, but you had to access them from a scripting language, which was usually VBscript. Even for those not intimidated by the code itself, it's too much code to mess with -- it's not an efficient way to do things.

[ Keep up with app dev issues and trends with InfoWorld's Fatal Exception and Strategic Developer ]

With the coming of PowerShell, however, all of that is gone because it makes calling SMO much easier; the code is much shorter as well. In essence, SMO has been given back to us as a usable method for administration. Not only are DBAs finally starting to use it en masse, but PowerShell itself uses SMO to do a lot of its tasks.

Things are starting to get interesting. Before, only those with the stomach to sludge through its drudgery tackled SMO, but now, SMO will be used by the masses. Any potential idiosyncrasies are going to be called out -- sometimes painfully.

I'm not saying that SMO is completely inadequate; I'm just saying that it hasn't been battle-tested nearly as thoroughly as it will be with the wide adoption of PowerShell. I'd be interested in seeing how many bugs and feature requests start turning up at Microsoft because of it.

I can also predict with a fair amount of certainty that the newer technologies like xEvents and StreamInsight will follow the same pattern. Both are relatively difficult for DBAs to use, so only the brave are attempting them. However, as these technologies mature and start gaining friendlier interfaces, the real testing will begin when they are adopted by the masses, who will put them through the wringer.

This article, "SQL Server Management Objects: Finally ready for prime time," was originally published at InfoWorld.com. Read more of Sean McCown's Database Underground blog and follow the latest developments in databases and data management at InfoWorld.com. 

From CIO: 8 Free Online Courses to Grow Your Tech Skills