Microsoft Sql Server 2012 Management Objects

[Jesper Sahu]

Ihave been working with SQL Server for a couple of years. I have heard about SMO but I don't know anything about it. What are the benefits of using it? Should I learn and start using SMO in my SQL Server projects (mainly data warehouse development)? Why?

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications. Although SQL Server Management Studio is a powerful and extensive application for managing SQL Server, there might be times when you would be better served by an SMO application.

For example, the user applications that control the SQL Server management tasks might have to be simplified to meet the needs of new users and to reduce training costs. You might have to create customized SQL Server databases, or create an application for creating and monitoring the efficiency of indexes. An SMO application might also be used to include third-party hardware or software seamlessly into the database management application.

The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model. Compared to SQL-DMO, SMO increases performance, control, and ease of use. Most SQL-DMO functionality is included in SMO, and there are various new classes that support new features in SQL Server. The object model is intuitive and uses SQL-DMO terminology, where it is possible, to help transfer your skills.

It depends on what you're trying to do. SMO is SQL Server Management Objects. It is a set of libraries for managing SQL Server programmatically. For example if you're trying to build a clone of SQL Maangement Studio then SMO is something you probably want to look into. OR if you're trying to manpulate the structure of your database programmatically then that's the place to look.

I've recently created an POS/Work Order Management application with a SQL database backend and the SMO library. The SMO gave my a application a lot of flexibility to control the database in terms of work order records, user's records an even my own set of user roles. Helping me to differentiate SQL users when managing a specific database. So, my take is that it all depends on the extend of your use of SQL and how much you may need to automate and control certain aspects of your SQL database.

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management.

Cached object model and optimized object instance creation. Objects are loaded only when referenced. Object properties are only partially loaded when the object is created. The remaining objects and properties are loaded when they are referenced directly.

The SMO namespace is Microsoft.SqlServer.Management.Smo. SMO is implemented as a Microsoft .NET Framework assembly. This means that the common language runtime from the Microsoft .NET Framework version 2.0 must be installed before using the SMO objects. The SMO assemblies are installed by default into the Global Assembly Cache (GAC) with the SQL Server SDK option. The assemblies are located in C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies. For more information, see the Visual Studio .NET Framework documentation.

The instance classes represent SQL Server objects such as servers, databases, tables, triggers, and stored procedures. The ServerConnection class is used to establish a connection to the instance of SQL Server and control the capture mode of commands sent to it.

The SMO instance objects form a hierarchy that represents the hierarchy of a database server. At the top are the instances of SQL Server, under which are the databases, and following on with tables, columns, triggers, and so on. If it is logical that there is a one parent to many children relationship, such as a table having one or more columns, then the child is represented by a collection of objects. Otherwise the child is represented by one object.

The SMO architecture is efficient in terms of memory because objects are only partially instantiated at first, and minimal property information is requested from the server. Full instantiation of objects is delayed until the object is explicitly referenced. An object is fully instantiated when a property is requested that is not in the set of properties that are first retrieved, or when a method is called that requires such a property. The transition between partially instantiated and fully instantiated objects is transparent to the user. Additionally, some properties that use lots of memory are never retrieved, unless the property explicitly referenced. An example of this is the Size property of the Database object property. However, partial instantiation does require more network round trips and might not be the best performing option for your application.

You can control instantiation to suit the system environment. Relying on delayed instantiation minimizes the amount of memory required by the application, although it might trigger many server requests when properties are referenced.

Instance classes, objects that represent real database objects, can exist in three levels of instantiation. These are minimal-instantiated (only the minimal required properties are read in one block), partially instantiated (all the properties that use a relatively large amount of memory are read in one block), and fully instantiated. Uninstantiated and fully instantiated are the traditional states of instantiation. The partially instantiated state increases efficiency because a partially instantiated object does not contain values for the full set of object properties. Partial instantiation is the default state for an object that is not directly referenced. When one of these properties is referenced, a fault is generated that prompts a full instantiation of the object.

Capture execution lets you capture Transact-SQL batches that would typically be executed. This lets the SMO programmer defer the script, store it for later execution, or provide a preview for the end-user. For example, a create database, a create table, and a create index statement can be sent in one batch and then run as three sequential steps. This functionality is controlled by the user by using the Server object.

The WMI Provider objects are wrapped by SMO. This provides the SMO programmer with a simple object model that is similar to SMO classes closely, without the requirement to understand the programming model that is represented by the namespace and the details of the SQL Server WMI Provider. The WMI Provider lets you configure SQL Server services, aliases, and client and server network libraries.

In SMO, scripting has been enhanced and moved into the Scripter class. The Scripter class can discover dependencies, understand the relationships between objects, and enables manipulation of the dependency-hierarchy. The main scripting object is the Scripter object. There are also several supporting objects that handle the dependencies and respond to progress or error events.

A key concept in using the SMO object library is the Unique Resource Name (URN). The URN uses a syntax similar to XPath. The XPath is a hierarchy path used to specify an object in which each level has qualifiers and functions. In SMO the URN has two elements, the path and attribute naming that has limited functionality. The path is used to specify the location of the object whereas the attribute naming allows for a degree of filtering.

The URN of an object can be retrieved by referencing its URN property. The Scripter object also uses URNs as parameters that pass object references to the method of the Scripter object. Additionally, an URN can be specified for the GetSmoObject method of the Server object. This is used to create an instance of the SMO object.

SQL Server 2005 and 2008 provide SQL Server ManagementObjects (SMO), a collection of namespaces which in turn contain different classes,interfaces, delegates and enumerations, to programmatically work with and managea SQL Server instance. SMO extends and supersedes SQL Server Distributed ManagementObjects (SQL-DMO) which was used for SQL Server 2000. In this tip, I am going todiscuss how you can get started with SMO and how you can programmaticallymanage a SQL Server instance with your choice of programming language.

For example, consider you are developing a build deployment tool, this tool willdeploy the build but before that it needs to make sure that the SQL Server and SQLServer Agent services are running, a database is available and online. For thiskind of work, you can use SMO, a SQL Server API object model.

SMO object model is based on managed code and implemented as .NET Framework assemblies.It provides several benefits over traditional SQL-DMO along with support for newfeatures introduced with SQL Server 2005 and SQL Server 2008.

There are a couple of other assemblies which contain namespacesfor certain tasks, but few of them are essential to work with SMO. Some of the frequentlyused namespaces and their purposes are summarized in the below table, other namespacesare used for specific tasks like working with SQL Server Agent where you would referenceMicrosoft.SqlServer.Management.Smo.Agent etc.

3a8082e126