• Sidebar: Built on SQL Server 2005 Reporting Services
• Illustration: Web Reports
• Illustration: SCRM Architecture

A release candidate of System Center Reporting Manager (SCRM) 2006, Microsoft's new tool for analyzing and reporting systems management data, is available for evaluation. By combining performance and event data extracted by Microsoft Operations Manager (MOM) 2005 with inventory data extracted from Systems Management Server (SMS) 2003, SCRM is designed to help IT departments and managers make better operational and planning decisions. However, the first version of SCRM can't be extended to include additional data elements or other sources of management data, and it will also be incompatible with the next releases of MOM and SMS.

System Center Product Integration Strategy

After Microsoft revealed a plan in 2003 to merge MOM and SMS into a single management product called System Center, customers made it clear that they didn't see enough value in a consolidated product to offset the design and transition problems created by merging the two very different products. In response, Microsoft decided to steer away from the traditional consolidated management product model toward what it describes as a "federated" model. Microsoft now plans to maintain MOM and SMS as separate products, each with operational data stored in its own SQL Server database, and will soon offer a third product, SCRM 2006, to warehouse data extracted from both products and generate reports from that data.

This model is intended to give customers more choice—they can use the Microsoft management products that best meets their needs without overbuying—and to give Microsoft the flexibility to upgrade each individual product on its own release cycle. Furthermore, the federated model leaves open the possibility of integrating and correlating data from other management data sources, such as Microsoft's System Center Capacity Planner 2006 and System Center Data Protection Manager 2006 (a hard disk—based backup and restore facility), or data from third-party network management products, such as Hewlett-Packard's OpenView, or help desk management systems, such as BMC Remedy.

However, the transition to this model will be awkward, as SMS 2003 and MOM 2005 each have their own built-in reporting systems, yet both will undergo substantial change as Microsoft redesigns them to meet the goals of its Dynamic Systems Initiative (DSI), the company's long-term plan for "baking" management into all system components, including applications.

Better Together, Useful with Either

Combining MOM event and performance data with SMS inventory data can help identify the root cause of a problem. For example, when a change is made to one or more servers, such as installing a software patch or upgrade, SCRM reports can help administrators correlate such changes with server performance degradation or certain event log errors. SCRM can also help determine whether a correlation exists between certain types of events and various types of server hardware.

However, SCRM can be a useful complement to the reporting systems in SMS or MOM even when organizations use only one of those products.

SMS only. SMS's operational database holds a great deal of detailed inventory and metering data, but by default SMS keeps it for only 90 days. When SCRM is used with SMS, a subset of that data is moved to the SCRM data warehouse and then held for longer periods (the default is a year), making it available for reporting longer-term trends. Furthermore, because SCRM reports run against a copy of the data on a separate server (rather than the operational SMS database), reports will display faster without impacting SMS server performance. Although SMS 2003 offers limited reporting customizability, because SCRM is based on SQL Server 2005 Reporting Services any programmer familiar with it can build many types of custom reports. (For more information about SQL Server Reporting Services integration, see the sidebar "Built on SQL Server 2005 Reporting Services".)

MOM only. MOM 2005's built-in reporting system is very similar to that of SCRM—they both load data into a data warehouse separate from MOM's operational database, and both use SQL Server Reporting Services to generate reports. However, while SCRM may seem redundant if the organization is not using SMS, large organizations that use multiple MOM systems still benefit from SCRM: although each MOM 2005 data warehouse can extract data from only a single MOM operational database, SCRM can consolidate data from multiple MOM databases into reports that cover the organization's entire server infrastructure.

Since SCRM 2006 contains a full superset of MOM 2005's reporting system, it is possible that Microsoft will remove the current MOM reporting system from future versions of MOM. It also seems unlikely that Microsoft will continue to use the SMS-specific Web report engine now that it offers SQL Server Reporting Services. However, future versions of MOM and SMS will still contain features that enable IT personnel to query live operational data.

SCRM 2006 Components and Architecture

SCRM consists of a SQL Server 2005—based data warehouse, a set of predefined SQL Server Data Transformation Service (DTS) jobs to copy data from the SMS and MOM operational databases into the SCRM data warehouse, a scheduling engine to periodically run the DTS jobs, and report definition files for various types of system management reports.

Data warehouse. Installing SCRM 2006 creates a data warehouse (a type of database typically used to store and summarize large amounts of historical data). This data warehouse has a schema specifically designed to accept data from SMS 2003, MOM 2005, and Active Directory (AD) data sources. Although the underlying data schema is not published, SCRM 2006 includes SQL Server views with a published presentation schema so that developers can write custom reports or modify the reports included with the product.

SMS agent. SCRM 2006 includes an agent that must be installed on the organization's SMS Central Site Servers (servers that write to SMS's SQL Server database). This agent periodically runs SQL Server's bulk copy (bcp) utility to extract selected data elements from the SQL Operational database into a bcp file.

DTS scripts. Data Transformation Services (DTS) are a set of SQL Server APIs and developer tools for creating extract, transform, and load (ETL) scripts and are used to populate data warehouses with data extracted from operational systems. SCRM 2006 includes two DTS scripts that load data from the bcp files on one or more SMS servers (one script does a full import, while the other imports changed data only.) SCRM also includes a DTS script to load operational data from SQL Server databases on the MOM 2005 servers.

In addition, SCRM 2006 includes DTS jobs for grooming the warehouse's MOM and SMS data, which eliminates duplicate data and ages out data older than the configured interval.

Scheduling engine. SCRM has a scheduling engine that triggers the DTS jobs at preconfigured intervals, which can be set to be as frequent as the customer needs.

Report definitions. The release candidate of SCRM comes with 11 pre-authored report definitions—XML files that tell SQL Reporting Services how to query the data warehouse and display the results in HTML. After these files are imported into Reporting Services, Reporting Services makes them available to users by publishing them on an ASP.NET-based Web portal hosted on the SCRM server. (For an illustration of MOM data displayed on a Web portal, see "Web Reports".)

In addition to including all of the 80+ MOM 2005 Reporting Services reports, SCRM 2006 includes 55 new reports that report on SMS data or combine SMS and MOM data. Listed below are some examples of the reports included with SCRM 2006:

• Hardware Purchase Trend Report by Vendor
• Software Distribution Error Hotspots
• Software Update Compliance Over Time for a Specific Update
• Underutilized Computers

(For an illustration showing the various SCRM components, see "SCRM Architecture".)

Significant Limitations in First Release

Although SCRM 2006 shows potential and leverages Microsoft's investment in SQL Server 2005 Reporting Services, as a version one product it has some glaring weaknesses.

Fixed schema. The SCRM 2006 data warehouse has a fixed schema, and although knowledgeable SQL developers can get at the base data schema, in this release Microsoft will not support customers or software partners who modify or extend it to include additional MOM or SMS data elements or add other data sources such as non-Microsoft systems management and accounting products. Microsoft has hinted that a future version of SCRM will be extensible, but gave no details or timeframes.

Limited data correlation. Although some of the reports can help IT personnel identify cause-and-effect relationships from captured management data, this capability is primitive and is still a long way from providing "probable cause" analysis, let alone determining the root causes of problems. Probable cause analysis is a capability Microsoft intends to provide in future releases of management products as part of the DSI, which will narrow down the underlying sources of problems by using software models that specify the relationships and dependencies between applications, OSs, and hardware.

Not forward compatible. Because of major changes planned for the next versions of SMS and MOM, SCRM 2006 will work only with the current versions, SMS 2003 and MOM 2005. This means that customers who use this version of SCRM will be unable to upgrade their SMS or MOM servers until a successor to SCRM 2006 is available.

Three separate database servers required. SCRM requires three SQL Server databases: its own, SMS 2003's, and MOM 2005's. However, none of these can use the same instance of SQL Server, and these databases cannot even be run on multiple instances of SQL Server co-located on the same server (except through use of server virtualization). For smaller companies whose systems monitoring, management, and reporting requirements could be met with the processing power of a single server, the requirement for separate servers means substantial added cost in SQL Server licenses, Windows Server 2003 licenses, and hardware.

Uses older DTS technology rather than Integration Services. Microsoft overhauled SQL Server 2000's DTS for SQL Server 2005 and gave it a new name: Integration Services. The new service offers simpler programming, better performance, and improved management capabilities compared to its predecessor. Yet, because Integration Services is not fully backward-compatible with existing DTS jobs and Microsoft's planned tools for migrating DTS code to Integration Services only works for a subset of existing DTS packages, SQL Server 2005 still supports DTS.

However, SCRM 2006 uses DTS rather than Integration Services. The reason: SCRM uses MOM's existing DTS scripts. Because the SCRM data warehouse schema is not intended to be extensible and the necessary DTS scripts are included, the choice of DTS should not be problematic for users. Nonetheless, Microsoft will almost certainly update the product to use SQL Server 2005's Integration Services in a future release.

No automatic DTS jobs for AD. The SCRM data warehouse includes elements (such as user location, cost center, and manager name) that can be derived from data in AD, enterprise resource planning systems, or human resources systems. However, since SCRM 2006 does not include a scheduled DTS job to automatically and periodically extract this information, SCRM reports may include out-of-date information. Microsoft supplies an Excel template with SCRM 2006 so that administrators can manually populate it with business contact data extracted from one of the above sources and then import the data using the SQL Server Reporting Services' DTS Import/Export Wizard, but Microsoft intended this for evaluation and test purposes only. It plans to provide prescriptive guidance and sample scripts for importing AD data into SCRM. The scripts will enable customers to create recurring tasks so that AD data transfer can be done periodically and automatically.

Availability, Requirements, and Resources

The release candidate of SCRM 2006 is available in English only, but the shipping version will also be available in French, German, and Japanese.

Although the Standard Edition of SQL Server 2005 (or higher) is required to run SCRM 2006, SQL Server is not included with the product. However, as Microsoft did with MOM 2005 and SMS 2003, the company plans to offer a specially priced SCRM 2006/SQL 2005 Server bundle.

As of Feb. 2006, no pricing or licensing details were available.

Interested parties can apply for access to the release candidate version and access to newsgroup support at www.microsoft.com/windowsserversystem/systemcenter/evaluation/overview/reporting/beta.mspx.

MOM 2005 was covered in depth in "More Polished Operations Manager Nearing Completion" on page 3 of the July 2004 Update and "MOM 2005 Ships with New Licensing Model" on page 18 of the Oct. 2004 Update.

SMS 2003 was covered in depth in "Stronger Systems Management Server Worth a New Look" on page 9 of the Nov. 2003 Update.