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.
