SQL Server Management Objects

In this tutorial we shall learn about SQL Server Management Objects, briefly examine the different objects available for the process, Connecting to the Server, Database objects and Collections and Scripting.

The Administrative programming interface of SQL Server 2005, eases the process of managing SQL Server Management objects. The developer is given a complex array of objects that are specifically designed to assist in the task.

Connecting to the Server

Before actually beginning the task of managing objects, the developer must connect to the server. This can be done by way of standard authentication or integrated security.

SQL server 2005 goes way beyond to provide an automated system of connecting known as connection pooling. Users can select the option or unselect it by specifying the option in the NonPooledConnection property of the ConnectionContext object. If selected the user does not have to connect and disconnect from the server manually. The connection is established automatically when required and released to a connection pool when not required.

For establishing connection using the Server Authentication mode the developer will have to create an instance of the ServerConnection class. This is achieved by overloading the constructor and allowing the overload pass in three string parameters that represent the server, user name and password. The connection will be made automatically from the server pool.

Integrated security requires the developer create an object instance of the Server class. The LoginSecure property need not be set and user name and password need not be specified. The user only has to specify the server name to which he wants to connect. The login credentials of the windows logged in user will be used.

Database objects and Collections.

The most commonly used objects are database, tables and views. The database is a parent object that is a container for other objects such as tables and views. A number of methods are available for the developer to access these child objects via the parent object. The database object itself is the child object of the Server object. To access the database object the developer has to assign a variable of the type Database from the Database collection property of the server object. The trusted connection then clears the contents of the combo cboDatabases and iterates the database collection of the server object. This gives him access to the contents of the database and its objects.

The individual table or view within a database object can be accessed via the database object. The columns and indexes of each table is iterated along with the keys and constraints. The database objects will be loaded in a hierarchical display. If tables and views in different schemas have similar names, the schema name can be included in the display. Specialized tools can be written to manage database objects or security.

Yet other objects that can be accessed via the SMO and are innumerable. It would be beyond the scope of this tutorial to cover all these objects and hence an attempt is not being made here.

Scripting

An advantage of the SMO is the ability to define a number of objects and then call the Script method of the Scripter object with the array of objects as its parameter. The function returns a string with a script for each object in the array and scripts for related objects. Unlike the DMO version, the SMO version takes advantage of the .NET frameworks capability to copy arrays and collections and does not do its own scripting and iterating. The objects can be serialized and de-serialized back into the array of objects and this makes the creation of stateless applications easier.

The objects in the SMO give the user the ability to manage the SQL server. All services, starting and stopping services, managing objects at the server and database levels, all can be accomplished using the SMO. It can be a double edged weapon that is extremely powerful. However, it is possible to restrict what programmers can do and cannot do with SMO by defining the limits and boundaries of jobs for various levels using the SMO itself!