MS SQL Server Managerment Objects (MS-SMO) - Using SMO

The SMO namespace is implemented as a .NET assembly using which you can include all SMO functionality in your .NET application. The SMO namespace is Microsoft.SqlServer.SMO and is located by default in “C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies” directory. It is installed with the client tools of SQL Server 2005 and requires Common Language Runtime (CLR) to be installed as well. The assemblies required to work with SMO in managed environment are:

• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.SmoEnum
• Microsoft.SqlServer.SqlEnum

Other assemblies present in SMO can be used as and when required.

SMO can be used even in an unmanaged environment, as there exist the COM wrappers around the SMO classes. The SQLSMO.dll and SQLSMO.tlb files enable the SMO to be used with unmanaged code.

SMO Classes

The SMO Object Model Contains two types of classes: Instance Classes and Utility Classes. Each of them is discussed below in details.

Instance Classes

SMO Instance Classes contains the SMO Objects in a hierarchy that matches the hierarchy of a database server. At the top of the hierarchy is the Server Instance class that represents a SQL Server instance, and under it there are other classes in hierarchy representing the other objects of the database such as databases, tables, columns, indexes, stored procedures, etc.

A sample hierarchy of Instance Classes is depicted in the figure below:


Utility Classes

SMO Utility Classes are meant for performing some specific task, being independent of the SQL Server Instance. Lists of tasks, which can be performed using these utility classes, are:

• Generate Database Scripts
• Backup / Restore Databases
• Transfer Database Schema between database instances
• Administering the Database Mail subsystem
• Administering the SQL server Agent
• Administering the Service Broker
• Administering the Notification Services