Operations Manager

Operations Manager 2007, aka System Center Operations Manager, aka MOM v2, is a great enterprise monitoring tool from Microsoft. This tool, out of the box, finds all the servers on your system and starts collecting data on them, providing alerts, and performance history and all kinds of other functionality. Adding the SQL Server Management Pack can really make the system sing. Once you see all the data that Operations Manager collects automatically, you may really start to think, I did, "Well, what else can I get?" The first thing that came up in our shop was keeping track of the individual files on a database. Not simply the free space available to the file, which can be retreived with a performance counter, but the space used and the allocated size were to be collected and stored for reporting over time. It was a great idea. We had a procedure ready to pull it all together:

SELECT [name] AS LogicalName
 , / 128.0 AS TotalSizeInMB
 ,FILEPROPERTY([name], 'SpaceUsed') / 128.0 AS SpaceUsedInMB
 ,( - FILEPROPERTY([name], 'SpaceUsed')) / 128.0 AS FreeSpaceInMB
FROM sys.database_files

I decided to just plug this in to Operations Manager so we could report on this data for all our databases with no real work or effort required. Thus began a five day learning experience. The documentation in Operations Manager is just a bit thin on the ground. What's more, it doesn't cover every topic and it doesn't cover every topic completely. It really came down to finding all the places where something had been done before and attempting to replicate it. To start with, the very best information I've found on the web is the System Center Forum. But I wasn't able to find very much in the way of database specific examples. For these I went to the Management Pack for SQL Server supplied by Microsoft as outlined in Scott Abrants article. Finally, I found enough information that I was able to make it work.

Collecting Data

Data collection in SCOM is through Rules. So we'll need to create a new one. Through the Authoring area in Operations Manager, to look at SQL Server Databases, you need to navigate to "Rules" and then down to the "SQL 2005 DB" type. Right clicking you can start the Create Rule Wizard:

To get all the data from the file that we want, we can't simply use a performance counter. So we will need to write a small VBS Script (or JScript). Don't make the mistake I did. Right there in front of you in the "Time Commands" is the "Execute a Script" rule. This doesn't collect data. Actually you want to create a "Probe Based" script and specifically the "Script (Performance)" type. Once that's selected you can pick the destination management pack then click "Next." Here you'll determine a few general settings:

Name it whatever you want. Just remember that you'll need to find it mixed in among all the other rules. I usually name the rules I create SQL2005 (or SQL2000) followed by something descriptive for what that rule does. Be sure that the "Rule Category" is set to "Performance Collection" and that the "Rule Target:" is the database type you want, in this case, "SQL 2005 DB." Click Next again:

Obviously the schedule is really dependent on the type of data being collected and the purpose of the collection. The default, above, is every 15 minutes. For our purposes we changed it to once a day. Click Next again. Which brings us to the meat:

You can type scripts straight into this window if you're so inclined. I find editing the scripts in Visual Studio to be somewhat easier. It also gives you the opportunity to debug the script prior to trying to run it in OpsMgr (see here and here and here for debug tips). Give the file a unique name and get the script into the window.

The Script

Included at the end of the article is the complete script and the XML from my management pack will be attached as customrule_pm.txt (I still haven't learned how to export Management Packs, cut me slack). Also, this script has no error trapping, so you may want to introduce a bit of that prior to releasing it into the wild of your production servers.

This is straight VBS with few surprises. First I need to establish a connection to my database and instantiate OpsMgr scripting:

If WScript.Arguments.Count = 2 then
Dim oAPI
Set oAPI = CreateObject("MOM.ScriptAPI")

'Connect to the database
Dim cnADOConnection
Set cnADOConnection = CreateObject("ADODB.Connection")
cnADOConnection.Provider = "sqloledb"
cnADOConnection.ConnectionTimeout = 15
Dim ConnString
ConnString = "Server=" & WScript.Arguments(0) & ";Database=" & WScript.Arguments(1) & ";Integrated Security=SSPI"
cnADOConnection.Open ConnString

As you can see, I check that I've got two parameters, more on that in a minute, create the MOM scripting object (MOM is the previous name for Operations Manager and it's still evident in some places in the code). From there it's a straight, traditional database connection using SQLOLEDB. The parameters are used to define the server and database for the connection.

After the connection I have to run my query from above:

Dim oResults
Set oResults = cnADOConnection.Execute("SELECT [name],[physical_name],/128.0 as TotalSizeInMB,FILEPROPERTY([name], 'SpaceUsed')/128.0 asSpaceUsedInMB,( - FILEPROPERTY([name], 'SpaceUsed'))/128.0 asFreeSpaceInMB,CASE WHEN is_percent_growth = 1 THEN CAST(growth as VARCHAR(8)) + '%' ELSE CAST(FLOOR(growth/128.0) as VARCHAR(8)) + 'MB' END as growth, [is_read_only] FROM sys.database_files ORDER BY [Name] ASC")

Once more, this is straight VBS. No surprises for anyone familiar with scripting and how to execute a query from a VBS script. But now, I've got to process the results of the query that returned into the result set. This means creating a Property Bag. These are the objects that Operations Manager will use to translate into performance information that will be available through all the other parts of the Operations Manager tool suite. It's basically a collection of name/value pairs where the name is unique. In order for us to group the data and because we're returning multiple values, we have to create mulitple Property Bags. Thanks to the article on System Center Forum, I can do this:

Dim oBagTotalSize
Dim oBagSpaceUsed
Dim oBagFreeSpace

'Walk the result set
Do While Not oResults.EOF
Set oBagTotalSize = oAPI.CreateTypedPropertyBag(2)
call oBagTotalSize.AddValue("DBName",Cstr(WScript.Arguments(1)))
call oBagTotalSize.AddValue("LogicalName" ,CStr(oResults(0)))
call oBagTotalSize.AddValue("TargetName","TotalSizeInMb")
call oBagTotalSize.AddValue("perfValue",Cstr(oResults(2)))
oAPI.AddItem(oBagTotalSize)

Set oBagSpaceUsed = oAPI.CreateTypedPropertyBag(2)
call oBagSpaceUsed.AddValue("DBName",Cstr(WScript.Arguments(1)))
call oBagSpaceUsed.AddValue("LogicalName" ,CStr(oResults(0)))
call oBagSpaceUsed.AddValue("TargetName","SpaceUsedInMb")
call oBagSpaceUsed.AddValue("perfValue",Cstr(oResults(3)))
oAPI.AddItem(oBagSpaceUsed)

Set oBagFreeSpace = oAPI.CreateTypedPropertyBag(2)
call oBagFreeSpace.AddValue("DBName",Cstr(WScript.Arguments(1)))
call oBagFreeSpace.AddValue("LogicalName" ,CStr(oResults(0)))
call oBagFreeSpace.AddValue("TargetName","FreeSpaceInMb")
call oBagFreeSpace.AddValue("perfValue",Cstr(oResults(4)))
oAPI.AddItem(oBagFreeSpace)

oResults.MoveNext
Loop
cnADOConnection.Close

'return the property bag objects
Call oAPI.ReturnItems

I create three property bags for the three types of information that I'm returning and then walk through the result set, creating the bag, setting the information inside of it, and then adding it to a collection within the OpsMgr scripting object. Finally I close the connection and return all the property bags, no matter how many there are.

You can execute the script yourself (if you followed the debugging links above) through cscript.exe with a set of parameters you define, like this:

cscript.exe get2005fileszie.vbs "MyServer\MyInstance" "model"

It returns XML as output that represents the collection of property bags you created (attached as customrule_property.txt).

Parameters

Parameters are defined by clicking on the "Parameters..." button visible on the scripting window above. Since our intent is to run this script on every database on a given server, we'll need the server and instance as well as the database name. OpsMgr provides context parameters which means that as it runs through the servers checking whatever it is that it checks, parameters are available in context. The two we need are here:

$Target/Host/Property[Type="SQLServer!Microsoft.SQLServer.DBEngine"]/ConnectionString$ $Target/Property[Type="SQLServer!Microsoft.SQLServer.Database"]/DatabaseName$

The parameter is wrapped in "$" and clearly defines a path within the context of the OpsMgr process. Of particular interest is the first parameter, "ConnectionString." When you think of connection strings, you would think of the server, instance and database, but in fact, from browsing Microsofts SQL Server Management Pack, you find that "ConnectionString" is simply the server name. I was not able to find any definitions for SQL Server context parameters in Operations Manager 2007 documentation anywhere. Your best source will be experimentation or browsing through other management packs, like the one provided by Microsoft as outlined in Scot Abrants' article. You've already seen how these parameters are pulled into the script.

With the parameters defined, we're almost done.

Mapping

Simply filling in the property bags in the script is not enough. You have to tell Operations Manager what to do with the values collected. Comparing the settings above with the code, you can see how the property names are created as the property bags are filled out:

call oBagFreeSpace.AddValue("TargetName","FreeSpaceInMb")
call oBagFreeSpace.AddValue("perfValue",Cstr(oResults(4)))

So in the example above I designate one proprety in the bag as the "FreeSpaceInMb" which is the name of the column I'm collecting. Next I load the value, defined as "perfValue," from the column in my query at ordinal position 4. These two values are then pulled from the property bag object for storage in the Operations Manager database by the following two strings:

$Data/Property[@Name='TargetName']$
$Data/Property[@Name='perfValue']$

Conclusion

I'm posting this because I couldn't find a good sample that showed everything needed to transform TSQL into data in Operations Manager. Instead I had to go spelunking through unrelated documentation, web sites with incorrect or incomplete information and other code samples until I could pull together the various pieces. I've stated it several times in this short article, but I'll repeat myself: your best source of information right now is the web site System Center Forum. Now there is a single working example showing exactly what I needed because I think others will need it too.

Once you have all the information in front of you, this is a very simple process. I put a lot of work into creating this initial script, configuring it and getting the rule running in Operations Manager. The second custom process I wrote, the same thing only for SQL Server 2000, took well less than 1/2 hour and it only took that long because I had a typo in my code.

Complete Script

' Param 0: The SQL connection string for the server
' Param 1: The Database to use
Option Explicit

If WScript.Arguments.Count = 2 then
Dim oAPI
Set oAPI = CreateObject("MOM.ScriptAPI")

'Connect to the database
Dim cnADOConnection
Set cnADOConnection = CreateObject("ADODB.Connection")
cnADOConnection.Provider = "sqloledb"
cnADOConnection.ConnectionTimeout = 15
Dim ConnString
ConnString = "Server=" & WScript.Arguments(0) & ";Database=" & WScript.Arguments(1) & ";Integrated Security=SSPI"
cnADOConnection.Open ConnString

'Connection established, now run the code
Dim oResults
Set oResults = cnADOConnection.Execute("SELECT [name],[physical_name],/128.0 as TotalSizeInMB,FILEPROPERTY([name], 'SpaceUsed')/128.0 as SpaceUsedInMB,( - FILEPROPERTY([name], 'SpaceUsed'))/128.0 as FreeSpaceInMB,CASE WHEN is_percent_growth = 1 THEN CAST(growth as VARCHAR(8)) + '%' ELSE CAST(FLOOR(growth/128.0) as VARCHAR(8)) + 'MB' END as growth, [is_read_only] FROM sys.database_files ORDER BY [Name] ASC")

Dim oBagTotalSize
Dim oBagSpaceUsed
Dim oBagFreeSpace

'Walk the result set
Do While Not oResults.EOF
Set oBagTotalSize = oAPI.CreateTypedPropertyBag(2)
call oBagTotalSize.AddValue("DBName",Cstr(WScript.Arguments(1)))
call oBagTotalSize.AddValue("LogicalName" ,CStr(oResults(0)))
call oBagTotalSize.AddValue("TargetName","TotalSizeInMb")
call oBagTotalSize.AddValue("perfValue",Cstr(oResults(2)))
oAPI.AddItem(oBagTotalSize)

Set oBagSpaceUsed = oAPI.CreateTypedPropertyBag(2)
call oBagSpaceUsed.AddValue("DBName",Cstr(WScript.Arguments(1)))
call oBagSpaceUsed.AddValue("LogicalName" ,CStr(oResults(0)))
call oBagSpaceUsed.AddValue("TargetName","SpaceUsedInMb")
call oBagSpaceUsed.AddValue("perfValue",Cstr(oResults(3)))
oAPI.AddItem(oBagSpaceUsed)

Set oBagFreeSpace = oAPI.CreateTypedPropertyBag(2)
call oBagFreeSpace.AddValue("DBName",Cstr(WScript.Arguments(1)))
call oBagFreeSpace.AddValue("LogicalName" ,CStr(oResults(0)))
call oBagFreeSpace.AddValue("TargetName","FreeSpaceInMb")
call oBagFreeSpace.AddValue("perfValue",Cstr(oResults(4)))
oAPI.AddItem(oBagFreeSpace)

oResults.MoveNext
Loop
cnADOConnection.Close

'return the property bag objects
Call oAPI.ReturnItems
End If