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

MS SQL Server Managerment Objects (MS-SMO) Introduction

Microsoft has introduced Server Management Objects (SMO) with SQL Server 2005 that enhances the capability of Distributed Management Objects (DMO) of the earlier version of SQL Server. In SQL Server 2005, DMO is abandoned in favour of SMO. It allows managing the database server programmatically, and is compatible with the earlier versions of SQL Server (2000 and 7.0). However, SMO cannot be used to manage databases with compatibility level 60 and 65. The only functionality that SMO doesn’t offer as compared to DMO is that, replication objects are not included in SMO. Instead, there is separate Replication Management Objects (RMO) that exists for replication in SQL Server 2005.

To enlist few of the tasks that you can do with SMO are:

• Connect to Database Server
• Create Database
• Drop Database
• Backup Database
• Attach / Detach Database
• Copy Database Objects
• Create / Edit / Drop Objects (Tables / Views / Indexes / Stored Procedures / etc.)
• Create / Edit / Drop Relationship between tables
• Generate Scripts
• Handle HTTP and SOAP requests using EndPoints objects

The list still goes on. It’s almost everything that you can do in a Server; you can do it through SMO. In addition, the Capture Execution feature in SMO is an interesting new feature that allows capturing scripts for later execution. For example, suppose you have a section of your code that creates a database or table, adds an index, populates data, for example, in an installation routine. After testing, you can actually use SMO to capture this as a script for later execution, or on a separate server.

continued...