Comparing SMO and SQL-DMO

SQL Server Management Objects (SMO) is a managed code object model that was introduced in SQL Server 2005. It replaces SQL Distributed Management Objects (SQL-DMO). Similar to SQL-DMO, SMO provides objects that are used to manage SQL Server. The programming model has been improved, and the number of objects has grown to include features that were introduced in SQL Server 2005. Starting with SQL Server 2008, SMO is based on the SQL Server Foundation classes (SFC). The Policy-Based Management object model (DMF) is also introduced in SQL Server 2008.

SMO includes the following features:

  • Improved performance. Objects are loaded only when specifically referenced. Object properties are only partially loaded when the object is created. The remaining objects are loaded when they are referenced directly

  • Capture and batched execution of Transact-SQL statements. Statements can be captured and sent as a batch to improve network performance.

  • Management of SQL Server services with the WMI Provider. SQL Server 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.

  • Transfer of data is leveraged off the scripting functionality and uses Integration Services.

  • Use of Unique Resource Names (URNs).

  • Event handling that allows the developer to insert code that is executed when a specific event occurs.

  • Exceptions that are used to identify specific to SMO errors.

  • Better coverage of database objects. For example, the ForeignKey object allows for more control over foreign keys.

SMO also represents as new objects or properties a number of features and components that were introduced in SQL Server 2005. These new features and components include the following: