New Management Frameworks

In addition to the new management tools found in the SQL Server 2005, the SQL Server management framework has also been completely revamped. A new .NET-based management framework called System Management Objects (SMO) can be used to write custom server management applications for SQL Server 2005. New .NET-based management APIs have also been added for Analysis Services and replication management. Another significant new change for the SQL Server 2005 administrative tool set is support for WMI configuration and WMI events. In the following section of this chapter, you’ll find out more about each of these new management frameworks in SQL Server 2005.

SQL Server Management Objects (SMO)

Like its predecessor, Distributed Management Objects (DMO), the SMO object framework is designed to be able to programmatically manage every aspect of SQL Server. However, unlike the older DMO object framework, which was based on COM, the new SMO object framework has been implemented as a .NET class library. This means that SMO requires the .NET Framework to be installed on the systems that are used to run SMO management applications. SMO can be used to manage SQL Server 7 and SQL Server 2000 systems as well as SQL Server 2005.

For backward compatibility, DMO will continue to be supported by SQL Server 2005, but it has not been enhanced to support the new features found in the new release. For instance, DMO cannot be used to manage the SQL Service Broker, the new HTTP Endpoints feature, the new XML and varbinary(max) data types, or any of the other numerous new features that Microsoft has added to the SQL Server 2005 release. In other words, DMO is limited to supporting only those features that were found in the previous releases of SQL Server. The new SMO object framework provides over 150 new classes to represent the new features found in SQL Server 2005. To make it easy to migrate older DMO applications to SMO, the object framework used by SMO is closely based on the same object framework that was used by DMO. While the object frameworks are not identical, they are quite similar. For instance, both SMO and DMO possess objects that represent servers, databases, tables, columns, and the other major database objects.

In addition to being able to access all of the new features found in SQL Server 2005, the new SMO object framework provides a couple of other important new features. First, SMO uses optimized instantiation of objects, which means that it can create an instance that represents a high-level object like a server object without needing to retrieve all of the properties that compose that object. In the case of an object like a server, this means that the SMO doesn’t need to retrieve all of the database information plus all of the database object information for all of the databases on the server when the server object is created. Full instantiation is delayed until the object is explicitly referenced. Optimized instantiation results in much better performance—especially for VLDB implementations. SMO also has the ability to capture and batch together groups of SQL statements for better performance. There’s also an enhanced scripting capability that’s enabled by a new Scripter class. The Scripter class is able to discover and create creation scripts for database objects and their dependencies.

SMO is implemented in the Microsoft.SqlServer.Management.Smo, Microsoft.SqlServer.Management.Smo.Common, and Microsoft.SqlServer.Management .Smo.Agent namespaces.

Analysis Management Objects (AMO)

Just as SMO is used to manage SQL Server servers, Analysis Management Objects (AMO) is used to manage Analysis Services. Among other items, AMO can be used to manage Analysis Services servers, data sources, cubes, dimensions, measures, and data mining models. Like SMO, AMO is a .NET-based object framework that requires the CLR in order to run. AMO is conceptually based on the older COM-based Decision Support Objects (DSO) library. As is the case with DMO, SQL Server 2005 still supports DSO for backward compatibility of existing applications. AMO generates XML-based messages that can be used to create or change Analysis Server objects. These XML messages can be saved as XML documents. AMO is implemented in the Microsoft.SqlServer.Management .AnalysisServices namespace.

Replication Management Objects (RMO)

Exactly as SMO has been designed to control all aspects of SQL Server configuration and management, the new .NET-based Replication Management Objects (RMO) is designed to configure and manage the database replication process in SQL Server 2005. As with SMO and AMO, RMO applications require the presence of the CLR. The new RMO object framework contains classes that enable the management of SQL Server replications, administration, synchronization, and monitoring. RMO is implemented in the Microsoft.SqlServer.Replication namespace.

Windows Management Instrumentation (WMI)

SQL Server 2005 can also be managed using the Windows Management Instrumentation (WMI) API. WMI is Microsoft’s cross-product management standard, but prior to the SQL Server 2005 release, WMI wasn’t as effective at managing SQL Server as DMO or SQL Server Enterprise Manager. For shops attempting to standardize on the WMI management framework, this made enterprise deployments of SQL Server difficult, as manual intervention was often required. To address enterprise manageability, SQL Server 2005’s WMI management capabilities have been extended with a new WMI Configuration Provider and new WMI events.

WMI Configuration Provider

SQL Server 2005’s new WMI Configuration Provider enables a remote management application to connect to SQL Server and perform management functions. The WMI configuration API is designed to function in a disconnected fashion. The WMI provider is implemented in the Microsoft.SqlServer.Smo.Wmi namespace.

WMI Events

SQL Server 2005’s new WMI events enable SQL Server to be monitored by Microsoft Operations Manager (MOM) and other third-party WMI-compliant management applications. SQL Server 2005 can raise WMI events for virtually all of the operations that can be traced using Profiler. For example, events can be generated for create database, drop database, alter database, and all of the other operations that can be traced. Just as when using Trace, there’s some overhead generated when you use WMI events, but they provide very rich management information. WMI events can be raised by SQL Server, Analysis Services, DTS, and Reporting Services.