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...