Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Using the SQL Management Objects

By Exforsys | on December 15, 2005 |
SQL Server 2005

Using the SQL Management Objects

In this tutorial you will learn about Using the SQL Management Objects, Unsupported namespaces, Programming SQL Server Management objects, Setting Default Initialization Fields, Definitions, Capture mode, Linked servers and SMO methods and properties.

SQL Server Management objects (SMO) exposes the functionality of SQL Server database and replication management. SMO can be used to automate repetitive tasks or commonly performed administrative tasks. The SQL SMO is implemented as a .NET assembly and the model extends and replaces the SQL Server Distributed Management object (DMO) of the earlier versions. A number of enhancements have been made such as partial instantiation, capture mode execution, delegated execution, objects in space and integration with .NET framework.

There are certain features that all Server Management objects share such as running methods, setting properties and manipulating collections. Specific tasks can be programmed using SMO objects. These include complex subjects that are required by programs with specialized functions such as backing up, monitoring statistics, replication, managing instance objects and setting configuration options.

SQL Server Management Objects is installed when SQL Server 2005 Client tools is installed. The files can be removed or added from the installation by selecting the SDK branch of the Client Components option on the feature selection screen during setup. The assemblies relating to the SMO are installed in C:\Program Files\Microsoft SQL Server \90 \SDK \Assemblies directory by default. To program the SMO it is necessary to select the language that is supported by the CLR.

The SMO object model is a hierarchy of objects with the Server object at the top level and all the instance objects residing within the Server object. The ManagedComputer is the top level class and has a separate object hierarchy. It represents Microsoft SQL Server services and network settings that are available through the WMI provider. A number of utility classes represent tasks such as Transfer, backup or Restore. The Model is made up of several namespaces and uses the System.Data.SqlClient object driver to connect to and communicate with different instances of SQL Server.

The SMO client requires SQL Server Native Client that is part of the SQL Server 2005 and .NET Framework 2.0.

Applications in SMO require the installation of Visual Studio 2005.

Transaction processing in SMO is done by using the ServerConnection object which is referenced by the ConnectionContext property of the Server object. Methods such as StartTransaction, RollBackTransaction and CommitTransaction are available to the user.

It is not possible to upgrade a SQL DMO application to SMO. The application will have to be rewritten using SMO classes.

A number of SMO namespaces are not supported by earlier versions of SQL Server. Some of the unsupported namespaces are:

  • Microsoft.SqlServer.Management.NotificationServices
  • Microsoft.SqlServer.Management.Smo.Broker
  • Microsoft.SqlServer.Management.Smo.Mail
  • Microsoft.SqlServer.Management.Smo.RegisteredServer
  • Microsoft.SqlServer.Management.Smo.Wmi
  • Microsoft.SqlServer.Management.Trace

Microsoft.SqlServer.Management.Smo.Agent and Microsoft.SqlServer. Management. Smo namespaces are partially supported. Some classes in Microsoft.SqlServer. Management.Smo provide support for SQL Server 2000 and SQL Server 7.0 while several distributed Management objects have been removed in the transition.

Programming SQL Server Management objects

An instance of SQL Server can be connected in three ways. The ServerConnection object variable can be used to provide connection information or the server object property can be set to provide connection information or the name of the SQL Server can be passed in the Server object constructor.

The advantage of using the first option is that the connection information can be reused. The user declares a Server object variable and then declares a ServerConnection object and sets the properties with connection information such as the name of the instance of SQL Server and the authentication mode. The ServerConnection object variable is then passed as a parameter to the Server object constructor. However, shared connections between different server objects will create problems in this scenario if used simultaneously.

Server object properties can be set explicitly and the constructor can be called. The Server object will try to connect to the default instance of the SQL Server with all the default connection settings.

Finally the SQL Server Instance name can be specified in the Server object constructor by declaring the server object variable and passing the instance name as a string parameter in the constructor. This establishes the connection with the instance of SQL Server with default connection settings.

Closely allied to the above concept is the Connection pooling concept. SMO automatically establishes a connection when required and releases the connection to the connection pool when not required. However, when the connect method is called the connection is not released to the pool automatically and a disconnect method needs to called to perform the operation. Additionally, a non-pooled connection can also be requested using the NonPooledConnection property of the ServerConnection object.

Multi threaded applications require the use of separate ServerConnection objects for each thread.

Setting Default Initialization Fields

When retrieving objects SMO performs optimizations. The optimization minimizes the number of properties loaded by automatically scaling between the different states. In a partially loaded scenario a minimum number of properties are referenced. In a fully loaded instance referenced properties are initialized and made available quickly in the order of speed of loading. Properties which require a large amount of memory are loaded only when specifically required.

The SetDefaultInitFields method can be used to ensure that the application loads only the required properties for optimal performance. This can be reset when required and the resetting can be neutralized using the GetDefaultInitFields when required.

Definitions

A collection is a list of objects that have been constructed from the same object class and share the same parent object. Data is a string that has a defined length, a number that has specific accuracy or a user defined data type that is an object with its own set of rules. The DataType object classifies the type of data so that it can be handled accurately by the SQL Server. The SMO objects that accept data must have a DataType object property as under and the properties must be set.

  • Column
  • UserDefinedDataType
  • UserDefinedType
  • UserDefinedFunctionParameter
  • StoredProcedureParameter
  • UserDefinedFunctionParameter
  • UserDefinedAggregateParameter

The Capture mode has to be enabled to capture and record the equivalent T-SQL statements issued by the programs. This is enabled by using the ServerConnection object or ConncetionContext property of the Server object. The StartTransaction, RollBackTransaction and CommitTransaction methods belong to the ConnectionContext property of the Server object.

Linked servers in SMO are represented by the LinkedServer object. The LinkedServerLogins property references a collection of LinkedServerLogin objects. The logon credentials are stored and help establish the connection with the linked server. OLE DB providers are represented by the OleDbProviderSettings objects.

SMO methods and properties are not designed to report success or failure of a return value. In other words no exceptions are thrown when an error occurs. Errors have to be caught and handled with an exception handler. A number of exception classes have been built into the SMO for this purpose.

« « Using SQL Sever Management Studio – Part 2
SQL Server 2005 – Using the Sqlcmd Utility » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Ads

Free Training

RSSSubscribe 416 Followers
Ads
  • Popular
  • Recent
  • SQL Server 2005 – Using DDL Triggers

    December 12, 2005 - 0 Comment
  • Using XML in SQL Server 2005

    October 26, 2005 - 0 Comment
  • SQL Server 2005 – Using the Database Tuning Advisor

    December 24, 2005 - 0 Comment
  • SQL server 2005 Editions

    November 23, 2005 - 0 Comment
  • SQL Server 2005 – Using Event Notifications

    December 13, 2005 - 0 Comment
  • XML Data Types in SQL Server 2005

    October 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment
  • SQL Server 2005 Installation

    November 23, 2005 - 0 Comment
  • Getting started with SQL Server 2005

    August 9, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 1

    December 15, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2021. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.Accept Reject Read More
Privacy & Cookies Policy
Necessary Always Enabled