Gathering Metrics with SMO

,

One of the most important tasks you can do as a DBA is to gather metrics on the servers you manage. From processor information, amount of memory and database file and usage sizes to server version and service pack level, having current and historical information helps you make important decisions that will keep your servers running at peak performance and minimize downtime. Server Management Objects (SMO) provides a programming object library that allows you to capture this kind of data from your servers, allowing you to build a database of server data, from which you can make these decisions.

We'll build a VB.Net console application to gather details from a defined set of servers. The first thing you'll need to do is to select Add Reference from the Project menu. Within the Add Reference dialog, use the Ctrl key to select the following assemblies (you may have to browse to the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder):

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.SqlEnum.dll

Next, in the code window at the top of the code, before any declarations, insert the following lines:

Imports Microsoft.SqlServer.Management.SMO
Imports Microsoft.SqlServer.Management.Common

Because it's most likely you manage multiple servers, your best plan is to build a means by which your solution can access all your servers. You can use many different ways to accomplish this, and I chose to build an XML file with the servers to be managed.

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Servers>
    <Name>SQLHASP1</Name>
    <Server>SQLHASP1</Server>
    <Integrated>true</Integrated>
  </Servers>
  <Servers>
    <Name>SQLHASP1\SQLDEV01</Name>
    <Server>SQLHASP1</Server>
    <Instance>SQLDEV01</Instance>
    <Integrated>true</Integrated>
  </Servers>
  <Servers>
    <Name>SQLHASP1\SQLDEV02</Name>
    <Server>SQLHASP1</Server>
    <Instance>SQLDEV02</Instance>
    <Integrated>true</Integrated>
  </Servers>
</NewDataSet>

This file, stored as c:\Demos\servers.xml, can be easily read into a DataTable using the following lines of code, which are inserted after the Sub Main() in the code window:

Dim dsServer As New DataSet
Dim tblServerName As New DataTable
dsServer.ReadXml("C:\Demos\servers.xml")
tblServerName = dsServer.Tables(0)

At this point you can iterate through your defined servers with the following code:

Dim rowServer As DataRow
For Each rowServer In tblServerName.Rows
Next

Within the for/next loop we'll collect information on each server. The first thing we want to get is the Information at the server level. SMO provides an Information object for the server which provides many details we need. The first thing we need to do is to connect to the server using the information from the DataRow:

Dim strSrvName As String
strSrvName = rowServer(0)
Dim srvMgmtServer As Server
srvMgmtServer = New Server(strSrvName)
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = rowServer("Integrated")
If srvConn.LoginSecure = False Then
srvConn.Login = rowServer("Login")
srvConn.Password = rowServer("Password")
End If

Next, we'll define variables to hold the system information and pull that information from the properties in the Information object.

Dim intNbrProcs As Integer
Dim strSysMemory As String
Dim strServerVersion As String
Dim strEdition As String
Dim strVersion As String
Dim strLevel As String
strSysMemory = srvMgmtServer.Information.PhysicalMemory
intNbrProcs = srvMgmtServer.Information.Processors
strServerVersion = srvMgmtServer.Information.OSVersion
strVersion = srvMgmtServer.Information.VersionString
strLevel = srvMgmtServer.Information.ProductLevel
strEdition = srvMgmtServer.Information.Edition

At this point you can store these values into your database along with a current datetime value, and a review of these rows over time will allow you to track when changes have occurred on your servers.

The rest of the information we want to capture is at the database level, so we need to iterate through the databases on the server. We do that this way:

Dim lisDBs As DatabaseCollection
lisDBs = srvMgmtServer.Databases
Dim objDB As Database
For Each objDB In lisDBs

Next

At the database level we want to capture details about the files used by each database, and we want to summarize the total space allocated and used by the database and the log for each database. In my case I don't want to capture information on system databases, or for snapshot databases (because snapshot databases are a special case and use special NTFS sparse files, not normal filesystem files. Notice how we have to check to make sure the database is a SQL Server 2005 database before checking to see if it's a snapshot database. If we don't do this Visual Basic will throw an error.) I use the following code to filter these databases out:

Dim blnProcessDB As Boolean
blnProcessDB = True
If objDB.IsSystemObject Then
blnProcessDB = False
End If
If (objDB.CompatibilityLevel = CompatibilityLevel.Version90) Then
If objDB.IsDatabaseSnapshot Then
blnProcessDB = False
End If
End If
If blnProcessDB = True Then
End If

So, the database information collection process goes inside this last IF statement block.

There's a difference between the way the information is stored for data files versus log files in SQL Server. Because data files are defined within file groups we have to browse the FileGroups collection for the database before we can get to the actual files, but there are no file groups defined for log files. We'll use these objects to work through them:

Dim objFileGroups As FileGroupCollection
Dim objFileGroup As FileGroup
Dim objLogFiles As LogFileCollection
Dim objLogFile As LogFile

We define counters for the size information we want to capture, and we loop through the file groups and files, writing the information for the physical files as we go, and at the end we write the size and usage values to our database.

Dim strDBName As String
Dim fltDBSpace As Double
Dim fltDBUsed As Double
Dim fltLogSpace As Double
Dim fltLogUsed As Double
strDBName = objDB.Name
fltDBSpace = 0
fltDBUsed = 0
fltLogSpace = 0
fltLogUsed = 0
objFileGroups = objDB.FileGroups
For Each objFileGroup In objFileGroups
Dim objDataFiles As DataFileCollection
Dim objDataFile As DataFile
Dim fltFGSpace As Double
Dim fltFGUsed As Double
fltFGSpace = 0
fltFGUsed = 0
objDataFiles = objFileGroup.Files
For Each objDataFile In objDataFiles
Dim strFileName As String
Dim strLogName As String
Dim fltDFSpace As Double
Dim fltDFUsed As Double
strFileName = objDataFile.FileName
strLogName = objDataFile.Name
fltDFSpace = objDataFile.Size
fltDFUsed = objDataFile.UsedSpace
fltFGSpace += fltDFSpace
fltFGUsed += fltDFUsed
'Write the physical file info for your data files
Next
fltDBSpace += fltFGSpace
fltDBUsed += fltFGUsed
Next
objLogFiles = objDB.LogFiles
For Each objLogFile In objLogFiles
Dim strFileName As String
Dim strLogName As String
Dim fltDFSpace As Double
Dim fltDFUsed As Double
strFileName = objLogFile.FileName
strLogName = objLogFile.Name
fltDFSpace = objLogFile.Size
fltDFUsed = objLogFile.UsedSpace
fltLogSpace += fltDFSpace
fltLogUsed += fltDFUsed
'Write the physical file info for your log files 
Next
'Write the database space usage values 

By recording this information to your metrics database you can monitor growth trends and anticipate when you're going to need additional disk for your databases, making you a more effective administrator.

###

Allen is the Microsoft SQL Server database administrator for Advanstar Communications, Inc., which publishes trade magazines and produces trade shows for the healthcare, off-road sports, and fashion industries. His certifications include MCITP: Database Administrator, MCITP: Database Developer, MCTS: SQL Server 2005, and is a Microsoft Certified Trainer. He will be presenting a session called DBA-201: Using SMO to Create Custom SQL Server Management Solutions at the 2006 PASS Community Summit.

Rate

Share

Share

Rate