Operations Management for BizTalk published in BizTalk Hotrod

I just found out the latest BizTalk HotRod to hit the web (February 2009) does include an article I did on Systems Center Operations Manager (SCOM) and BizTalk. Go here to get a copy of the article: http://biztalkhotrod.com/Documents/BizTalk%20HotRod%20Magazine%20Q1%202009.pdf. This is my first published article so it is exciting for me. This is partially based on my Operations Management for BizTalk presentation from about a year ago.

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.

BizTalk & SCOM Presentation

I just got back from my presentation on using SCOM/MOM for managing the supporting infrastructure of BizTalk. I presented today at the Midwest Connected Systems User Group (formerly Midwest BizTalk Users Group) in Downers Grove, IL (near Chicago). The website for the usergroup is www.mwbug.org. Here is a summary of the presentation:
BizTalk Server 2006 includes many features for handling server diagnostics, issue resolution and message management. Did you know that BizTalk also provides a specialized management pack for use with Microsoft Operations Manager or Systems Center Operations Manager? This management packs provides additional features for tracking exceptions for BizTalk artifacts such as ports or orchestrations. Ben Cline will describe these capabilities and present how they may be leveraged for managing a BizTalk environment.

Blog at WordPress.com.

Up ↑