Microsoft SQL Server Distributed Management Objects

Elspeth Paton
SQL Server Development Team
Microsoft Corporation

August 1996

Abstract

This document is aimed at developers and administrators who have an understanding of Microsoft® SQL Server and Microsoft programming concepts. It provides an introduction to SQL Distributed Management Objects (SQL-DMO). For more information, see the book Programming SQL Distributed Management Objects in the MSDN Library.

Overview

As applications become less centralized and more distributed, managing multiple, distributed databases becomes more difficult. Graphical tools, such as the Microsoft® SQL Server pioneering user interface Enterprise Manager, are one part of the solution, because they allow you to administer multiple distributed servers from a central console. Another critical part of the solution is a robust scripting language for automating repetitive administrative tasks.

IBM's REXX language, Oracle's TCL language, and the UNIX environment PERL scripts, are all unique scripting languages created to address this issue. These scripting languages do have limitations: typically, they are not as robust as standard programming languages such as Microsoft Visual C++®, Microsoft Visual Basic®, COBOL, Fortran, or emerging languages such as Java. The scripting languages lack the class libraries, robust debugger support, and variety of third-party development tools. They also represent yet another language that the developer or administrator must learn.

Microsoft SQL Server is the first database management system to take a different approach. Rather than ship with a proprietary scripting language, Microsoft SQL Server ships with a set of management objects, called SQL Distributed Management Objects (SQL-DMO), that can be invoked from any language that follows the Microsoft Component Object Model (COM) standard and the Microsoft OLE 2.0 standard. SQL Server administrative scripts can be created using the Microsoft Visual Basic programming system, the Microsoft Visual C++ development system, or any other OLE 2.0 Automation controller (see the Appendix for a list of automation controllers).

SQL-DMO defines all aspects of the Microsoft SQL Server database engine and services. SQL-DMO provides over 60 objects and over 1,000 properties and methods. Some example objects include databases, rules, triggers, tables, columns, views, users, groups, publications, and indexes. The object-oriented model is so complete and flexible that the SQL Server management tool, Enterprise Manager, is written using SQL-DMO.

SQL Distributed Management Framework

SQL-DMO is part of a comprehensive framework called SQL Distributed Management Framework (SQL-DMF), which is an integrated framework of objects, services, and components used to manage Microsoft SQL Server (Figure 1). SQL-DMF provides a flexible and scaleable management framework that is adaptable to your specific needs. SQL-DMF reduces the need for user-attended maintenance tasks, such as database backup and alert notification, by providing services that interact directly with SQL Server.

Figure 1. The SQL Distributed Management Framework

All the key components of SQL-DMF are core elements of Microsoft SQL Server. SQL-DMF allows you to manage the SQL Servers in your enterprise by allowing you to define scenarios and corrective actions and to trigger either alerts to notify someone of the problem or tasks to correct the problem.

At the lowest level of this framework, SQL-DMF provides direct access to the SQL Server engine and to services executed from the command prompt using Transact-SQL. The second level of the framework is a set of distributed management objects (SQL-DMO) that provides an object interface to the SQL Server engine and services. The top level of the framework is a graphical administration tool, SQL Enterprise Manager, that provides an interface for managing a multi-server environment. The framework also provides services for replication, scheduling, and creating alerts.

The rest of this document discusses SQL-DMO, the second level of the framework, in detail.

The SQL-DMO Model

Microsoft SQL Server and its components are completely represented by the SQL-DMO model. The main object, SQL Server, is used with other objects that describe the server's elements, such as Login, Database, and Table.

For anyone familiar with Visual Basic, the concept of objects is not new. SQL-DMO, like Visual Basic controls, has properties and methods. Properties represent the object's attributes: methods, the object's functions. Unlike Visual Basic controls, SQL-DMO does not have a visual component or events that are triggered by the mouse or keyboard. SQL-DMO provides objects used for controlling the SQL Server engine and its components. These objects expose appropriate properties and methods for all administration and database definition options.

Figure 2 shows the complete set of objects in the SQL-DMO model and how the objects relate to one another in scope and hierarchy. The Application objects and the SQL Server object treat the application as their parent. Other objects depend on the availability of their parent object. These dependencies are represented in the diagram by indented sublevels. Once the application has declared an object, the application can modify the object's properties and call its methods.

Figure 2. The SQL Distributed Management Object model

SQL-DMO Model

Each object represents a physical element of a SQL Server system. The SQL Server object is the base object from which all other SQL Server objects branch in an object hierarchy. The scope of an object is determined by its parent because an object can be instantiated (connected, added, or made to represent a SQL Server element) only after its parent has been instantiated. For example, the SQL Server object is the Database object's parent, which means that a Database object can be instantiated only after the SQL Server object has been instantiated.

The model also shows two more important aspects of SQL-DMO: Collections and Application Objects.

Collections are a group of objects of the same type that have the same parent. For example, all the Table objects that belong to the pubs database are members of the pubs Tables collection.

The collection is a powerful programming concept. Using a collection, you can refer to a specific object down the object hierarchy without declaring it explicitly. You can iterate through each member of a collection without specifying the name of each object. You can also add or remove objects. Collections make it easy to write an application that iterates through all objects of the same type.

A collection's name is the plural of the type of object it contains. For example, a Tables collection contains Table objects.

Application Objects are a group of objects primarily used to hold information such as query results, permission information, or backup information in a particular form. For example, the Backup object is used to store information about a backup and is passed as a parameter when the Database object's Backup method is called. Application objects can exist in the application prior to the creation of a SQL Server object.

Using SQL-DMO

The following discussion of SQL-DMO code assumes an understanding of Visual Basic.

SQL-DMO is available for 32-bit Visual C++ and the 32-bit version of Visual Basic or Visual Basic for Applications. SQL-DMO is also available for third-party products that are OLE 2.0 Automation controllers (applications that can control other applications through OLE automation). The OLE object information is held in the sqlole65.tlb type library file and is available to OLE automation controllers. On the Visual Basic Tools menu, the References command loads SQL-DMO information from the sqlole65.tlb type library. Visual Basic applications can then use the SQL-DMO objects to manage SQL Server.

All SQL-DMO applications must first create a SQL Server object and then connect to a server. The following Visual Basic code is the minimum template required for a SQL-DMO application.

Dim ServerObject As New SQLOLE.SQLServer 
ServerObject.Connect "ServerName", "sa", "password"

Once connected, the SQL Server object can be used to reference other objects and make changes to the server. The following example changes the "allow updates" configuration value by referring to and updating the Configuration object. The SQL Server object, shown as ServerObject, has a Configuration property that owns a collection called ConfigValues. This line of code uses the ConfigValues collection to specify the "allow updates" member and to change its CurrentValue property.

ServerObject.Configuration.ConfigValues("allow updates").CurrentValue = 1

If you want your application to modify database options, first refer to the specific SQL Server, then to the specific database before modifying the DBOption object's properties. The following Visual Basic code modifies the SelectIntoBulkCopy database option for the pubs database. Note that this statement uses the Database collection to refer to the pubs database.

ServerObject.Databases("pubs").DBOption.SelectIntoBulkCopy = TRUE

Certain SQL-DMO methods, particularly those that enumerate a range of values, return a results set in a QueryResults object. A QueryResults object must be declared first and then set to the return value of the enumeration method. Note that it is not possible to use the New operator with the QueryResults object in the declare statement, because QueryResults objects are returned only as the result of a method that returns a results set. The QueryResults object has methods that allow you to step through each row and column in the results set.

Dim ServerObject As New SQLOLE.SQLServer
Dim Resultsobject As SQLOLE.QueryResults
ServerObject.Connect "servername","sa","password"
Set Resultsobject = ServerObject.EnumPublicationDatabases
Debug.Print Resultsobject.GetColumnString(1,1)

You can create a database and add tables, columns, and other database components by declaring new objects, setting essential properties, and then adding the new object to the appropriate collection. For example, a Column object has three essential properties (Name, Datatype, and Length) that must be specified before the Column object can be added to the Columns collection, as shown in the following code sample. Other optional properties, such as Identity, also must be set before the Column object can be added to the Columns collection. These properties cannot be changed once the object has been added to the collection. Other properties, such as the ID property, are controlled by the system.

Dim NewColobject as New SQLOLE.Column
NewColobject.Name = "OrderCategory"
NewColobject.Datatype = "char"
NewColobject.Length = 2
ServerObject.Databases("SalesDB").Tables("Orders").Columns.Add NewColObject

SQL-DMO error handling is consistent with Visual Basic error handling. You can trap SQL-DMO errors using the Visual Basic On Error Goto statement, as shown in the following procedure.

Sub ConnectToServer
On Error Goto MyErrorHandler
Dim ServerObject As New SQLOLE.SQLServer 
ServerObject.Connect "ServerName", "sa", "password"
Exit Sub
MyErrorHandler:
MsgBox "The connection failed with error number " + str(Err.Number)
End Sub

Errors are handled differently in Visual C++. Each function returns a status value that you can use with a macro to obtain the error number and message.

The ServerGroup and RegisteredServer objects are new in Microsoft SQL Server 6.5. You can use these objects to write multi server applications where instructions are applied to all the servers on your network. The server groups and registered servers information is stored in the Registry of the system running SQL Server. You can use the following code sample to update the statistics for all tables on all the registered servers on your network.

Dim ServerGroupObject As New SQLOLE.ServerGroup
Dim RegisteredServerObject As New SQLOLE.RegisteredServer
Dim SQLServerObject As New SQLOLE.SQLServer
Dim DatabaseObject As New SQLOLE.Database
Dim TableObject As New SQLOLE.Table
For Each ServerGroupObject In ServerGroups
    For Each RegisteredServerObject In ServerGroupObject.RegisteredServers
       SQLServerObject.Connect RegisteredServerObject.Name, "sa", ""
       For Each DatabaseObject In SQLServerObject.Databases
            For Each TableObject In DatabaseObject.Tables
                TableObject.UpdateStatistics
            Next
       Next
       SQLServerObject.Close
    Next
Next

SQL-DMO Sample Solutions

You can use SQL-DMO to develop customized SQL Server administration solutions. This section suggests a range of applications that can be developed by using SQL-DMO to enhance SQL Server.

An Application for Testing Backups

You can create an interface used for automating database consistency checks and running backups (similar to the SQLMaint utility).

You can include customized features such as the ability to start and stop SQL Server. Visual Basic programs can easily check the day and time, which means that the interface could change the type of backup depending on the day of the week. Database dumps could occur on Friday and transaction log dumps on Monday through Thursday. Because the backup process cannot verify the quality of the tape or disk used, it is a good idea to occasionally restore a database from a backup to a test server and check the consistency of the data. This restoration process can be built into the Visual Basic application as a safeguard, as well as to simplify the process in the event of a true media failure.

Figure 3 shows an example Visual Basic interface that backs up, restores, verifies the backup, alerts engineers when there are errors, checks the efficiency of a range of procedures, and e-mails reports.

Figure 3. An example Visual Basic interface

First, you use the Database object's consistency checking methods to check the integrity of the database. Then you use the Dump method to back up the database. The backup details can be stored in the Backup object and used with the DatabaseDump method. You can use the Operator and Alert objects to send e-mail. Restore is handled by the Database object's Load method. You can use Visual Basic tools to time stored procedures called with the Database object's ExecuteWithResults or ExecuteImmediate methods, or you can obtain the efficiency information within the stored procedure and return it to the application.

An Application for Testing Load Balance

You can create a test that helps determine the best strategies for load balancing a SQL Server system for optimum performance.

When you are working with Visual Basic to create a new database system, you may find that you frequently start and stop the SQL Server and rebuild a database from scratch, perhaps with only a few changes. Visual Basic is an ideal platform for writing a test-harness (a set of simultaneous processes derived from a typical real-time system). The test-harness application can initiate simultaneous user applications or use a script to simulate the keystrokes and mouse movements of an end user. This type of application is similar to a SQL script.

The SQL-DMO application contains code for all the objects that make up the database to drop, create, and insert into a collection. These objects include Tables, Indexes, Columns, Users, Views, Triggers, Options, and Configurations. You can test stored procedures using the ExecuteImmediate or ExecuteWithResults method, or you can simulate multiple instances of an application using the Visual Basic Shell command.

An Application for Transferring Data

You can create an application that uses SQL-DMO to build a database and to insert rows using data from another source (such as a mainframe or hardware that generates statistics). The program could retrieve data through a third-party control, or a driver, or a gateway.

You can use Visual Basic to read the data and manage simple insertion of data using the Database object's ExecuteImmediate method to insert the data.

This is an example of where it may be more appropriate to use open database connectivity (ODBC) or DB-Library. The decision to use ODBC or DB-Library depends on whether the application requires only data manipulation tasks. If the application requires mostly data definition tasks, then SQL-DMO is more suitable.

A Customized Interface for Replication

You can create a replication interface that automates the process of setting up replication and that provides the user with additional information about the SQL Server installations.

To create a replication interface, you set up a publisher and a subscriber, and then you create publications. To set up a publisher, you set the SQL Server object's distribution properties and use the RemoteServer object and its SetOptions method to allow remote servers to subscribe. To set up a subscriber, you use the RemoteServer's SetOptions method to subscribe to a remote server. You can add new Publication, Article, and Subscription objects to the relevant collection and then use the RemoteServer's Subscribe method to start replicating data. The RemoteServer object's SubscriberInfo object stores information that relates to the distribution schedule. The interface, created with Visual Basic, can be used to control the creation and management of the replication objects.

A Graphical Interface for Building Databases

You can create an interface that allows a user to build a custom database by clicking buttons and selecting table names from a combo box.

Many small companies need a unique database for their business. Frequently, database functions are similar for different businesses. For example, the appointment database used by a dentist, a psychiatrist, and a financial consultant may differ only in the table and column names. Information about an appointment can be stored in a patient table, a client table, or a customer table, depending on the type of business. A company may prefer to have a standard solution rather than to pay for a customized system. You can use SQL-DMO to create an interface that will build a working database, which you can then modify to meet the needs of specific clients.

To build an interface that creates custom databases, you create an interface that accepts descriptive names for columns and tables for one or more database scenarios, such as an appointment database. The bulk of the code creates and inserts Columns, Tables, Triggers, and Stored procedures using the appropriate collections.

An Application for Ensuring Security

You can create an automated system that creates groups and assigns permissions based on responses to queries made by other applications or data such as organization charts or job title lists.

To create an application that sets up security measures for a database system, you can use Visual Basic file I/O, OLE, or an API to retrieve information from the organization chart or from anther file or application. You can then use this information to create Login, User, or Group objects and insert them into the Logins, Users, or Groups collections. Permissions are then assigned by using the Grant method for Table, Database, or StoredProcedure objects.

A Graphical Interface for a Database's Table Relationships

You can create an interface that draws a graphical representation of a database, its tables, and relationships.

The following example interface, Figure 4, shows the relationships between tables in a database. You can iterate through all the collections and objects in the Database object and then use Visual Basic controls to display the information graphically.

Figure 4. Table relationships

An Application for Testing Index Performance

You can create an application that systematically drops and rebuilds indexes using different columns and orders and that measures and records the performance of each new index format by using a test-harness application (a set of simultaneous processes derived from a typical real-time system).

Before programming, you must identify the type and frequency of procedures requested by clients and create a representative sample of stored procedures. You can then write an application that adds and removes Index objects based on different permutations of a table's columns. You can identify the table's columns by iterating through the Columns collection. Then, the application can execute the representative sample of stored procedures using the ExecuteImmediate method and measure the execution time for each index. You can use this tool on many database systems to identify the combination of indexes that provides the best results.

A Multi-Server Microsoft Excel Macro

The following example application demonstrates how SQL-DMO can be used to create a multi-server Backup macro in Microsoft Excel. The following section of code backs up all the databases on a network to a central file share. Providing the server has been registered, the application connects to and backs up all the databases. Then, it calculates the percentage of space used and displays the results in a Microsoft Excel spreadsheet. You can extend this simple example to perform complex backup strategies and chart the use of resources.

Because this application backs up to a network share, you must give the Microsoft SQL Server service a domain system account that has write access to the file share on every server.

Sub ListRegisteredServerandGroups()
'Initialize the row count variable, x.
x = 1
'Create objects.
Dim OServerGroup As Object
Dim ORegisteredServer As Object
Dim OBackup As Object
Dim ODatabase As Object
Set OBackup = CreateObject("sqlole.backup")
Set ODatabase = CreateObject("sqlole.database")
Set OSQLServer = CreateObject("sqlole.sqlserver")
Set OServerGroup = CreateObject("sqlole.servergroup")
Set ORegisteredServer = CreateObject("sqlole.registeredserver")
'Enable error handling.
On Error GoTo errorhandle
'Print column headings on the worksheet and increment the row count.
Worksheets(1).Cells(1, 1).Value = "Group"
Worksheets(1).Cells(1, 2).Value = "Server"
Worksheets(1).Cells(1, 3).Value = "Database"
Worksheets(1).Cells(1, 4).Value = "Backup File"
Worksheets(1).Cells(1, 5).Value = "Space Used"
Worksheets(1).Cells(1, 6).Value = "% Space Used"
x = x + 1
'Iterate through server groups and registered and print names.
For Each OServerGroup In ServerGroups
    Worksheets(1).Cells(x, 1).Value = OServerGroup.Name
    x = x + 1
    For Each ORegisteredServer In OServerGroup.RegisteredServers
        Worksheets(1).Cells(x, 2).Value = ORegisteredServer.Name
        x = x + 1
'Connect to the registered server.
        OSQLServer.Connect ORegisteredServer.Name, "sa", ""
'Back up every database on the server to a central network share.
        For Each ODatabase In OSQLServer.Databases
            OBackup.DiskDevices = "'\\central\public\" + OSQLServer.Name +_
            ODatabase.Name + ".dmp'"
            ODatabase.Dump OBackup
'Print database name, dump file name, space used and percentage space used.
            Worksheets(1).Cells(x, 3).Value = ODatabase.Name
            Worksheets(1).Cells(x, 4).Value = OSQLServer.Name + _
            ODatabase.Name + ".dmp"
            SpaceUsed = (ODatabase.Size - ODatabase.SpaceAvailableInMB)
            Worksheets(1).Cells(x, 5).Value = SpaceUsed
            Worksheets(1).Cells(x, 6).Value = SpaceUsed / ODatabase.Size * _
            100
            x = x + 1
        Next
'Close the SQLServer object.
        OSQLServer.Close
    Next
Next
Exit Sub
'Handle errors by displaying error message in a message box and terminating.
errorhandle:
MsgBox Error()
OSQLServer.DisConnect
End Sub

The results are generated on the Microsoft Excel worksheet as shown in Figure 5:

Figure 5. Example of a multi-server Microsoft Excel Macro

Future Direction

Microsoft continues to enhance SQL-DMO as a development tool. SQL-DMO will continue to support Microsoft SQL Server features. For example, when new SQL Server features are added to replication, new SQL-DMO objects or methods will be created to represent these new features.

SQL-DMO will also continue to support the Microsoft COM. The COM standard has recently introduced the Distributed COM component, which supports simultaneous calls from local or remote clients. Future releases of SQL-DMO will include Distributed COM components.

Summary

The Microsoft SQL Distributed Management Framework is a comprehensive solution to your database needs in today's distributed, client-server environments. The framework provides SQL Enterprise Manager, a graphical user interface for SQL Server, and SQL-DMO, a set of objects that you can call from any standard language that supports OLE 2.0 Automation.

SQL-DMO enables developers to develop, quickly and easily, a range of useful, customized SQL Server administration tools and interfaces for a multi-server environment. The unique power and flexibility of SQL-DMO can be applied to the full range of database management and database definition tasks such as production, performance tuning, and graphical representations of databases. SQL-DMO enables robust administration of complex distributed server environments.

No other database vendor provides a tool like SQL-DMO that allows you to quickly develop administration applications that can control multiple database servers.

Appendix: Third Party OLE 2.0 Automation Controllers

Third-party companies provide OLE 2.0 Automation controllers that can be used to control OLE 2.0 Automation servers such as SQL-DMO. The following is a list of all known third-party products that are OLE 2.0 Automation controllers.

Third-party OLE 2.0 Automation controllers