Unleash the power and flexibility of SQL Server's DMO functions with SQL Database Monitor
VB Solutions is about using Visual Basic to build a variety of solutions to
specific business problems. This column doesn't teach you how to write VB, but
how to use VB as a tool to provide quick, easy-to-implement solutions that you
can use right away. Last month, I presented a network administration utility
that used some Win32 APIs to quickly check for NT security violations. This
month, I'll show you how SQL Server's Distributed Management Objects (DMO) let
you use VB to create custom management tools for SQL Server.
Because you can control SQL Server from VB, you can easily develop custom
SQL Server management functions. And if you use DMO from VB, you can easily
build tools that streamline some functions that SQL Server's graphical
Administration tool provides. For instance, viewing the storage requirements for
all the tables in a SQL Server database requires you to traverse several SQL
Enterprise Manager screens or write SQL programs in SQL Server's Transact SQL
language, which has limited graphic elements. SQL Server's DMO opens up SQL
Server to a custom VB program that can consolidate SQL Server information and
present it through VB's built-in graphical controls.
Screen 1 previews this month's utility, the SQL Database Monitor. The SQL Database Monitor helps you quickly see the size of all the tables in a SQL
Server database by presenting the tables and their respective sizes in a graph.
In addition to table size, Database Monitor offers a quick view of the number of
rows in each table and other information such as the table creation date.
To use the SQL Database Monitor, you first must select the name of the SQL
Server you want to connect to and then click Connect. The SQL Database Monitor
will present a login prompt for your SQL Server login ID and password. After you
successfully log on to SQL Server, the SQL Database Monitor lists all the
databases you have access rights to. Double-clicking a database name causes the
SQL Database Monitor to graph all the user-created tables in the selected
database. The tabbed dialog at the bottom of the screen lets you choose from a
graph showing the size in KB of each table, a graph showing the number of rows
for each table, and a list containing more detailed table information.
To get programmatic access to this kind of database information from
non-SQL Server platforms (if it is available at all), you need to master
low-level networking and system interfaces. Luckily, SQL Server is built on an
Object Linking and Embedding (OLE) foundation, DMO, that makes access to SQL
Server's database management functions easy. For the background we'll need to
understand the detailed workings of the SQL Database Monitor, let's get a quick
overview of SQL Server's DMO.
Overview of DMO
DMO is a set of 32-bit OLE objects that enable program access to SQL
Server's management functions. Microsoft introduced SQL Server's DMO in SQL
Server 6.0 and enhanced it in SQL Server 6.5. Microsoft intended SQL Server's
Distributed Management Framework to facilitate the use of SQL Server in a
distributed environment. The Distributed Management Framework extends the power
of SQL Server's management function to all the clients in the network. Because
SQL Server implements the DMO as OLE objects, you can use them only from a
32-bit client application. Although this month's example SQL Database Monitor
program is written in VB, SQL Server's DMO functions are not limited to VB. Any
32-bit OLE-compliant application, including VB, Visual C++, and Delphi, can use
them.
DMO is a part of the larger Distributed Management Framework that Microsoft
provides for SQL Server. Figure 1 shows you the three-tiered architecture of SQL
Server's Distributed Management Framework. The SQL Server Engine and the SQL
Executive functions make up the foundation, providing the core database and
management services of SQL Server. The DMO is the middle level and bridges the
user interface and the core SQL Server components. SQL Server's SQL Enterprise
Manager and the OLE interface that is open to your VB applications use the DMO.
With more than 60 different objects and 1000 properties and methods, SQL
Server's DMO gives you far-reaching access to SQL Server's capabilities.
Microsoft organized the DMO into the hierarchical order shown in Figure 2.
The application object is at the top of the DMO organization. The SQL Server
objects are under the application object. Each SQL Server object contains one or
more database objects, and each database object contains the DMO table objects
and other types of SQL Server objects such as triggers, views, and stored
procedures. Each level of the DMO hierarchy consists of objects that relate to
the level. For instance, the SQL Server level consists of device, login, and
database objects, and the table level includes column and trigger objects. This
hierarchy follows the same basic organization of SQL Server's Enterprise
Manager.
Adding DMO Support to VB
Before you can use the DMO in VB's development environment, you must install
it. SQL Server copies the files that provide the basic support for DMO to your
client system when you first install the SQL Server 32-bit client. However, you
need to install the files in VB's development environment to use them from VB.
To add DMO support to VB, you need to reference the SQL DMO type library from
VB. Select References from VB's Tools menu to display the References dialog you
see in Screen 2.
Scroll through the References dialog until you see the Microsoft SQLOLE
Object Library. Check this list entry to add the sqlole32.tlb file to VB's IDE.
(The SQL Server DMO functions are in the sqlole32.dll.) When you add OLE custom
controls (OCXs) and ActiveX controls to VB, you see an icon representing the
control in VB's toolbox. Unlike ActiveX controls or OCXs, SQL Server does not
add visual objects to VB's IDE, and you will not see an additional icon in VB's
toolbox. To work with the SQL OLE object, you must use VB's Object Browser to
see the added SQL DMO functions.
You're now ready to implement DMO. See "Using SQL Server's DMO,"
for the steps you need to follow.