Microsoft Corporation
May 1996
With an increasing number of organizations adopting a decentralized data strategy implemented on client-server systems, the issues around distributed data management and database administration are becoming critical to the success of management information systems (MIS) departments.
This paper presents the new OLE-based management framework provided by Microsoft® SQL Server™ client-server database management system version 6.0 and describes how it can be used in conjunction with Microsoft Visual Basic® for Applications programming system to build solutions for enterprise-wide database administration.
We begin with some acronym definitions and discuss the new administration capabilities available in Microsoft SQL Server 6.0. We then examine the details of the object model for Microsoft SQL Server 6.0 and discuss how Visual Basic applications interact with OLE Automation objects in general and with Microsoft SQL Server objects in particular.
Microsoft SQL-DMO requires a 32-bit OLE Automation client and can, therefore, only be used by 32-bit Visual Basic environments such as 32-bit Microsoft Excel or 32-bit Visual Basic 4.0. It cannot be used with Visual Basic 3.0 or the 16-bit version of Visual Basic 4.0. Microsoft SQL-DMO can also be used by non–Visual Basic 32-bit automation clients such as 32-bit Microsoft Visual C++®, but in this paper we will focus on Visual Basic.
One of the major design goals for Microsoft SQL Server version 6.0 was to make the job of database administration easier. To this end, several new components were introduced to the Microsoft SQL Server architecture
A task scheduling component allows the definition of tasks along with the rules for their execution. Tasks can be defined to be Transact-Microsoft SQL scripts or Microsoft Windows NT command files. Microsoft SQL Server version 6.0 additionally defines some internal tasks for the purposes of replication (see below). Tasks can be defined as on-demand, one-time, or recurring tasks. Success or failure of a particular task execution can be reported to the Windows NT Event Log and/or to database administrators via e-mail. Retry intervals and number of retry attempts can be set for each task. For example, a task could be created to run a script that updates statistics every night at 10 p.m. on all the tables of the pubs database.
An alerting component watches for Microsoft SQL Server events from the Windows NT Event Log and can respond to these events by notifying an administrator and/or invoking a predefined task. An alert in Microsoft SQL Server version 6.0, therefore, maps to an event in the Windows NT event log, which has a source of "SQL Server." In order for an action (that is, notification or task execution) to occur in response to an event, a Microsoft SQL Server alert has to be defined that matches the data for that event. Alerts are generated not only automatically by Microsoft SQL Server, but also when users invoke the xp_logevent procedure. Alerts consist of the following:
For example, a Microsoft SQL Server version 6.0 alert might be defined to send mail to an administrator when the state of a particular Microsoft SQL Server changes from running to paused or stopped.
Task scheduling and alerts in Microsoft SQL Server version 6.0 are designed to work closely with each other. A scheduled task can generate an alert as a result of its successful or unsuccessful completion, and an alert can trigger an on-demand task.
Task and alert information is stored in a new Microsoft SQL Server database called Microsoft database (MSDB). MSDB is installed automatically during Microsoft SQL Server setup, and it contains (among others) the systasks, sysalerts, syshistory and sysoperators tables. The syshistory table is used to maintain an audit trail of task executions. Administrator information from the sysoperators table is associated with alerts and tasks and is used to determine the notification process.
Task scheduling and alerting are both implemented within the Microsoft SQL Scheduler service that runs as a Windows NT Service in conjunction with Microsoft SQL Server. Microsoft SQL Scheduler can be started and stopped by the Microsoft SQL Service Manager interface and must be running for scheduling/alerting functionality. Microsoft SQL Scheduler is registered with the Windows NT Event Log as the event handler for Microsoft SQL Server events, and it does the matching of Windows NT events to Microsoft SQL alerts.
During its service startup, Microsoft SQL Scheduler connects by DB-LIB to its associated Microsoft SQL Server and loads the list of tasks to be executed. Tasks are invoked by threads spawned by Microsoft SQL Scheduler, which then records completion status in the Windows NT Event log, the syshistory table in MSDB, and performs the notification.
The task and alert processing model are illustrated in Figure 1.
Figure 1. Microsoft SQL Server event/response model linking task scheduling and alerting
Task scheduling and alerting are also designed to operate in a distributed environment in which several Microsoft SQL Servers cooperate with each other and need to be managed centrally, sharing a common set of tasks, alerts and administrators. Alerts that are not defined locally on a Microsoft SQL Server can be optionally forwarded to another server for processing. Tasks can be executed against remote Microsoft SQL servers and the task scheduler can get its list of tasks from a remote server.
Other functionality and feature enhancements provided by Microsoft SQL Server version 6.0 in the area of database administration are listed below:
For more details on the Microsoft SQL Server version 6.0 administration features and tools, please refer to session SQ205.
Essential to the success of a distributed data strategy is the timely and effective synchronization of shared data between multiple databases. Microsoft SQL Server version 6.0 provides two mechanisms to achieve this: two-phased commit and replication.
Transactions that employ a two-phased commit guarantee that either all or none of the participating servers apply the changes in that transaction to their copies of the data. If all participating servers cannot complete the transaction, it is rolled back and not applied to any server. This approach can be used to synchronize data across multiple servers by implementing two-phased commit on transactions that update shared data. Microsoft SQL Server version 6.0 provides a programmatic interface in DB-LIBRARY to build two-phased commit solutions. However, the two-phased commit approach to synchronization can be expensive in terms of infrastructure, difficult to administer, and usually becomes unreliable as the number of participating servers and the distance between them increases. More importantly, unless all servers need to have the latest copy of the shared data at all times, the rigid all-or-nothing semantics of two-phased commit can be overkill in terms of the business needs of the data.
For a large set of business problems, a "real-enough time" data synchronization is sufficient and more viable from an implementation and administration point of view. In these scenarios, a master copy of the shared data is updated at one location and then subsequently forwarded to other servers. Microsoft SQL Server version 6.0 provides built in replication capabilities to address this form of data synchronization.
Using Microsoft SQL Server version 6.0 replication, read-only copies of data from a publishing server can be automatically distributed to one or more subscribing servers. The data replication can be performed on a continuous basis or at specific time intervals. A subscriber can subscribe to all of the replicated data or to a particular filtered subset of the data. There is no predefined limit on the number of publishing and subscribing servers, and a single server can perform both roles.
The unit of replication in Microsoft SQL Server version 6.0 is called a publication. A publication is defined as a collection of articles and its purpose is to serve as a mechanism to group and distribute publishable data. Articles map to data from a single user table in the publishing database. An article could represent the entire table or a horizontal and/or vertical fragment of the table. A subscriber can subscribe to one or more articles from a publication. Tables that are published as articles are required to have a primary key. A publication can be restricted to a specified set of subscribers or can be designated as unrestricted allowing any subscriber to access it.
There are some restrictions on the types of data that can be published by Microsoft SQL Server version 6.0. Specifically, system tables in the master database and text and image columns cannot be published. Columns defined with the identity property will be replicated in terms of the column values, but the characteristics of the identity property will not be transferred to the subscriber. Timestamp columns from the publisher will be converted to varbinary columns.
Replication of a publication can be performed by means of a scheduled refresh of the published data, or on a transaction-log basis. In the latter case, after an initial synchronization of data between publisher and subscriber, the transaction log of the published database is monitored for changes to tables that have been marked for replication. These changes are copied to a distribution database (actually to certain tables in the MSDB database) from where they are distributed and applied to the subscribing servers. Working off the transaction log of the published database means that user access to the published database is not excluded by replication and that the order of transactions is maintained on the subscribing servers.
The process of distributing changes to subscribers can be customized to suit different configurations:
The components involved in the replication process are illustrated in the following diagram:
Figure 2. SQL Server Replication processes
The sync process is a scheduled task for each publication that serves the purpose of getting new subscribers a snapshot of the published data. This process generates the table creation script and optionally the script for primary key and index creation, if the subscriber wants to duplicate these. It also creates a data file with the published data and initiates the transaction queuing process.
The replication log reader is a multithreaded executable with two threads for each published database. It reads the transaction log of the published database and moves marked transactions to the distribution database and as part of this process, reconstructs the Microsoft SQL commands to be applied on the subscriber.
The distribution process is scheduled via the SQL Scheduler and distributes replicated data to subscribing servers. It is a multithreaded Windows NT service with one thread for each subscribing database. The distribution process uses ODBC to connect to subscribers (ODBC must, therefore, be installed on the distribution server for replication to work properly).
To track and tune the data replication process, Microsoft SQL Server includes the following Perfmon counters designed especially for replication:
In addition to providing the specific capabilities discussed above, Microsoft SQL Server version 6.0 includes a new OLE Automation interface (Microsoft SQL-DMO) for programmatic access to its administrative functions.
OLE Automation is a standard mechanism that allows one application (an Automation Controller) to programmatically access the capabilities of another application (an Automation Server). The Automation Server defines a set of objects that it exposes. These objects have properties that describe their state, and methods that can be used to query or modify their state. A developer can then use the published list of objects, methods and properties from an Automation Server and build an application that creates and manipulates its objects. Microsoft SQL-DMO provides a list of objects, properties and methods that comply with this mechanism and allow the building of scripts and applications to manage Microsoft SQL Server. The organization of the objects, methods and properties in Microsoft SQL-DMO or any other Automation Server is known as its object model.
By implementing OLE Automation, Microsoft SQL-DMO also provides an intuitive way of thinking about Microsoft SQL Server administration. The object model for Microsoft SQL-DMO defines all the objects in the Microsoft SQL Server administration environment and formalizes their characteristics and interaction in terms of properties and methods. This is a valuable learning aid for novice Microsoft SQL Server administrators.
One of the goals of Microsoft SQL-DMO was to provide a simpler and more consistent management interface than is available in Microsoft SQL Server 4.2. Microsoft SQL-DMO exposes functionality that is currently implemented by means of:
Microsoft SQL-DMO aims to alleviate the difficulties associated with having to use these different dialects and having to remember the list of parameters and options that often accompany management commands. Wherever possible, the same function name is used to accomplish similar functionality on different objects and additional functions are provided where the number of permutations to a command might have made it hard to remember and use. For example, creation of new objects is done by means of the .ADD method whereas currently, the stored procedure sp_addtype is used to create a user-defined datatype and the T-SQL statement CREATE DEFAULT is used to create a default object. Similarly, DBCC functions that operate on a given database object have been exposed as functions at the object level such as Table.Checktable and Database.Checkcatalog, as opposed to having to qualify a single DBCC command with object names.
Microsoft SQL-DMO also makes programmatic access to Microsoft SQL Server functions easier. This is an important factor in enterprise-wide data administration where a command line interface or graphical user interface (GUI) applets are not sufficient to centrally manage a large number of servers. While it is certainly possible today to create T-SQL scripts and extended stored procedures to remotely manage servers, the available functionality and development environment is limited. By using OLE Automation as the mechanism to provide programmatic access to Microsoft SQL Server, Microsoft SQL-DMO opens up the possibilities for high-level solutions for data administration. OLE-enabled environments such as Visual Basic provide a framework for browsing and manipulating objects and quickly building programmatic solutions that would typically take much longer to create in the current Microsoft SQL Server environment. Using a high-level development environment such as Visual Basic also has the added benefit of providing access to a rich set of graphical controls for solutions that need a GUI. It also eases access to other Visual Basic/OLE environments such as Microsoft Excel that can be used for value-added postprocessing of server information such as the creation of charts and reports that provide feedback on overall database usage and performance.
Microsoft SQL-DMO objects are organized into a hierarchy in which one object can contain other objects.
In cases where there is a one-many ownership relationship between objects, a collection object is used. For example a SQL server can have one or more databases, but a database can belong to only one SQL server, therefore each Microsoft SQL Server object has a collection of database objects as one of its properties. In almost every case where a collection is used, there are actually two different object types defined in the object model, one for the individual object and one for the collection. Both object types have distinct methods and properties, but for sake of visual simplicity, they are shown as one in the object model diagrams.
Some Microsoft SQL-DMO objects also have lists associated with them. Lists are used to represent many-many relationships between objects without a connotation of ownership. For example, a database could reside physically on one or more devices and a device can be used to store one or more databases. Therefore, a device object has a ListDatabases method defined for it that returns a list of databases on that device and similarly, the database object has a ListDevices method.
The top level of the object model is illustrated in Figure 3. Objects in the clear boxes represent individual objects only and objects in the shaded boxes represent both objects and collections.
As is standard with all OLE Automation Servers, at the highest level in the hierarchy is the Application object. This object represents the Automation Controller application within whose context the remainder of the objects are instantiated. The Microsoft SQL Server, Transfer, Backup, Permission, QueryResults, HistoryFilter and Names objects are the first level objects beneath Application.
Of the top-level objects, the main object of interest to us is the Microsoft SQL Server object (and collection). The Microsoft SQL Server object is the object that serves as the main 'entry-point' for access to a particular server. Other than the objects shown in Figure 3, all Microsoft SQL-DMO objects are contained within a Microsoft SQL Server object. The other top-level objects serve as helper objects in Microsoft SQL-DMO and are not contained within a particular Microsoft SQL Server.
Figure 3. Microsoft SQL-DMO Object Model—top-level objects
Figures 4 and 5 illustrate a drill-down of the Microsoft SQL Server object. A Microsoft SQL Server object is associated with a single Microsoft SQL Server and represents a single connection to that server. The main properties of a Microsoft SQL Server object are the collections that describe the databases, devices, logins, alerts, and so on. It also has properties to represent the server name, host name, process ID, language, scheduler, registry, and so on. The methods of a Microsoft SQL Server object include Connect, Shutdown, ExecuteWithResults, ReadErrorLog, BeginTransaction, and so on.
A Backup object contains information used to perform a backup or restore of a Database or TransactionLog. Its properties include the table name in cases of table dumps, device information on disk devices, dump devices, floppy devices, and tape devices, and expiration information on dumps. In order to perform a backup, a backup object must be created, its properties set to the appropriate values, and then it must be passed as the argument to the Dump or Load method of the appropriate Database or TransactionLog object.
A QueryResults object contains the result set from an executed Transact-SQL query. All list methods that start with Enum return a QueryResults object. The ExecuteWithResults method of the Microsoft SQL Server and Database objects also return a QueryResults object. The properties and methods of the QueryResults object allow developers to extract rows and columns from the results for manipulation in their programs.
Figure 4. Microsoft SQL Server Object (part 1)
Figure 5. Microsoft SQL Server Object (part 2)
Figures 6 and 7 show the details of the database object.
Figure 6. Database Object (part 1)
Figure 7. Database Object (part 2)
The complete details of all the objects and their associated properties and methods are provided in a Help file included in Microsoft SQL Server (SQLDMO.HLP).
Visual Basic for Applications provides support for building solutions that make use of objects exposed by other applications through OLE Automation. In this section we will look at how Visual Basic can be used to create Microsoft SQL-DMO applications.
Before getting started with Visual Basic, the SQL-DMO type library must be referenced. In Microsoft Excel, this can be done through the References item in the Tools menu with a module as the active sheet. When the references dialog box is up, select the Browse button and look for the file SQLOLE32.TLB in the SQL95\BINN directory (where SQL95 is the directory in which Microsoft SQL Server is installed). This allows Visual Basic to create and manipulate the Microsoft SQL-DMO objects.
Once the type library has been referenced, you can use the Object Browser to view all the objects exposed by Microsoft SQL-DMO and their associated methods and properties. Select the Object Browser item from the View menu to bring up the Object Browser. A snapshot of the browser is shown in Figure 8. Make sure that SQLOLE is the selected Library. To inspect the methods/properties of an object, simply click on the object type in the Objects/Modules list and its list of methods and properties will appear. As can be seen in the figure, there are separate entries in the objects/modules list for individual objects and collections. For example, the column object will have the properties/methods for an individual column such as Name, Datatype, AllowNulls. The columns object will have the properties/methods to add a column to the collection and list the columns in the collection.
Figure 8. SQL-DMO viewed through the Object Browser
Objects can be created in Visual Basic by either directly dimensioning a variable to be of a given object type with the New operator, or by using the CreateObject function to create an object of a given type and then assigning it to an object variable with the Set statement. Both of the following statements can be used to create a Microsoft SQL Server object.
Dim MyServer as New SQLServer
Set MyServer = CreateObject("Sqlole.SQLServer")
To access properties of an object, use the dot notation. The following statement can be used to print the machine name on which Microsoft SQL Server is installed. The statement after it is used to alter the network packet size of the server.
Print Myserver.HostName
MyServer.NetPacketSize = 256
To invoke a method on an object, the dot notation is used again. The following statement connects to a Microsoft SQL Server. The connect method takes three arguments, the name of the Microsoft SQL Server, a login ID, and the password.
MyServer.Connect "ashishk", "sa", ""
For methods that take a large number of arguments all of which are not always required, named arguments can be used to make the code easy to write and read. For example, the SendMail method on a Microsoft SQL Server object is used to invoke the extended stored procedure to send mail. This method has four optional string arguments: To, CC, Subject, and Message. To send mail to John Smith without copying any other recipients and without using a subject line, named arguments can be used as follows:
MyServer.SendMail TO :="John Smith" Message:="This is a test"
The dot notation is also used to navigate through the object hierarchy. The following statement updates the statistics for the auth_PK index on the authors table in the pubs database of the Microsoft SQL Server that MyServer is connected to.
SQLServers("MyServer").Databases("pubs").Tables("authors").Indexes("auth_PK").UpdateStatistics
It is not necessary to state the object type of each object in the hierarchy. The ! notation allows a shorter way of navigating the hierarchy.
MyServer!pubs!authors!auth_PK.UpdateStatistics
For cases where you will be doing a number of things with an object that is deep down in the hierarchy, it is more convenient to assign it to a variable.
Dim auth_PK as Index
Set auth_PK = MyServer!pubs!authors!auth_PK
auth_PK.UpdateStatistics
space = auth_PK.SpaceUsed 'read spaceused into variable
If space > threshold 'rebuild index if space use exceeds threshold
auth_PK.Rebuild
In most cases, the way to create and delete database objects in the Microsoft SQL Server environment is by adding new objects to a given collection or removing an existing object from its collection. For example, there is no destroy operation on a database object; the way to drop a database is by removing it from the databases collection of its parent Microsoft SQL Server object.
MyServer.Databases("pubs").Remove
To add an object to a collection, create the object, set its properties and pass it as an argument to the add method on its collection object
Dim PKIndex as New Index
PKIndex.Name="auth_PK"
PKIndex.COl1 = "ID"
PKIndex.Type=CLUSTERED
MyServer!pubs!authors!Indexes.Add PKIndex
To iterate through the objects in a collection, the For Each construct should be used:
Dim x as Index
For Each x in MyServer!pubs!authors.Indexes
Print x.Name
Next x
The following code snippet illustrates a Visual Basic macro that takes a server and database as input, dumps the database, updates the statistics on all the tables in that database and e-mails an administrator when the task is done.
Sub NightlyAdmin(MyServer As SQLServer, MyDB As Database)
Dim MyTable As SQLServer.Table
MyServer!MyDB.Dump TapeBackup1 ' Dump DB using TapeBackup Object
For Each MyTable In MyServer!MyDB.Tables
MyTable.UpdateStatistics ' update stats
Next MyTable
MyServer.SendEMail TO:="admin"
Subject:="Nightly Backup Successful"
End Sub
Microsoft SQL Server version 6.0 provides several new capabilities to support server administration in a distributed data environment. New components include task scheduling, event alerting, and replication. Microsoft SQL Server 6.0 also provides programmatic access to these and previously existing functions by means of an OLE Automation interface. This allows the creation of custom scripts and GUI-enabled applications that can ease the management of a large number of servers. Microsoft SQL Enterprise Workbench is a utility included with Microsoft SQL Server 6.0 that is based on Microsoft SQL-DMO and provides a comprehensive GUI environment for the interactive administration of SQL Servers. Put together, the capabilities provided by the Microsoft SQL Executive, Microsoft SQL-DMO and Enterprise Workbench form a distributed management framework for the enterprise wide administration of Microsoft SQL Servers.