Introduction to SQL-DMO

What is it?

SQL-DMO (Distributed Management Objects) is a powerful

collection of objects that gives developers and DBAs programmatic access to the

administrative and development components SQL Server.  DMO uses the MS SQL Server ODBC driver to connect to an

instance of SQL Server, and there are numerous stored procedures installed with

SQL Server that support the functionality of DMO (they are also required for

Enterprise Manager to work).  These

sps are installed from a master script called "sqldmo.sql" that can be

found in the " C:\Program Files\Microsoft SQL Server\MSSQL\Install

"directory on a standard SQL Server Installation. 

The script can be re-run if needed to repair damaged or missing procs.

You can access DMO through a COM development environment

like Visual Basic or C++, and execute a wide variety of database administration

and development tasks through the exposed properties and methods. 

VB is my development tool of choice, and that will be reflected in the

examples and references I use here.  If

you don't have an instance of SQL Server running on your development machine,

chances are you don't have the necessary components to access SQL server through

DMO.  Not to worry - the kind folks

at Microsoft were nice enough to wrap everything up in a single DLL, aptly named

"Sqldmo.dll" (you'll need the appropriate header files if you are

developing in C++).  I've never

actually had to register the DLL on its own before, but I expect that it should

be fairly simple to copy it onto your development machine, register it, set a

reference to the "Microsoft SQLDMO Object Library", and start coding

away.

What is it good for?

Two things that I like about DMO:  1)  It

facilitates quick problem-solving turn around from concept to implementation,

and 2) Little or no T-SQL required.  As

much as I love SQL Server, I'm not a big fan of complex T-SQL for

administration, and I am a huge fan of solving problems quickly and efficiently

(Yukon cannot get here fast enough).  DMO

has proven invaluable in our environment for quickly automating maintenance

tasks that require scores of routines to be checked against nearly 300

databases.  It allows you to quickly get into the guts of SQL server do

some quick integrity checks, all from the comfort of your favorite development

environment.  If there is a

maintenance routine that you find yourself firing up on an increasingly frequent

basis, then you may want to look at DMO to help take on the drudgery.

I have also seen a few home-grown applications that are

designed to augment or replace the enterprise manager, and DMO is a core part of

those programs.  If you have the time and the inclination, you can use DMO

to build an application or utility that shields your user from the intricacies

of database development and SQL syntax, enabling them to create database objects

and perform a variety of administration tasks without the benefit of SQL client

tools.  The merits of doing so are beyond the scope of this article, and

should not be taken lightly.

The DMO Object Model

DMO was put together just like any other good object

design; it has its top level objects, properties, methods, and collections of

other objects.  These sub-objects in

turn have their own properties and methods, and in some cases (like backups)

have that ability to raise events.  I

have a graphic that outlines the object model hierarchy, and it

should give you a good idea of what DMO is capable of regarding SQL Server

functionality.  It is promoted as

being able to handle any task you need to perform in the SQL server application,

and I have yet to run into a situation where that wasn't the case. 

Let me know if you have, and we'll post your case in the discussion area

for input from the SSC community.

The entry point into DMO generally starts with the

SQLServer object.  I say

"generally" here because for most of what you will need to get

accomplished with DMO, you will know which instance of SQL server you want to

use.  In the case that you would

want to be able to chose which instance to use, there is an object that is one

level higher than the SQLServer object - the Application object.  This object has a number of useful methods and properties,

not the least of which is the ability to quickly provide a list of available

instances of SQL Server on the network (I have left out the clean-up and error

handling for simplicity):

This example takes a parameter that represents the name of

a listbox control on a form, creates an application object, and executes the

ListAvailableSQLServers method which returns a NameList object. 

The NameList object holds an enumerated list of all of the

"network-visible" instances of SQL Server. 

Looping through this list, each is added to the listbox control, and the

user can then select which server to use from the listbox. 

The usefulness of this function is extended by the next piece of code,

one which gets a listing of the databases for a target server. 

The server name that was picked from the list generated by the previous

function is now passed to the user-defined listDatabases function, along with a

name reference to another listbox control on the form: 

This illustrates two very important aspects of the

SQLServer object:  the log in

methodology, and the Databases collection. 

Here I have logged in using Windows Authentication Mode by setting the

LoginSecure property to TRUE.  If

you want to use the SQL login, set this property to false (or leave the

statement out altogether) and supply the user name and password. 

The Connect method takes three parameters: 

Server name, login, and password.  Here's

an example using the SQL login: 

In both cases, I passed the name of the SQL Server instance

in the variable vServer.  If you opt

for the SQL login, you do not need to set the LoginSecure property, but you will

have to supply a user name and password.  These are not required when using

Windows Authentication. 

Conclusion

This article barely scratches the surface of DMO, but

hopefully has given you enough information to begin exploring it on your own. 

The best recommendation I can give you is to create a test project and

just start trying it out, examining the properties of different objects, looping

through the collections, and testing the available methods - but please don't do

it on a production database server.  If

you have any questions or suggestions for uses of DMO, or have run into any

problems with it (or my characterization of it here), please follow the link to

the discussion area for this article, or the DMO discussion area, and post your

message there.  I'll be writing more in-depth articles on DMO in the near

future, so if you have any ideas you would like to see on the site, please send

me an email and I'll do what I can to accommodate your request.