Programming SMO applications for improved management, automation

SQL Server Management Objects can be very helpful when used with (or even instead of) T-SQL, particularly when it comes to monitoring database backups and space utilization.

SQL Server Management Objects (SMO) are a collection of .NET code libraries containing API classes for accessing and working with all areas of Microsoft SQL Server. Many of the native SQL Server tools such as SQL Server Management Studio (SSMS) utilize these classes. You can use SMO to build your own management utilities to either complement the native tools or to automate certain tasks.

Alerte Email

Inscrivez-vous pour recevoir régulièrement toute l’actualité IT.

Safe Harbor

Before you can use the SMO libraries in Microsoft Visual Studio, you have to explicitly add references to the SMO assemblies. Here is a list of the core libraries you should include in order to successfully compile the code in this article:

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.ConnectionInfoExtended
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
  • Microsoft.SqlServer.SqlEnum

When you work with SMO, you typically start by connecting to a SQL Server instance using the Server object. Once you have a reference to the server, you can start using the server properties or begin drilling into child objects like databases. This code example connects to a server and gets the number of connections connected to the master database:

Server svr = new Server("rrehak");
int connectionCount = svr.GetActiveDBConnectionCount   ("master");

The above code passes the server name in the constructor. If you want to connect to unknown servers or perform an operation on all servers on your network, you can make a network call to get a list of SQL Server instances on the computer or network first using the SmoApplication class:

DataTable dt = SmoApplication.EnumAvailableSqlServers   (false);

The returned DataTable object contains several properties for each SQL Server found, such as computer name, instance name, version, and whether the server is local and clustered or not. Please note that in order for a server to be included in the results, the SQL Server Browser windows should be running on the computer where the SQL Server instance is installed. Based on my experience, however, it seems the server may not show up even if the browser service is running or vice versa. It all depends on the network and firewall setup, so your best bet is to compile your own server list if you want to automate operations on multiple servers.

Checking on backups with SMO

One example of how SMO is useful is when you want to check your databases to make sure backups are taking place. While it’s easy to configure maintenance plans and SQL Server Agent jobs to send notifications in case a backup fails, it’s much harder to configure them to let you know if a backup didn’t happen. Such a scenario is very possible if somebody shuts down the SQL Server Agent, and of course your notification system can always fail too. Also, while many DBAs are rigid about setting up backups on production servers, they often neglect to do the same on development and QA servers, exposing themselves to losing many hours of work.

The following code demonstrates how to retrieve last backup dates (full, differential and log) for a specified database:

Server svr = new Server("rrehak");
Database db = svr.Databases["AdventureWorks2008"];

string fullBackupDate = FormatBackupDate
   (db.LastBackupDate);
string diffBackupDate =
   FormatBackupDate(db.LastDifferentialBackupDate);
string logBackupDate = FormatBackupDate
   (db.LastLogBackupDate);

If a particular backup hasn’t happened yet, the value in the corresponding backup date property in SMO is “1/1/0001”. I wrote the FormatBackupDate() function you see in the previous code sample to convert the date to a string, or to return “None” if the particular type of backup has not occurred:

private stringFormatBackupDate(DateTime date)
{
   DateTime nullDate = new DateTime(1,1,1);

            if (date != nullDate)
        return date.ToString();
      else
        return "None";
    }

Keeping an eye on space

Aside from backups, database space utilization is another area that DBAs need to monitor and sometimes take action if the amount of free space gets too low. Again, SMO makes it fairly easy by exposing all needed numbers as properties, although you need to do some calculations and conversions along the way. The values in some size properties is listed in megabytes, while others use kilobytes. That’s why the code divides by 1024 in some cases.

You start by declaring some variables and connecting to a database:

double dbLogTotalSize = 0;
double dbLogUsedSpace = 0;

Server svr = new Server(@"rrehak");
Database db = svr.Databases["AdventureWorks2008"];

Next, you need to loop through the collection of log files and add up their sizes and free space because there are no database properties containing these totals:

foreach (LogFile lf in db.LogFiles)
{
   dbLogTotalSize += lf.Size / 1024; // convert to MB
   dbLogUsedSpace += lf.UsedSpace / 1024;
}

txtLogSize.Text = dbLogUsedSpace.ToString("N2") +
   "/" + dbLogTotalSize.ToString("N2") + " MB used";

After that you can display database details. Note that you will need to subtract the total size of the transaction log calculated in the example above from the total database size to get the total size of the data files:

double dbTotalSize = db.Size - dbLogTotalSize;//subtract
   log size

double dbUsedSpace = dbTotalSize - db.SpaceAvailable/1024;

txtDatabaseSize.Text = dbUsedSpace.ToString("N2") +
   "/" + dbTotalSize.ToString("N2") + " MB used";

A sample output would be something like “22.56/50.00 MB used.”

When to use SQL Server Management Objects

Those are some SMO basics and potential areas of use. So when does it make sense to use SMO instead of or in addition to using T-SQL? Here are a few examples:

  • Monitoring applications (server uptime, database space, backups)
  • Applications that generate SQL Server documentation (servers, settings, databases)
  • Scripting automation (many objects exposed in SMO are scriptable with a variety of options)
  • Applications that collect and store server and database trends and statistics
  • Programming management and maintenance utilities for SQL Server Express to compensate for the lack of SSMS and SQL Server Agent

Unfortunately, the SMO documentation in SQL Server Books Online is very dry and contains just basic descriptions of the classes. It includes no overview, suggestions for using SMO, or best practices. Fortunately, there are many useful code samples on the CodePlex website that you can download, explore and utilize in your own SMO applications.

ABOUT THE AUTHOR
Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.

This was first published in July 2010

Dig deeper on Microsoft SQL Server Tools and Utilities

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close