Programming SMO applications for improved management, automation
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.
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in July 2010
- 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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation