Home    Articles    A Quick Introduction to SQL Server Management Objects (SMO)

A Quick Introduction to SQL Server Management Objects (SMO) Thursday, July 29, 2004

Summary
Microsoft® SQL Server™ 2000 supports a COM-based object model, called SQL-DMO, which can be used to programmatically manage the SQL Servers. Enterprise Manager uses this API to manage SQL Server. SQL Server 2005 replaces SQL-DMO with two .NET based object libraries: SQL Server Management Objects (SMO) & Replication Management Objects (RMO). In this short tutorial, I'll show you how you can use SMO in your .NET applications.

(5 printed pages)

SQL Server 2005 introduces new and improved features for both, developers and database administrators. Developers now can write stored procedures using any of the managed .NET language, work with XML and XQuery, Web services-enable their T-SQL code, create scalable asynchronous messaging application, easily create Web-based reports, and so on. For database administrators, two big improvements in SQL Server 2005 include security enhancements and SQL Server Management Studio. Other features include improvements to Profiler, dedicated admin connection, Maintenance Plan Designer, deadlock visualization, SQLiMail, and in integrated MMC-based interface, called SQL Computer Manager that replaces Client Network Utility, Server Network Utility, and SQL Service Manager.

SQL Server Management Studio is a new integrated environment that DBAs can use to manage and administer SQL Servers, Analysis Servers, and Report Servers. A single IDE can be used to manage multiple versions of SQL Server, including SQL Server 2000, 2005, and mobile databases.

If Management Studio does not fit your environment, or does not meet any custom requirements that you or your DBAs might have, or if you need to automate certain management and administration tasks, or if you are a third party vendor interested in integrating SQL Server management capabilities, or if you would like to create a new interface, for example, ASP.NET Web based interface for SQL Server management, you can use SMO in such situations.

Note that SQL Server 2005 continues to ship DMO, but SMO is a recommended API when you are building SQL Server management applications. Especially in your .NET applications, you should use SMO instead of DMO over COM-interop.

SMO, a .NET based object model, ships with SQL Server 2005 in an assembly named Microsoft.SqlServer.Smo.dll. Some other supporting DLLs are also included in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. These include Microsoft.SqlServer.ConnectionInfo.dll and Microsoft.SqlServer.SmoEnum.dll. Like any other assembly, to use SMO in your application, add reference to Microsoft.SqlServer.Smo.dll (and Microsoft.SqlServer.ConnectionInfo.dll) and begin using the SMO classes.

You can use SMO to connect to SQL Server 7, 2000, or 2005. However, there are some objects and properties that only work with SQL Server 2005. These new objects and properties are provided to support new SQL Server 2005 features such as SQL Service Broker, Snapshot Isolation and Row Level Versioning, Table and Index Partitioning, HTTP/SOAP requests, and so on. In addition, SMO have been optimized to provide better performance. Replication specific objects are provided separately in a different assembly named Microsoft.SqlServer.Rmo.dll.

SMO applications can be created using Visual Studio Team System 2005, and can run on Windows NT (SP5 and above), Windows 2000, XP, and Server 2003. SMO applications have dependency on MDAC 9.0 and Microsoft System.Data.SqlClient. Client Tools option in the Microsoft SQL Server 2005 installation can be used to install SMO on a machine.

SMO assembly provides classes categorized in following six namespaces:

With this introduction to SMO, let's now create a small C# Windows Forms application that uses the SMO API to get a list of servers on the network. When a server is selected from the list, it displays databases on that server. For simplicity, we would make trusted connection in this application and get a list of databases. Once a database is selected from the list, it displays properties for that database in a property grid.

You'll need Visual Studio 2005 or Visual C# 2005 Express Edition to build and run the sample code for this article.

try
{
    this.Cursor = Cursors.WaitCursor;

    //  Get a list of SQL servers available on the networks
    DataTable dtSQLServers = SmoApplication.EnumAvailableSqlServers(false);
    
    foreach (DataRow drServer in dtSQLServers.Rows)
    {
        String ServerName;
        ServerName = drServer["Server"].ToString();

        if (drServer["Instance"] != null && drServer["Instance"].ToString().Length > 0)
            ServerName += @"\" + drServer["Instance"].ToString();

        if (comboServer.Items.IndexOf(ServerName) < 0)
            comboServer.Items.Add(ServerName);
    }

    //  By default select the local server
    Server LocalServer = new Server();
    
    String LocalServerName = LocalServer.Name;
    if (LocalServer.InstanceName != null && LocalServer.InstanceName.Length > 0)
        LocalServerName += @"\" + LocalServer.InstanceName;
    
    Int32 ItemIndex = comboServer.FindStringExact(LocalServerName);
    
    if (ItemIndex >= 0)
        comboServer.SelectedIndex = ItemIndex;
}
catch (SmoException smoException)
{
    MessageBox.Show(smoException.ToString());
}
catch (Exception exception)
{
    MessageBox.Show(exception.ToString());
}
finally
{
    this.Cursor = Cursors.Default;
}

try
{
    this.Cursor = Cursors.WaitCursor;

    //  Fill the databases combo
    comboDB.Items.Clear();
    PropertyGridDB.SelectedObject = null;

    Server SelectedServer = new Server(comboServer.Text);
    Int32 DBCount = 0;
    foreach (Database db in SelectedServer.Databases)
    {
        if (CheckSystemDB.Checked)
        {
            DBCount++;
            comboDB.Items.Add(db.Name);
        }
        else if (!db.IsSystemObject)
        {
            DBCount++;
            comboDB.Items.Add(db.Name);
        }
    }

    if (DBCount > 0)
    {
        lblDB.Text = "&Database - " + DBCount + " found. Select one from the following list:";
        comboDB.Enabled = true;
    }
    else
        lblDB.Text = "&Database";

    CheckSystemDB.Enabled = true;
}
catch (SmoException smoException)
{
    MessageBox.Show(smoException.ToString());
    lblDB.Text = "&Database";
    comboDB.Enabled = false;
    CheckSystemDB.Enabled = false;
}
catch (Exception exception)
{
    MessageBox.Show(exception.ToString());
    lblDB.Text = "&Database";
    comboDB.Enabled = false;
    CheckSystemDB.Enabled = false;
}
finally
{
    this.Cursor = Cursors.Default;
}

The Microsoft.SqlServer.Management.Smo.Server class is used to connect to selected local or remote SQL Server. Once connected to the server, Databases property is used to get a list of databases on that server.

On the database selection change event, the above code gets the SMO Database object based on the selected server and selected database. This Database object is passed to the PropertyGrid, which displays the properties.

try
{
    //  Get the database properties for selected database
    this.Cursor = Cursors.WaitCursor;

    Server SelectedServer = new Server(comboServer.Text);
    Database SelectedDatabase = SelectedServer.Databases[comboDB.Text];

    PropertyGridDB.SelectedObject = SelectedDatabase;
    
    DBPropLabel.Text = "Properties for the database: " + comboDB.Text;
}

Click here to download this sample application.

SQL Server Management Objects or SMO is a new .NET based object library introduced in SQL Server 2005. SMO is a replacement for SQL-DMO, plus it is enhanced for performance and to support new SQL Server 2005 features. The replication objects from SQL-DMO are now available in a separate object library, named Replication Management Objects (RMO).

This article briefly explained the SMO concepts and an example was provided that illustrated using SMO in a C# Visual Studio 2005 application. Refer to SQL Server books online for more details on SMO classes.