Jaa

Overview

  • Artikkeli

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications. Although SQL Server Management Studio is a powerful and extensive application for managing SQL Server, there might be times when you would be better served by an SMO application.

For example, the user applications that control the SQL Server management tasks might have to be simplified to meet the needs of new users and to reduce training costs. You might have to create customized SQL Server databases, or create an application for creating and monitoring the efficiency of indexes. An SMO application might also be used to include third-party hardware or software seamlessly into the database management application.

The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model. Compared to SQL-DMO, SMO increases performance, control, and ease of use. Most SQL-DMO functionality is included in SMO, and there are various new classes that support new features in Microsoft SQL Server 2005. The object model is intuitive and uses SQL-DMO terminology, where it is possible, to help transfer your skills.

Important

SMO does not support compatibility level 60, 65 or 70. If you use SMO with a database set to compatibility level 60, 65 or 70, you will not be able to manage the database by using SMO.

New features in SMO include the following:

  • Cached object model and optimized object instance creation. Objects are loaded only when specifically referenced. Object properties are only partially loaded when the object is created. The remaining objects and properties are loaded when they are referenced directly.
  • Batched execution of Transact-SQL statements. Statements are batched to improve network performance.
  • Capture Transact-SQL statements. Allows any operation to be captured into a script. Management Studio uses this capability to script an operation instead of executing it immediately.
  • Management of SQL services with the WMI Provider. SQL services can be started, stopped, and paused programmatically.
  • Advanced Scripting. Transact-SQL scripts can be generated to re-create SQL Server objects that describe relationships to other objects on the instance of SQL Server.
  • Use of Unique Resource Names (URNs). A URN allows you to create instances of and reference SMO objects.

SMO also represents as new objects or properties many features and components new to SQL Server 2005. These new features and components include the following:

The SMO namespace is Microsoft.SqlServer.Management.Smo. SMO is implemented as a Microsoft .NET assembly. This means that the common language runtime from the Microsoft .NET Framework version 2.0 must be installed before using the SMO objects. The SMO assemblies are installed by default into the Global Assembly Cache (GAC) with the SQL Server 2005 SDK option. The assemblies are located in c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies. For more information about redistributing applications see the Visual Studio .NET documentation.

SMO Classes

SMO classes include two categories: instance classes and utility classes.

Instance Classes

The instance classes represent SQL Server objects such as servers, databases, tables, triggers, and stored procedures. The ServerConnection class is used to establish a connection to the instance of SQL Server and control the capture mode of commands sent to it.

The SMO instance objects form a hierarchy that represents the hierarchy of a database server. At the top are the instances of SQL Server, under which are the databases, and following on with tables, columns, triggers and so on. If it is logical that there is a one parent to many children relationship, such as a table having one or more columns, then the child is represented by a collection of objects. Otherwise the child is just represented by an object.

Utility Classes

Utilities classes are a group of objects that have been created explicitly to perform specific tasks. They have been divided into different object hierarchies based on function:

  • Transfer class. This is used to transfer schema and data to another database.
  • Backup and Restore classes. These are used to back up and restore databases.
  • Scripter Class. This is used to create script files for the regeneration of objects and their dependencies.

New SMO Features

Optimized Performance

In SQL-DMO, object enumeration required that each object within a collection was fully instantiated. This is inefficient in terms of network and memory footprint. On many occasions an object might be instantiated without most of its properties being explicitly referenced.

The SMO architecture is more efficient in terms of memory because objects are only partially instantiated at first, and minimal property information is requested from the server. Full instantiation of objects is delayed until the object is explicitly referenced. An object is fully instantiated when a property is requested that is not in the set of properties that are first retrieved, or when a method is called that requires such a property. The transition between partially instantiated and fully instantiated objects is transparent to the user. Additionally, some properties that use lots of memory are never retrieved, unless the property explicitly referenced. An example of this is the Size property of the Database object property. However, partial instantiation does require more network round trips and might not be the best performing option for your application.

You can control instantiation to suit the system environment. Relying on delayed instantiation minimizes the amount of memory required by the application, although it might trigger many server requests when properties are referenced.

Instance classes, objects that represent real database objects, can exist in three levels of instantiation. These are minimal-instantiated (only the minimal required properties are read in one block), partially instantiated (all the properties that use a relatively large amount of memory are read in one block), and fully instantiated. Un-instantiated and fully instantiated are the traditional states of instantiation. The partially instantiated state increases efficiency because a partially instantiated object does not contain values for the full set of object properties. Partial instantiation is the default state for an object that is not directly referenced. When one of these properties is referenced, a fault is generated that prompts a full instantiation of the object.

Capture Execution

Direct execution is the usual method of execution. Statements are sent to an instance of SQL Server directly as they are incurred. Capture execution is the alternative to this.

Capture execution lets you capture Transact-SQL batches that would typically be executed. This lets the SMO programmer defer the script, store it for later execution, or provide a preview for the end-user. For example, a create database, a create table, and a create index statement can be sent in one batch and then run as three sequential steps. This functionality is controlled by the user by using the Server object.

WMI Provider

The WMI Provider objects are wrapped by SMO. This provides the SMO programmer with a simple object model that is similar to SMO classes very closely, without the requirement to understand the programming model that is represented by the namespace and the details of the SQL Server WMI Provider. The WMI Provider lets you configure SQL Server services, aliases, and client and server network libraries.

Scripting

In SMO, scripting has been enhanced and moved into the Scripter class. The Scripter class can discover dependencies, understand the relationships between objects, and enables manipulation of the dependency-hierarchy. The main scripting object is the Scripter object. There are also several supporting objects that handle the dependencies and respond to progress or error events.

The Scripter object supports the following advanced scripting options:

  • Simple 1-phase scripting (creates the script in one step)
  • Advanced 3-phase scripting (creates the script in three steps; depencency discovery, list generation, script generation)
  • Two-way dependency discovery (allows for discovery of dependencies, or dependents)
  • Response to progress events
  • Response to error events

Unique Resource Names

A key concept in using the SMO object library is the Unique Resource Name (URN). The URN uses a syntax similar to XPath. The XPath is a hierarchy path used to specify an object in which each level has qualifiers and functions. In SMO the URN has two elements, the path and attribute naming that has limited functionality. The path is used to specify the location of the object whereas the attribute naming allows for a degree of filtering.

An example of an URN for a database is

/Server/Database[@Name='Adventureworks']

The URN of an object can be retrieved by referencing its URN property. The Scripter object also uses URNs as parameters that pass object references to the method of the Scripter object. Additionally, an URN can be specified for the GetSmoObject method of the Server object. This is used to create an instance of the SMO object.

New SQL Server 2005 Features Represented in SMO

Table and Index Partitioning

Index Table Partitioning lets you manage the spread of data in tables and indexes across file groups. This new feature is represented by SMO objects.

EndPoints

SOAP and database mirroring requests are handled by endpoints using the Endpoint object.

Snapshot Isolation/Row Level Versioning

Snapshot Isolation (row level versioning) is represented by new Database object properties.

XML Schema Namespace, XML Indexes and XML datatype

XML Schema Namespaces are represented in SMO by a collection of objects. XML indexes are represented in SMO by an Index object property.

Full Text Search Enhancements

New objects are provided in SMO that represent the enhancements to full text search.

Page Verify

The PageVerify object represents database page verify options.

Snapshot Databases

A snapshot database is a read-only copy of a specified database as a specific point in time. A snapshot database can be specified by using the IsDatabaseSnapshot property of the Database object.

Service Broker

Service Broker and its functionality is represented by a group of objects

Index Enhancements

SQL Server 2005 index enhancements are represented by new properties in the Index object.

SMO and SQL-DMO

The SMO object model supersedes and replaces SQL-DMO. SMO supports SQL Server 2000, and SQL Server 2005. It supports more SQL Server management tasks and contains many new features in SQL Server 2005. SMO is designed to be more efficient and provide more control.

The DMO library is a COM object model, whereas SMO is implemented as a .NET assembly. COM components are libraries that provide re-usable functionality to applications and in unmanaged application programming. The .NET assemblies provide reusable functionality for the .NET Framework to write managed code applications.

During the transition to .NET technology it is possible to have applications written partly in managed code and partly in unmanaged code. The .NET Framework lets you interface with COM components, which requires a Primary Interop Assembly. A runtime wrapper is required for SQL-DMO so that it can be called from a .NET application.

See Also

Other Resources

What's New in SQL Server 2005
Programming with Replication Management Objects
SQL Distributed Management Objects (SQL-DMO)
Creating DMX Queries in SQL Server Management Studio

Help and Information

Getting SQL Server 2005 Assistance