Management APIs

Earlier in this chapter you saw how to register a server in SQL Server Management Studio. What if you have to register hundreds of servers? You can certainly do it through the user interface or create an XML file by hand and import it into the user interface, but that is still a lot of work. You are likely to run into a similar situation when managing a large number of servers, databases, or database objects. For situations like this, you need the ability to automate common management tasks.

SQL Server 6.5 first introduced a COM-based application programming interface (API) called SQL-DMO, which allowed automating management tasks using languages such as VBScript, C++, and Visual Basic. This API was further extended in SQL Server 7.

SQL Server 2000 continued supporting SQL-DMO to automate all the tasks that you can do by using SQL Server Enterprise Manager. SQL Server 2005 still ships SQL-DMO COM libraries, but it is not upgraded to support newer features, such as Service Broker, Database Mail, Database Mirroring, and so on.

SQL-DMO is now being superseded by a .NET-based object library called SQL Server Management Objects (SMO). In addition to SMO, SQL Server 2005 introduces other .NET object libraries to automate replication tasks (Replication Management Objects [RMO]) and to automate Analysis Services management tasks (Analysis Management Objects [AMO]). This chapter provides details on SMO and WMI support in SQL Server 2005. Refer to Chapter 4 for an overview of WMI.

SQL Server Management Objects (SMO)

The SMO API's primary goal is to provide a powerful yet easy-to-use programming interface to automate administration, deployment, and maintenance of SQL Server. In addition to providing maximum coverage of SQL Server 2005 features, the SMO API contains several other improvements over SQL-DMO. SMO is designed from the ground up so that the API can perform better, scale well, and increases flexibility. The SQL Server Management Studio internally uses the SMO API to interact with SQL Server. Therefore, anything you can do by using Management Studio, you can automate by using SMO. You can use SMO from any .NET programming language, such as Visual Basic .NET or C#. In addition, SQL Server 2005 provides a COM wrapper over this .NET library so that you can use SMO from scripting and unmanaged languages to automate management tasks.

Tip

You can use SMO to manage SQL Server versions 2000 and 2005. SMO does not support databases with the compatibility level set to 60 or 65.

The following are some of the improvements introduced in the SMO API:

• Delayed instantiation If you access any of the instance class objects, SQL-DMO retrieves all the properties up front. On one hand, this might reduce the roundtrips to the server, but on the other hand, it requires more memory and also does not scale well. SMO gives developers the option to determine whether they want to retrieve objects and properties as needed (delayed instantiation), prefetch entire collections, or retrieve objects by using a set of predefined properties. In other words, SMO gives developers a fine level of control over how data is retrieved from the server.

• Cached object model Unlike SQL-DMO, SMO does not propagate object changes to the server immediately. It caches the changes until you decide to apply or discard the changes, and hence it reduces the roundtrips to the server by sending updates in batches.

• WMI provider If using WMI API seems complex to you, SMO has an answer for you. SMO provides a simple programming interface for performing tasks that you can do by using WMI API. Things that you can do using SQL Server Configuration Manager, such as managing service accounts, network protocols, and so on can now be easily done by using the SMO API.

• COM wrappers The fact that SMO is a .NET-based object library does not mean you can use it only from .NET applications. SMO ships with a COM wrapper that can be used from scripting languages such as VBScript and JScript, and from other unmanaged programming languages, such as Visual Basic and Visual C++.

• Capture mode Earlier in this chapter you learned that all the management dialogs in SQL Server Management Studio are scriptable. That means that a T-SQL script can be generated for the changes you perform in the dialog. The management dialogs provide this functionality by using the capture mode feature in SMO, which lets you record or capture changes made to SMO objects and generate a T-SQL script for those changes.

• Exception hierarchy SMO exceptions are almost always chained, providing context at every level. This means that if there is an error, you can find out about the entire error chain.

• Releasable state The SQL-DMO object model consists of a static object named Application as the root, and this object is a starting point for accessing SQL Server objects. SQL-DMO does not allow releasing this object. With SMO, the Application object is gone; the new top-level class in the SMO object hierarchy is Server, which can be released (set to null) at any time, freeing the memory.

• XPath-style syntax Let's say you would like to find out the total number of rows in a table. You can first get the Server object, then the Database object, and then the Table object, and then you can access the RowCount properties. SMO provides an alternative, simpler, and more efficient syntax to do this. This syntax, which looks like folder hierarchy syntax or like the unique resource name (URN) syntax, allows you to specify the path of the object you want to access.

• Advanced scripting support With SQL-DMO, scripting was deeply embedded in SQL-DMO classes, each having a Script method. SMO continues to support this approach for backward compatibility, but it also introduces a standalone object, a class called Scripter that provides advanced scripting support such as discovering object dependencies.

The SMO API provides three types of classes:

• Instance classes These classes provide access to all SQL Server objects and their properties. Examples of instance classes include Server, Database, Table, View, and StoredProcedure.

• Utility classes These classes can be used for management and administration tasks such as backup/restore, jobs, Database Mail, and Service Broker management.

• Scripter class This class provides advanced scripting functionality.

Note

Because SMO is a .NET-based object library, you need the Microsoft .NET Framework version 2.0 or higher installed on the machine in order for your SMO application to work. SQL Server 2005 provides an MSI setup for SMO, SQL-DMO, and SMO COM wrappers that you can redistribute with your application.

Here is an example of using SMO in VBScript:

Dim smoSvr Dim svrName Dim smoDB svrName = "DDGXP\YUKON" Set smoSvr = CreateObject("Microsoft.SqlServer.Management.Smo.Server") smoSvr.ConnectionContext.ServerInstance = svrName For Each smoDB in smoSvr.Databases     WScript.Echo smoDB.Name Next Set smoSvr = Nothing

This script lists all the databases on a server. You can update the svrName variable with your SQL Server 2005 instance name, save the script, and run it. The script displays a message box that lists each database on the specified server.

Here is another example of using SMO in VBScript. This VBScript creates a new database on the specified server.

Dim smoSvr Dim smoDB Dim svrName Dim dbName svrName = "DDGXP\YUKON" dbName = "TestDatabase" Set smoSvr = CreateObject("Microsoft.SqlServer.Management.Smo.Server") smoSvr.ConnectionContext.ServerInstance = svrName 'Create the database Set smoDB = CreateObject("Microsoft.SqlServer.Management.Smo.Database") smoDB.Parent = smoSvr smoDB.Name = dbName smoDB.Create() WScript.Echo "Done! Database created successfully." Set smoSvr = Nothing Set smoDB = Nothing

Before concluding this chapter, let's see an example of WMI support in SQL Server 2005.

Windows Management Instrumentation (WMI)

As described in Chapter 4, WMI provides an industry-standard programming interface for managing an enterprise environment. WMI provides a consistent way of managing hardware such as disk drives and software such as IIS Web server and SQL Server. (Refer to Chapter 4 for an overview of WMI.)

WMI support in SQL Server 2005 primarily allows you to do the following:

• Manage SQL Server services, service accounts, and network protocols. SQL Server Configuration Manager uses WMI and hence anything that you can do using SQL Server Configuration Manager can be programmed using WMI API.

• Monitor SQL Server events. As you will see in an example later in this section, you can write VBScript or code in some other language, use WMI, and monitor the events in SQL Server. In the example provided in this chapter, the VBScript monitors any DDL events, such as creating a table or dropping a table.

• Generate SQL Server Agent alerts in response to WMI events. This is essentially a counterpart to the previous point. You can write T-SQL code to raise an Agent alert in response to a WMI event. You can use the new @wmi_namespace and @wmi_query parameters added to the msdb.dbo.sp_add_alert stored procedure to implement this functionality.

Not enough time to work on this one! It's ok. Here is a VBScript example that uses WMI to monitor DDL events in SQL Server 2005:

[View full width]
Dim evtToMonitor Dim currentEvent Set evtToMonitor = GetObject("winmgmts:{impersonationLevel=impersonate}! //./root /Microsoft/SqlServer/ServerEvents/YUKON"). ExecNotificationQuery("select * from DDL_EVENTS") WScript.Echo "Running...Click OK to continue..." Do Set currentEvent = evtToMonitor.NextEvent If Err <> 0 then  WScript.Echo Err.Number, Err.Description, Err.Source  Exit Do Else  WScript.Echo "DDL Event in database '" & _       currentEvent.DatabaseName & "' for object '" & _       currentEvent.ObjectName & "'" & vbNewLine & vbNewLine & _       currentEvent.TSQLCommand & vbNewLine & vbNewLine  End If Loop

Type this VBScript text into a text file or use the WMIEventMonitor.vbs script file provided with the code download, update the WMI namespace to include your SQL Server 2005 instance name in place of YUKON, and then run the VBScript file by double-clicking it in File Explorer.

The period (.) in the WMI namespace indicates the local server. If you want to monitor SQL Server on a different machine, you type the machine name instead of a period. Also, the word YUKON in the WMI namespace is the SQL Server 2005 instance name. You should change it to your named instance; for default or unnamed instance, you can replace YUKON with the word MSSQLSERVER.

After you launch the VBScript file, click OK, and then start SQL Server Management Studio or a SQLCMD session and connect to the server specified in the WMI script. Then you should create a table by using the CREATE TABLE DDL or by using Object Explorer. Or you can alter or drop a table, and for each DDL action you perform on this server, the script shows a message box showing the database in which DDL action is performed, the object name in context, and the XML formatted DDL action. To end the monitoring, you launch Windows Task Manager and end the wscript.exe process.