Retrieving SQL Server 2005 Database Info Using SMO: Basics
(Page 1 of 5 )
This article focuses on retrieving SQL Server 2005 information using SMO together with Visual Basic 2005 and Visual Studio 2005.
A downloadable file for this article is available
here.
The entire demonstration solution has been developed using SQL Server 2005 Enterprise Edition and Visual Studio 2005 Professional Edition on Windows Server 2003 Standard Edition. Note that I didn’t really test the solution on any of other versions/editions of similar suites of Microsoft products.
What the hell are “DMO” and “SMO”?
SQL Distributed Management Objects (SQL-DMO) is a collection of objects encapsulating Microsoft® SQL Server™'s database and replication management. SQL-DMO is a dual interface COM, in-process server implemented as a dynamic-link library (DLL). SQL-DMO encapsulates SQL Server components, presenting the attributes of the component piece to you as the properties of an object instance. We can alter the properties of the instance, or use object methods to automate SQL Server administration, including replication. Now, this whole issue is becoming as an old story.
The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model. Microsoft defines SMO as follows: “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.” In fact, we can consider SMO to be further enhanced than DMO. The most wonderful feature of SMO is that it is implemented as a .NET assembly.
If you would like to work with DMO (or SQL-DMO), we need to work with some of the COM interoperability issues together with .NET (which is a bit complicated in certain cases). Coming to the case of SMO, we can straight away integrate it into any .NET based applications. SMO is also compatible with SQL Server version 7.0, SQL Server 2000, and SQL Server 2005, which makes it easy for you to manage a multi-version environment. In fact, the SMO model supersedes and replaces SQL-DMO. If you are interested in working with SQL-DMO, you can look at another article of mine dedicated to it at http://www.aspfree.com/c/a/VB.NET/Simple-BackupRestore-Utility-With-SQLDMO-
Using-VBNET/1/
To work with SMO, we should learn about the hierarchy of the SMO Object Model. Within the huge hierarchy of the SMO Object model, “server” objects stays at the top. The “server” object generally needs to connect “SQL Server” and from there on, we can retrieve or manage SQL Server database information very easily.
The following sections shall help you to retrieve SQL Server database information using SMO together with Visual Basic 2005. Let’s start now.
Next: Creating the Visual Studio 2005 solution to connect to SMO >>
More MS SQL Server Articles
More By Jagadish Chaterjee
