SQL Server Management Objects (SMO) : part 1
What is SMO?
SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.
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.
- 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,
- 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. Like priter, accesing web.
SMO Vs DMO
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.
Features of SMO
- Cached object model and optimized object instance creation. Objects are loaded only when specifically referenced. Object properties are only partially loaded when the object is created. The remaining objects and properties are loaded when they are referenced directly.(Daynmic Loading and Binding ) this will provide memory manegment and efficent use of SMO objects.
- Batched execution of Transact-SQL statements. Statements are batched to improve network performance.
- Capture Transact-SQL statements. Allows any operation to be captured into a script. Management Studio uses this capability to script an operation instead of executing it immediately.
- Management of SQL Server services with the WMI Provider (Windows Management Instrumentation). SQL Server services can be started, stopped, and paused programmatically.
- Advanced Scripting. Transact-SQL scripts can be generated to re-create SQL Server objects that describe relationships to other objects on the instance of SQL Server.
- Use of Unique Resource Names (URNs). A URN allows you to create instances of and reference SMO objects.
Preparing for SMO use
- SMO Syntax Conventions: SQL Server Management Objects (SMO) typographical conventions are based on those used in Microsoft Visual Studio .NET reference materials.( Transact-SQL statements, macro names, and terms used at the operating system level; file names in text.)
- System Requirements: SQL Server Management Objects (SMO) uses the Microsoft System.Data.SqlClient object driver to connect to and communicate with instances of SQL Server. i.e SQL Server Native Client, which is included with SQL Server and the .NET Framework 2.0. , Visual Studio (2005 or 2008).
- Visual Basic .NET
- Visual C# .NET
Creating SMO Programs
General programming of SQL Server Management Objects (SMO) objects includes the common areas that all objects share, such as running methods, setting properties, and manipulating collections.
Stapes Creating a Visual C# SMO project in Visual Studio.NET
- Start Visual Studio 2005 or 2008.
- On the File menu, click New Project. The New Project dialog box appears.
- In Project Types dialog box, select Visual C# Projects. In the Templates pane, select Windows Application.
- (Optional) In Name, type the name of the new application
- Select the Visual C# application type. For the examples that follow select Console Application.
- On the Project menu, select Add Reference. The Add Reference dialog box appears.
- Select Browse, locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\ and select the following files:
- Add the additional SMO assemblies if you are specifically programming Service Broker or WMI:
- Click Open.
- On the View menu, click Code.-Or-Select the Program1.cs [Design] Windows and double click the windows form to display the code window.
- In the code, before the namespace statement, type the following using statements to qualify the types in the SMO namespace:
- using Microsoft.SqlServer.Management.Smo;
- using Microsoft.SqlServer.Management.Common;
- SMO has various namespaces under Microsoft.SqlServer.Management.Smo, such as Microsoft.SqlServer.Management.Smo.Agent. Add these namespaces as needed.
- You can now add your SMO code.
in next part i will tell about how to write SMO program..........................