Click here to monitor SSC
A service from Red Gate

in
Home SQL .NET SysAdmin Opinion Blogs Forums Books
Sign in | Join | Help
Home Blogs Forums
Blogs Home > Tony Davis > Reasons to Deprecate

Tony Davis

Simple-Talk Editor
News, views and good brews

Reasons to Deprecate

Published Friday, October 24, 2008 3:39 PM

I'm happy to see features and services of SQL Server deprecated by Microsoft if it is for a good reason. Good reasons include conformance with SQL Standards or rejection by the community of users; I consider Microsoft's commercial convenience to be a bad reason.

 

I suspect that everything possible has been said about Microsoft dropping Notification Services in SQL Server 2008. It is unprecedented for a service to be removed without an adequate substitute. Actually, there is an unsupported pre-release version of SQL Server 2005 Notification Services Components Package RC1 that has been altered to work with SQL Server 2008, but you have to download it separately. Its fate is sealed, and there is little cheer for the folks who have built complex systems around it, beguiled by the siren words of the Microsoft Marketing Department.

 

DMO is another SQL Server component that is being phased out. Poor DMO is still hanging on there by its fingernails, however. Microsoft hates it, but any users who have lots of old scripts still need it and like it. The 'softies' keep telling us we don't need it any more now we have Powershell and SMO; but to most DBAs, Powershell is a strange, unintuitive language cast in the UNIX mentality.

 

The two services are deprecated, but for entirely different reasons. Notification Services wasn't liked much by users, and take-up was poor. It was badly designed, and there has been a smell of death around it for some time. The WMI event model in SQL Server 2005 that used it was absurdly complicated, and it was hurriedly superseded by a new and simpler event handling system, namely Extended Events (XE).

 

DMO, on the other hand, was deprecated because it was inconvenient. Users liked it because any scripting system that could use OLE automation could use it. Even Perl could be used to automate SQL Server administration tasks. There was a botched attempt to allow DMO's replacement, SMO, to use OLE Automation, but SMO emerged as a .NET Assembly only.  Although a positive spin was put on it, the truth was that if you wanted to use SMO, then you now had to write compiled applications with C# or VB.NET, or grit your teeth and learn Powershell. DBAs weren't fooled, and weren't about to re-write hundreds of scripts just because Microsoft didn't want to maintain a scriptable version of SMO.

 

DMO lives because there is no viable alternative.

 

Cheers,

 

Tony.

by Tony Davis

Comments

 

Adam Machanic said:

Huh?  Since when do you need to write a compiled app to use SMO??  You can use VBScript or JScript, just like with DMO.  Here's a sample.

------EnumerateDBs.VBS--------------------
Set objServer = CreateObject("SQLDMO.SQLServer")
objServer.LoginSecure = True
objServer.Connect "(local)"
' Enumerate Databases
For Each objDataBase In objServer.Databases
 MsgBox objDataBase.Name
Next
objServer.Close
-----------------------------------------

WMI and XEvents are totally different things, with totally different purposes, and I don't see how either has anything to do with the demise of NS.  I'm not sure how you're making the connection there, but I feel the link may be as tenuous as that between SMO and the need for compiled apps.  NS had poor uptake because A) Very few people understood what it should be used for, B) Very few people, in reality, need fullblown notification applications, C) Those who do need fullblown notification applications discovered that the NS XML configuration was incredibly difficult to put together and manage, and had no UI at all, and D) To make matters worse, the documentation was worse than horrid and the samples didn't work.  Good riddance on that one!
October 29, 2008 10:19 AM
 

Allen White said:

Tony, DMO lives because administrators are unwilling to change, not because there's no viable alternative.  DMO was poorly structured and poorly implemented.  When you loaded a Database object you had to wait for ALL the databases objects to load, whether or not you needed them.  It uses COM, which across the board Microsoft has left behind, in favor of the .Net Framework.

SMO has a logical object model, loads objects when you need them and uses the Framework.  The integration between SMO and PowerShell is strong (come to my two sessions at PASS where I'll be discussing just that integration and how to use it), and PowerShell is the scripting language that administrators have been looking for while they rummaged around with VBScript, which was somewhat painful.

PowerShell has the additional benefit that it's part of Microsoft's Common Engineering Criteria, so ALL server products coming out of Microsoft will incorporate interfaces to PowerShell.  What this means is that in all those shops where there's no true DBA present, the network admins who are adopting PowerShell to automate their administrative functions can use PowerShell and SMO to effectively manage SQL Server.  This is a huge benefit to the users of SQL Server across the board.

I look forward to seeing you in Seattle next month, and look forward to continuing this discussion there (over a pint or two, of course!)

Allen
October 29, 2008 10:22 AM
 

Adam Machanic said:

Hmm, appears I posted a DMO script, not an SMO script... disregard :-)
October 29, 2008 10:43 AM
 

Adam Machanic said:

SMO version (snagged from Peter Ward's blog):

------EnumerateDBs.VBS--------------------
Dim db
Dim oSMOServer

Set oSMOServer = CreateObject("Microsoft.SQLServer.Management.SMO.Server")

For Each db In oSMOServer.Databases
   MsgBox db.Name
Next

Set oSMOServer = Nothing
-----------------------------------------
October 29, 2008 10:50 AM
 

Allen White said:

Adam,

The problem with VBScript and SMO is that in VBScript you can only connect to the local default instance of SQL Server.  This is why in my early demos I used VB.Net and have migrated to PowerShell.  They started to write a COM wrapper for SMO to allow it to be used in VBScript, but stopped when they saw what PowerShell offered.

PowerShell is NOT a hard scripting language to learn - in fact I think it's a LOT easier than VBScript, and once admins get over the fact that everything in Windows is no longer point-n-click I think they'll discover the real advantages of PowerShell.
October 29, 2008 12:28 PM
 

JonRobertson said:

We're an ISV that develops native code applications that use SQL Server as the backend.  Our products are compatible with SQL Server 2000, 2005, and 2008.  We have literally dozens of applications or utilities that access SQL Server via DMO.

I have no desire to modify these applications to use SMO.  Why should I?  The applications work just fine whether our customer is using SQL Server 2000, 2005, or 2008, thanks to the Backward Compatibility Components.

When (not if) DMO is dropped, I will either have to rewrite our applications from scratch using .NET technologies, or build a custom Interop layer in .NET that is exposed to COM.  Delphi has some strengths here that Microsoft tools do not have, and that is probably the route I'd take.  Some of our utilities are small and simple enough that a .NET rewrite would not take long.  But most of our applications are large enough that a .NET rewrite would take time and money, with no added benefit to the customer.

However, if we built a custom COM interop that just exposes the functionality we use, with a DMO compatible interface, then all we should have to change is the object name being created.  Of course, I'm dreaming, but without dreams we'd have no solutions...

Microsoft has warned us: The writing is on the wall, er, web page.  One of these days, DMO will not be supported by SQL Server, and we'll have to spend our resources to rewrite a lot of code just because Microsoft abandons another API.  It makes me cringe every time I start investigating another API or SDK from Microsoft.  I'm SO thankful I never invested any significant time with Visual Basic.

Although Microsoft touts that "Because SMO is compatible with SQL Server version 7.0, SQL Server 2000, SQL Server 2005, and SQL Server 2008, you easily manage a multi-version environment", I'm skeptical.  For one, I can't use SMSS 2005 to connect to a SMSS 2008 server instance.  What the ???  I can use Query Analyzer to connect to SQL 7, 2000, 2005, and 2008.  For another, I figure about the time DMO is actually dropped, they'll release yet another API and announce that SMO is deprecated.

Which is another reason I'll hold out as long as I can.  I'd much rather rewrite once for absolutely no material gain to my customers than do it twice.
October 29, 2008 1:31 PM
 

Adam Machanic said:

Hi Allen,

Thanks for the clarification.  I clearly shouldn't have tried to get involved in a DBA topics debate, but I had that code sample sitting around and figured I might as well use it for something :-)

Personally I haven't touched PowerShell and don't plan to, at least in the near future.  But I have done some SMO programming in C# -- a language I love -- and I think it is also not at all difficult to learn.  The worst thing about the SMO programming?  SMO itself seems half-baked in a lot of areas, especially if you're working with some of the data transfer methods (which is exactly what I was doing).  
October 29, 2008 1:32 PM
 

zenon said:

DMO was introduced in SQL 7, elaborated in 2K and being killed in later edtions. Same for DTS. Same for .....

The fact is, if one EVER uses a Microsoft-centric "feature" that is not part of the ANSI-SQL standard then you are effectively putting a knife in the eye of your business. Think about it. You spend hundreds of hours building a custom application and it works fine until the forced upgrade to Windows 2008 where 2000 will not install/work. All that money wasted.

But if you wrote those same processes in T-SQL using only standard stuff you find elsewhere then it will port. A simultaneous consideration are all those objects you wrote in VB 6. They don't work very well in the new universe of .NOT do they? But if you had written them in C/C++ they would still work (assuming you wrote them in the borland compiler or a generic compiler.)

We all need to learn to bullet-proof ourselves and our users from the vicious whims of the Microsoft Marketing Monkeys (MMM).  Their job is to churn the market and sell stuff. Our job is to protect the business. Those two challenges are direct opposites.
October 29, 2008 2:06 PM
 

NieGro said:

New features in SQL Server 2005 and beyond is not handled by DMO. For example database mirroring which is widely used by the organization where I work. These features con only be scripted by .NET scripts like PowerShell or IronPython. This is why we are learning and using PowerShell. Another benefit is the wide functionality that the .NET framework gives us.
Basically I think Microsoft can call their API(-s) what they want to - as long as the whole functionality is covered and can be integrated in the rest of the IT infrastructure. Right now that is a problem using Microsoft System Center Operations Manager (SCOM) and other operating systems like z/OS.
October 29, 2008 2:47 PM
You need to sign in to comment on this blog
Latest articles
A first look at SQL Server 2012 Availability Group Wait Statistics
 If you are trouble-shooting an AlwaysOn Availability Group topology, a study of the wait statistics... Read more...

SQL Server Prefetch and Query Performance
 Prefetching can make a surprising difference to SQL Server query execution times where there is a high... Read more...

SSIS Basics: Setting Up Your Initial Package
 When working with databases, the use of SQL Server Integration Services (SSIS) is a skill that often... Read more...

Checking Out SQL Backup Pro 7’s New Automatic Backup Verification
 Wouldn't it be great to offload the daily chore of checking the integrity of your production... Read more...

Chuck Lathrope: DBA of the Day
 Chuck Lathrope was a finalist for the Exceptional DBA of the Year award in 2009. We contacted him to... Read more...