Chapter 11. Inside Programming Object Models

SQL Server 2005 ships with two programming object models that allow programmatic management of SQL Server objects. SQL Server Management Objects (SMO) is a brand-new API. SMO is implemented as a .NET library and replaces SQL-DMO (COM server with multiple automation interfaces), which shipped as part of earlier releases and is deprecated with SQL Server 2005. SMO has an object tree hierarchy similar to SQL-DMO and provides most of the functionality of SQL-DMO, plus added support for all new objects added to SQL Server Database Engine in SQL Server 2005. SQL-DMO has also been upgraded to manage SQL Server 2005 Database Engine Instance, but no new functionality has been added to it. Therefore, customers using new SQL Server 2005 specific Database Engine features, such as, SQL Server Service Broker, Http Endpoints, and so on, will have to migrate to SMO.

Tip

Old applications written based on SQL-DMO will continue to function after an upgrade to SQL Server 2005 as long as the client computer using SQL-DMO has an upgraded version of the SQL-DMO library as well. Consider upgrading client computers before upgrading your server to ensure a smooth transition to the new platform.

SMO can manage SQL Server 2000 and SQL Server 2005 Database Engine instances. It cannot, however, manage earlier versions of Database Engine.

SMO, similar to DMO, is freely redistributable with your application, provided you comply with certain licensing restrictions. A special redistribution module contains SMO assemblies and registration code that you can download and that ship as part of your product. It is called XMO installer package and includes a few other assemblies in addition to SMO. You can download the RTM version from http://download.microsoft.com/download/f/7/4/f74cbdb1-87e2-4794-9186-e3ad6bd54b41/SQLServer2005_XMO.msi.

Please keep in mind that you don't need to download it separately if you are planning to install SQL Server 2005 on your computer anyway. In this case SMO components are installed automatically when you select the Client Tools option.

This chapter covers SMO through multiple usage scenarios, then covers internal SMO architecture, and finally dives into performance and optimization topics.