Using the SQL Management Objects

In this tutorial you will learn about Using the SQL Management Objects, Unsupported namespaces, Programming SQL Server Management objects, Setting Default Initialization Fields, Definitions, Capture mode, Linked servers and SMO methods and properties.

SQL Server Management objects (SMO) exposes the functionality of SQL Server database and replication management. SMO can be used to automate repetitive tasks or commonly performed administrative tasks. The SQL SMO is implemented as a .NET assembly and the model extends and replaces the SQL Server Distributed Management object (DMO) of the earlier versions. A number of enhancements have been made such as partial instantiation, capture mode execution, delegated execution, objects in space and integration with .NET framework.

There are certain features that all Server Management objects share such as running methods, setting properties and manipulating collections. Specific tasks can be programmed using SMO objects. These include complex subjects that are required by programs with specialized functions such as backing up, monitoring statistics, replication, managing instance objects and setting configuration options.

SQL Server Management Objects is installed when SQL Server 2005 Client tools is installed. The files can be removed or added from the installation by selecting the SDK branch of the Client Components option on the feature selection screen during setup. The assemblies relating to the SMO are installed in C:\Program Files\Microsoft SQL Server \90 \SDK \Assemblies directory by default. To program the SMO it is necessary to select the language that is supported by the CLR.

The SMO object model is a hierarchy of objects with the Server object at the top level and all the instance objects residing within the Server object. The ManagedComputer is the top level class and has a separate object hierarchy. It represents Microsoft SQL Server services and network settings that are available through the WMI provider. A number of utility classes represent tasks such as Transfer, backup or Restore. The Model is made up of several namespaces and uses the System.Data.SqlClient object driver to connect to and communicate with different instances of SQL Server.

The SMO client requires SQL Server Native Client that is part of the SQL Server 2005 and .NET Framework 2.0.

Applications in SMO require the installation of Visual Studio 2005.

Transaction processing in SMO is done by using the ServerConnection object which is referenced by the ConnectionContext property of the Server object. Methods such as StartTransaction, RollBackTransaction and CommitTransaction are available to the user.

It is not possible to upgrade a SQL DMO application to SMO. The application will have to be rewritten using SMO classes.

A number of SMO namespaces are not supported by earlier versions of SQL Server. Some of the unsupported namespaces are:

• Microsoft.SqlServer.Management.NotificationServices
• Microsoft.SqlServer.Management.Smo.Broker
• Microsoft.SqlServer.Management.Smo.Mail
• Microsoft.SqlServer.Management.Smo.RegisteredServer
• Microsoft.SqlServer.Management.Smo.Wmi
• Microsoft.SqlServer.Management.Trace

Microsoft.SqlServer.Management.Smo.Agent and Microsoft.SqlServer. Management. Smo namespaces are partially supported. Some classes in Microsoft.SqlServer. Management.Smo provide support for SQL Server 2000 and SQL Server 7.0 while several distributed Management objects have been removed in the transition.

Programming SQL Server Management objects

An instance of SQL Server can be connected in three ways. The ServerConnection object variable can be used to provide connection information or the server object property can be set to provide connection information or the name of the SQL Server can be passed in the Server object constructor.

The advantage of using the first option is that the connection information can be reused. The user declares a Server object variable and then declares a ServerConnection object and sets the properties with connection information such as the name of the instance of SQL Server and the authentication mode. The ServerConnection object variable is then passed as a parameter to the Server object constructor. However, shared connections between different server objects will create problems in this scenario if used simultaneously.

Server object properties can be set explicitly and the constructor can be called. The Server object will try to connect to the default instance of the SQL Server with all the default connection settings.

Finally the SQL Server Instance name can be specified in the Server object constructor by declaring the server object variable and passing the instance name as a string parameter in the constructor. This establishes the connection with the instance of SQL Server with default connection settings.

Closely allied to the above concept is the Connection pooling concept. SMO automatically establishes a connection when required and releases the connection to the connection pool when not required. However, when the connect method is called the connection is not released to the pool automatically and a disconnect method needs to called to perform the operation. Additionally, a non-pooled connection can also be requested using the NonPooledConnection property of the ServerConnection object.

Multi threaded applications require the use of separate ServerConnection objects for each thread.

Setting Default Initialization Fields

When retrieving objects SMO performs optimizations. The optimization minimizes the number of properties loaded by automatically scaling between the different states. In a partially loaded scenario a minimum number of properties are referenced. In a fully loaded instance referenced properties are initialized and made available quickly in the order of speed of loading. Properties which require a large amount of memory are loaded only when specifically required.

The SetDefaultInitFields method can be used to ensure that the application loads only the required properties for optimal performance. This can be reset when required and the resetting can be neutralized using the GetDefaultInitFields when required.

Definitions

A collection is a list of objects that have been constructed from the same object class and share the same parent object. Data is a string that has a defined length, a number that has specific accuracy or a user defined data type that is an object with its own set of rules. The DataType object classifies the type of data so that it can be handled accurately by the SQL Server. The SMO objects that accept data must have a DataType object property as under and the properties must be set.

• Column
• UserDefinedDataType
• UserDefinedType
• UserDefinedFunctionParameter
• StoredProcedureParameter
• UserDefinedFunctionParameter
• UserDefinedAggregateParameter

The Capture mode has to be enabled to capture and record the equivalent T-SQL statements issued by the programs. This is enabled by using the ServerConnection object or ConncetionContext property of the Server object. The StartTransaction, RollBackTransaction and CommitTransaction methods belong to the ConnectionContext property of the Server object.

Linked servers in SMO are represented by the LinkedServer object. The LinkedServerLogins property references a collection of LinkedServerLogin objects. The logon credentials are stored and help establish the connection with the linked server. OLE DB providers are represented by the OleDbProviderSettings objects.

SMO methods and properties are not designed to report success or failure of a return value. In other words no exceptions are thrown when an error occurs. Errors have to be caught and handled with an exception handler. A number of exception classes have been built into the SMO for this purpose.