SCOM runs SQL in the SQL Management Pack

As part of a couple projects I have gained experience working with Systems Center Operations Manager (SCOM) as part of working with IT departments who needed to manage BizTalk, Active Directory, and SQL Server. One request I recently got was to determine how SQL actually gets executed as part of the SQL Server management pack. So the first thing I needed to do was to load the SQL Management pack into SCOM and then explore the Task artifacts to determine which ones were executing SQL. You would think that there would be quite a few of them for DBA-centric tasks. Along the way of doing this I noticed a few gotchas and wanted to point these out. Catch the end of this post to see the results of my probing into the management pack.

 

When installing the SQL management pack download, an MSI runs to export a copy of all of the management pack files (*.mp) that need to be loaded into SCOM. If you search on the management pack catalog (http://technet.microsoft.com/en-us/systemcenter/cc462790.aspx), it comes back with the same management pack for SQL 2000, 2005, and 2008. But when running the installer it does not created any 2008-specific management packs, only 2000 and 2005 ones so this is a little misleading. The MSI creates 5 different management pack files, the SQL Core Library, the 2000 discovery pack, the 2000 monitoring pack, the 2005 discovery pack, and the 2005 monitoring pack. The core library should be loaded first, then one or both discovery packs, and finally one or both monitoring packs. Most managment pack installs just create a single .mp file so be sure to run the additional packs to get all of the management artifacts!

 

I loaded the management packs into SCOM and then started searching for where SQL may be executing based on the SQL management pack. There are a couple of different things to look for in a management pack such as alerts, monitors, tasks, rules, etc. Typically tasks are used for executing scripts, starting Windows services, or other command-line executable tasks. I ended up only finding a single instance of specific SQL being executed from the SQL Management pack:

sqlcmd.exe -E -S $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$ -Q "sp_configure"

 

One thing that is interesting about this command is that it pulls out a property from for the connection string in what looks like a Powershell variable and then executes a call to sp_configure. This provides an example of what would be required to create a custom task that executes SQL. Only finding a single task was surprising – I was expecting that there would be one for updating indexes based on the results of SQLProfiler, or other common administration tasks. Alas, I will need to create a custom management pack to do these kinds of things. At least this example shows me how easy it would be to create a task to execute some custom SQL.