Tip - SMO: SQL-DMO的替代者

一直不喜欢用SQL-DMO因为它是一个COM组件，在托管代码中使用总是有些麻烦。其实自从SQL2005出来后，就已经有了一个替代者 SMO - 一个托管代码组件

http://msdn.microsoft.com/en-us/library/ms345147.aspx

SQL Management Objects

The SQL Management Objects (SMO) model is the management object model for SQL Server 2005. SMO represents significant design and architectural improvements for the SQL Server management object model. It is a simple to use, but rich object model that is based on .NET Framework managed code. SMO is the primary tool for developing database management applications using .NET Framework or for automating SQL Server 2005 management operations. SMO is used by every dialog box in SQL Server Management Studio, and every administrative action that you can perform in SQL Server Management Studio you can also accomplish by using SMO.

The new SMO object model and the Microsoft Windows Management Instrumentation (WMI) APIs replace SQL-DMO. Where possible, SMO incorporates similar objects as SQL-DMO for ease of use. You can still use SQL Server 2005 with SQL-DMO, but SQL-DMO will not be updated to manage features that are specific to SQL Server 2005.

SMO and SQL-DMO

The SMO object model is a logical continuation of the work done in SQL-DMO. SMO is feature-compatible with SQL-DMO, containing many of the same objects. Where possible, the original SQL-DMO design is followed, but SMO has a number of additional features beyond SQL-DMO. To achieve maximum data definition language (DDL) and administrative coverage for SQL Server 2005, SMO adds more than 150 new classes.

The primary advantages of SMO are in its performance and scalability. SMO has a cached object model, which allows you to change several properties of an object before effecting the changes to SQL Server. As a result, SMO makes fewer round trips to the server, and makes its objects more flexible. SMO also has optimized instantiation, meaning that you can partially or fully instantiate objects. You can load many objects quickly by not instantiating all the properties of the objects.

Unlike SQL-DMO, which has a single application root directory that keeps references to all created server objects, SMO lets you establish multiple roots for servers without establishing a new connection. SMO implements advanced multiple-phase scripting, in addition to supporting SQL-DMO style scripting. You can also switch an object into capture mode and capture any DDL that would be emitted for that object, without actually applying changes to the server.

SQL-DMO also has a managed computer object that simplifies the interface to WMI, in order to support WMI monitoring and server configuration through the SMO object interface.