[ So have you ever wondered how many times Microsoft has to create new acronyms?  I bet someone get's paid for every new one they create. :) ]

Anyway, back to the topic at hand:

Managing SQL Server Objects

As I have written about in several articles here on my blog, I have been using SQLDMO to retrieve information from SQL Server and perform various activities that ordinarily I would have to use SQL Enterprise Manager for.

Today, I wanted to discuss with you an alternative to SQLDMO called SQL SMO, which ships with SQL Server 2005.

Here is a quick overview of both DMO and SMO:

SQLDMO:

  • Works with SQL Server versions 7, 2000, and 2005
  • COM based component - not managed code
  • Small footprint
  • Will work with .NET 1.1 and 2.0

SQL SMO:

  • Designed for SQL Server 2005 but most functionality will work with SQL Server 2000 and SQL Server 7
  • All managaged code
  • Requires .NET 2.0
  • Requires SMO to be installed
  • Requires SQL Server 2005 Native Client
  • Requires MSXML 6.0

 

Deciding Which to Use:

Looking at several of the examples on MSDN, it would seem that the SMO libraries are a bit cleaner than DMO and they allow you greater flexibility and functionality. 

I think you need to take into account the following items when planning your application:

  1. What is my market? Is it SQL Server 2000 or SQL Server 2005?
  2. Do my customers have SQL Server 2005 installed?
  3. If you are writing an add-on for another product, does that product require SQL Server 2000 or SQL Server 2005?
  4. What is your development platform? Visual Studio 2003 or 2005?
  5. How will your application be distributed?
    ( Web, CD, manual installation by an engineer. )

  

My Decision: 

But, after looking at all of the requirements, I decided to stick with using DMO until such a time as I encountered a feature of SMO that required me to switch to the new codebase.  As it turns out, most of my customers are running SQL 2Server 000 and will not be switching to 2005 for the foreseeable future.  So, I have a secondary reason for not making the switch.

Depending on your circumstances, you make take a similar route.  Although I like the idea of having a totally managed code solution, the costs far outweigh the benefits.   For me, requiring the user to download 50+MB of support software just to get my application to load, was more than I could stand.

In a couple of years, when the SQL Server 2005 market is more fully established, I am assuming I'll be making the switch and rewriting all of my DMO libraries to incorporate SMO features.

  

References:

SQL Server and DMO: Distributed Management Objects Enable Easy Task Automation

SQL Server Management Objects (SMO)