SQL DMO – Distributed Management Objects

Tech Forum - Dr. Nitin Paranjpe

Here is another article to bridge the gap between developers and administrators. SQL Server is being used more and more widely to manage data for a variety of applications. The critical nature of database uptime is known to everyone who has faced a nasty server failure. Ongoing administration, monitoring and tuning of any database is very critical for long-term reliability and for maximising uptime.

The methodologies that developers as well as administrators have used for managing and tweaking SQL Server can be seen in Table 1.

All these methods have their merits and are also quite well known. However, there is one method of administering SQL Server which is simply not known and almost completely unused by either developers or administrators. This is called Distributed Management Objects. SQL-DMO encapsulates SQL Server components, presenting the attributes of the component piece to you as the properties of an object instance. Alter the properties of the instance, or use object methods to automate SQL Server administration.

This is a surprisingly simple and yet very powerful way of handling certain types of administrative tasks in SQL Server. It has been available from pervious versions. But somehow nobody has really looked at it seriously enough.

What is SQL-DMO?

DMO is a set of objects that represents the SQL Server infrastructure. This is a very rich and complex object model. It is not intended to provide data handling, which is best done by traditional SQL commands.

DMO is designed for administration of SQL Server using a component object model approach. In fact, using DMO, you can do things that are impossible with Enterprise Manager. Moreover, it is often much simpler to write a DMO based code rather than trying to find equivalent Transact SQL syntax for achieving certain administrative tasks.

However, do not consider this as a replacement to all existing methods. In fact, the reason I am highlighting DMO here is because it is often ignored. Once you get acquainted with it, do some further reading on it. You will be able to ascertain just where could be useful in your scenario. In fact, in an earlier article, I had used SQL DMO to find out a list of duplicate indexes across a database.

Why an object model?

DMO is an object model. This means it has a root object, which in turn has a hierarchy of objects under it. Each object can have more objects (collections) attached to it and also have properties, methods and events.

This is a very well established concept on the Microsoft platform in general. You will find very intuitive and highly usable object models for everything from MSN Messenger to MS Word to WMI to Active Directory to Browser to XML and so on. Microsoft excels in converting abstract and complex implementations of software into very intuitive and easy-to-use object models. In fact, creating object models to represent complex software is something we should also think of while developing software applications. But that is a topic by itself.

As you know, most of the administrative tasks which can be executed using DMO could also have been executed using other means. Then why DMO?

Let me give you a simple example.

Observe the syntax differences listed in the table above. What is the fundamental difference? T-SQL was created by extending baseline ANSI SQL syntax that is verbose and has a lot of legacy compatibility to manage. ‘Alter Table’ will continue to be called ‘Alter Table’ for ages. Whereas, in DMO, there is no such constraint. Each object has to have its relevant properties, methods and everything else that makes logical sense.

Therefore a database collection will contain individual database objects. It is extremely logical and intuitive to have a ‘size’ property for a database object, so it is there. Similarly, each database will have its tables collection. The SQL Server will have a databases collection. Each table will contain its columns collection, each item in the columns collection has to have some property called datatype. Just apply common sense and look at the object model. The DMO object model is based upon intuitive logical organisation of available functionality rather than some arcane syntax developed a long time ago.

You don’t even have to check the reference in Books Online. Just think where the datatype should be and you will find it there.

How does DMO work?

If you think DMO bypasses all the SQL engines and has its own inroads into the internals of the database server, you are wrong. DMO is a very cleverly implemented object model. Here is a small piece of code:

‘create dmo object
Dim srv as New SQLDMO.SQLSERVER
‘connect to the server
Srv.connect ‘nitin1’,‘sa’,‘ ’
‘count number of databases
debug.print srv.databases.count
‘display name of 2nd database
debug.print srv.databases(2).name

Now here is the smart part. As I have mentioned on many occasions before, it is important to keep looking at the SQL Profiler to understand what commands actually reach the SQL engine. Now, when the Srv.connect command is executed, internally it does a proper login using standard SQL commands. The interesting part comes later. When you ask for the count of the total databases, you would expect it to generate an SQL command something like this:

Select count(*) from master..sysdatabases

But it does not. Instead it executes a stored procedure:

exec sp_MSdbuseraccess N‘db’, N‘%’

This stored procedure returns a grid containing not just the count of user databases but also a host of other information. Almost like Select * from sysdatabases would return.

Now the next command, which requests the name of the 2nd database, has more surprises for you. It DOES NOT generate any SQL statement at all!

Why is that so? Because the earlier command that asked for the count has already got all the information DMO wants about the base properties of the databases counted. So it serves you from an internal cache. Brilliant object design. Remember to use such techniques when you design object models for your
applications.

Now if you ask for another bit of information…

debug.print srv.databases(2).tables.count

… a very complicated looking command is executed:

select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id,
N‘SystemObj’ = (case when (OBJECTPROPERTY(o.id, N‘IsMSShipped’)=1) then 1 else OBJECTPROPERTY(o.id, N‘IsSystemTable’) end),
o.category, 0,
ObjectProperty(o.id, N‘TableHasActiveFulltextIndex’),
ObjectProperty(o.id, N‘TableFulltextCatalogId’),
N‘FakeTable’ =
(case when (OBJECTPROPERTY(o.id, N‘tableisfake’)=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id, N‘IsQuotedIdentOn’)=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id, N‘IsAnsiNullsOn’)=1) then 1 else 0 end)
from dbo.sysobjects o, dbo.sysindexes i
where OBJECTPROPERTY(o.id, N‘IsTable’) = 1 and i.id = o.id and i.indid < 2 and o.name not like N‘#%’
order by s1, s2

This command is sufficient to manage most of the queries on the tables collection as well as individual table properties.

Thus the DMO is a brilliant method, which combines the raw power of SQL syntax with the convenience and simplicity of an object model.

Modifying the database structure

Let us see some other tasks like adding a field to a table. Here is the syntax:

Dim objcol as sqldmo.column
objCol.name = ‘sample’
objCol.datatype = ‘int’
objCol.allownulls = true
Databases(‘pubs’)
.tables(‘authors’).columns.add
(objCol)

Now this time, it does internally use a very predictable syntax:

Use [pubs]
ALTER TABLE [dbo].[authors] WITH CHECK ADD [sample] int NOT NULL

Compare these SQL commands with the logical arrangement of the DMO model.

Here is the detailed object model of the database level of the object hierarchy. This will give you an idea about how the objects and related items are logically organised for simplicity and power.

Extended DMO Objects

DMO was introduced in SQL Server 7. When the newer version came out, some new functionality was added to existing objects. However, to minimise code migration issues, a separate set of objects was introduced with a special naming convention ‘<object>2’. For example, the extended table object was called ‘table2’. ‘Table2’ offered more functionality over and above the base ‘table’ object. These are called Extended DMO objects.

Event-driven programming

Event-driven programming is a good feature of DMO. Consider an example. Normally you schedule a backup and as soon as the backup is over, you want to do something else. This something may or may not be another SQL batch. It could be anything—like notifying the administrator on mail or starting another transaction using a component or whatever else. Using traditional programming methods, this was very difficult to achieve. But now with DMO it is very simple.

There is a special COMPLETE event which occurs when backup or restore is completed. You can write code in the event to do whatever else you want.

Now, what happens when you want to show your own progress bar to indicate completion status of time-consuming things like backup, restore or replication? Don’t worry. Microsoft developers have thought of that also. There is actually a percentcomplete event, which can call your code. The event is raised on 10 percent completion steps.

When to use DMO?

Here are some areas where DMO will be extremely useful:

About the Author:Dr Nitin Paranjape is the Chairman and MD of Maestros (Mediline). He is a consultant with many organisations, covering appropriate technology utilisation, business application of relevant technology, application architecture and audit as well as knowledge transfer. He has authored more than 650 articles on various technology-related subjects. He can be contacted at nitin@mediline.co.in