SQL-DMO - Using the '2' Objects

If you've used SQL-DMO very much you may have noticed that there are lots of almost duplicate objects. Examples are SQLServer vs SQLServer2, Database vs Database2, and many others. One question I get a lot is "which object should I use?", which is a fair question and worth a brief discussion.

To start with, you'll only see the "2" objects if you're using SQL2K (or at least the SQL2K DMO library). Let's take the SQLServer object as a good example. If you look in the help file in SQL7 or SQL2K, you'll get a huge list of properties and methods, which I'm showing here to save you a trip to Books Online:

Then if you look at the SQLServer2 object you'll see that it includes everything in the SQLServer object plus the following additional properties and methods:

I know I know, I haven't answered the question yet!

First, let's talk about why we have two sets of very similar objects. Object interfaces...the properties and methods that you can see above...are a contract with you the programmer. That contract is commonly called an interface. Once published, you're supposed to be guaranteed that if you set the loginsecure property (or any other, it's just an example) to true it always means the same thing and that the loginsecure property will always be there. You can release upgrades/revisions where you have new and improved code inside the properties or methods, but the name and the end result have to stay the same. Just so we all stay sane.

That's what you get with SQL7 - a new release of SQL-DMO that goes with the server and naturally doesn't support a method such as "listinstalledinstances" because in SQL7 there are no instances.

Now MS decides to build SQL2K and add in a lot of neat stuff that we've been requesting, named instances (who asked for that??) being one good example. Now they need to add support for it to DMO, but need to do so without breaking all the code that is already out there. That means they can't just add the ListInstalledMethods to the SQLServer object. The best way to solve this issue (and Im only guessing that this is what they did) is to use inheritance to create a new object called...SQLServer2. It inherits all the properties and methods of the SQLServer object and adds a few new ones.

Now we get closer to the answer. If you need to access SQL2K specific functionality you have to use a '2' object. If your code will never run on a SQL7 box, use the '2' object. What if your code will run on both versions? You have two choices. The first is to avoid the '2' objects, basically coding for the lowest common denominator. The other is to use the '2' objects and test very early on to determine which version of SQL is running. If you detect it's SQL7 then you..the weary developer...have to code so that you never use any of the SQL2K specific functionality in the objects.

Should you upgrade your code to use the '2' objects? Almost the same answer. If you need the functionality, yes. If not, don't sweat it.

All in all it's a fairly decent solution. Should be interesting to see if when Yukon is released we get a set of '3' objects!