One of the most exciting new technologies in SQL Server's Yukon release provides a programmatic access layer that lets database administrators and developers work directly with database objects in any Microsoft .NET language. Yukon's SQL Management Objects (SMO) replaces SQL-DMO, a set of APIs you can use to create and manage SQL Server database objects. Although SQL-DMO will still ship as part of Yukon, Microsoft isn't adding features to it or enhancing it to eliminate or reduce its performance and scalability constraints. To access Yukon's features and take advantage of its powerful architecture, you'll have to use SMO. However, SMO will work with SQL Server 2000 and 7.0 as well as Yukon.
When I asked Microsoft Program Manager Michiel Wories what SMO's design goals were, he said scalability and performance. After I used the object model for the first time, I added ease of use and flexibility to its description. Microsoft architects built SMO as a series of efficient .NET assemblies to achieve scalability and performance. Let's look at some of the features that make SMO so powerful.
Delayed instantiation. SMO's first important optimization over SQL-DMO is delayed instantiation. As you run your application, SMO retrieves objects and properties as needed. The key to this optimization is making many small round-trips to the server instead of getting everything up front, as SQL-DMO does and which is overkill in many scenarios. SMO also lets you prefetch entire collections. In addition, you can retrieve objects by using a set of predefined properties. The bottom line is that the programmer has control over SMO behavior, which lets you build an application that suits your needs.
Caching. The SMO object model is also cached, meaning that SMO doesn't propagate object changes to the server immediately. Instead, it caches them until you decide to apply (or discard) the changes. This caching yields fewer round-trips to the server because SMO sends all changes as one set of batches.
Releasable state. SMO lets you release application state because SMO removes the association between the Application object and the SQL Server object (called Server). Thus, you can instantiate a Server object (you can reuse an existing connection), perform your operations (e.g., actions on a large collection of tables), then release the reference to the Server object. The advantage of this design is that you control when to release object state, which lets you write programs that use memory efficiently.
Scripting. SMO provides advanced scripting functionality as part of the new Scripter object. This object lets you discover database-object dependencies, which results in an object tree. You can create an ordered object list from that tree, then generate a script from the list and optionally specify scripting options (a superset of SQL-DMO's scripting options). This architecture gives you maximum control over each scripting phase, letting you build specialized, customized scripting solutions.
Capturing T-SQL. In addition, SMO includes a script-capture mode that lets you capture the T-SQL code that SMO generates when your application performs an operation on an object. For example, a Visual Basic (VB) guru can use SMO to grab the T-SQL that his or her application generates.
Offline configuration. Yukon introduces a Windows Management Interface (WMI) provider that lets you configure SQL Server—related settings, such as the SQL Server Service account and client and server network protocols, without requiring a connection to SQL Server. You can access the WMI provider in a variety of ways, including through VBScript and the System.Management namespace. However, SMO's Managed Computer object lets you access this provider by wrapping all the SQL Server—related WMI objects in an easy-to-use, hierarchical set of SMO objects. If you know how to program with SMO, you can modify SQL Server's settings offline without having to learn WMI.
Yukon's new SQL Server Workbench management tool uses SMO, and every administrative action you can perform through the Workbench you can also do through SMO. As you develop your deployment and maintenance applications for SQL Server Yukon, seriously consider SMO, which adds more than 150 classes to cover Yukon Data Definition Language (DDL) and administrative functionality. SMO is powerful but easy to use. And it lets DBAs quickly and safely venture into the .NET programming world.