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
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):
listServers(vControl As Object)
Dim oApp As
Dim oNames As SQLDMO.NameList
Set oApp = New
Set oNames =
For Each oName In
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:
listDatabases(vServer As String, vControl As Object)
oServer As SQLDMO.SQLServer
oServer = New SQLDMO.SQLServer
Each oDatabase In oServer.Databases
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:
vServer, "sa", "theSApassword"
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
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.