Virtualization Planning & Reporting with MOM 2005 Reporting

Everyone is familiar with the advantages of server consolidation by now. With the recent announcement of the Virtual Server 2005 R2 MP refresh marks the return of one of the most useful reports you'll find in MOM 2005 - the "Virtualization Candidates Report". This report was pulled from the report xml file long ago when it was discovered it was not "SQL 2005 friendly". This report provides a quick way to identify all those underutilized boxes from the days when deploying a new application meant automatically deployed a server to host it. Essentially, the report parameters ask you to define your criteria for a good candidate. You simply select: Target computer group (e.g. - Windows 2003 Servers) Max and Avg CPU Max and Avg Memory Max number of CPUs present Max physical memory In most cases, you're going to be looking for 1-2 CPU / <= 2GB RAM as easy targets for virtualization Here's a quick screenshot of what I am talking about (sorry if it doesnt come out clear). There are several other reports I really like in this MP as well: VM Machine Details Performance History (CPU/MEM) Disk Usage Sadly, VMWare admins will discover these reports only work for Virtual Server 2005 VMs. :( I still have several MS VMs in production. I have both MS and VMWare in production, so maybe a hybrid version of this report can be hashed out in the community at some point.

Thoughts on Summary Reporting and SCDW Grooming

After installing the Summary Reporting Pack a couple of times recently, I got to thinking about the interdependencies of MOM databases from a disk space perspective, especially in single server environments, and wanted to share a couple random thoughts on the initial reporting data aggregation and grooming processes once you install the Summary Reporting Pack based on some observations of a much-less-than-SQL-guru during recent installations. I suppose this may also have some value in recovery if you should discover your SCDW grooming process has failed for extended periods due to lack of disk space.     Summary Reporting Notes   The Summary Reporting Pack aggregates data points (alerts, performance) into a single point for each day (or week) for a given counter or alert. I won't go into the detail as the Summary Reporting manual and Marcus have addressed this already in fine detail. I want to talk for a moment on managing log utilization and disk space in the process and job scheduling through the initial aggregation process and going forward.   At this point I'll restate a couple of somewhat self-apparent but important points:   Don't let the Summary Reporting 'BuildAggregations' SQL job do the initial aggregation for you, as it will attempt to do it all at once. FYI – It is scheduled to run nightly at 2am by default. A couple of items to note:   The DTS launched by this job appears to execute 4 stored procedures), 3 executed in parallel, which means they're all hammering away at server resources in unison. If you want a good visual representation of the process, just open the BuildAggregations.dts DTS package launched by the BuildAggregations SQL job through Enterprise Manager (dts package located in the install directory for the Summary Reporting installer package).   This job will cause your tempdb to grow substantially as the procedures executed create temporary tables during the aggregation process. (several GBs for each week varying by the number of servers, and their associated records in the SCDW fact tables)   More importantly, it does not appear chunk the operation in any way (that I can see), it's like one great big transaction, so if that BuildAggregations job is allowed to run on it's own initially, it will fail unless you have a vast amount of free disk space for tempdb. So once you have Summary Reporting installed and configured, follow the manual aggregation instructions in the guide that comes with the pack and manually chip away at that data a week to 10 days at a time until you have all your historical data taken care of.   …and if you can't do it the day you install it, consider disabling the BuildAggregations SQL job until you have time to complete this task.   When you lower the historical data retention on your 6 fact tables in SystemCenterReporting (SCDW), set your target and then groom the data manually in small doses.   To explain that a bit further…   If you have the default 395 days of data and you then aggregate that data into the summary data points as provided by the Summary Reporting Pack, you've now succeeded in increasing the total size of your SCDW. So to get your "up to 65% disk savings" as advertised with Summary Reporting, you now need to reduce the number of days you retain the historical data in the 6 fact tables.   NOTE: If you perform the following before you've successfully performed the initial aggregation process with Summary Reporting, there will be nothing to summarize. L   For example, let's assume you decide that with 395 days of data summarized into thousands of tidy single points of data, you can get by with storing only 6 months of detailed historical data. At this point, you use the standard SQL query to reduce retention in the 6 fact tables to only 7 months (see Marcus for the how HERE.)   This means the SCDWGroomJob on its next run is going to attempt to groom everything older than your retention date – in our example, that is 6 months of historical detail data (13 mths – 7mths).   So what is the impact of this in terms of disk space during the grooming operation? I wanted to just get some add-hoc data to quantify the impact. Here's a rundown of what I saw and some of the why.   The environment:   Agent-managed servers: approx 200 Rows of data (per day) in SC_SampledNumericDataFact_Table: approx 1 million rows (based on a dozen or so representative samples). (There were about 144 million rows total when we started). Row counts in the other 5 fact tables were so small in comparison in this environment I didn't factor them in.     The result:   By lowering historical data retention 1 day at a time (using the standard published SQL statement), I could then run the SCDWGroomJob in SQL and watch the growth of various databases during the process. What I saw was tempdb grow approximately 2.8GB in the process of grooming 1 days data (1 million rows). Reducing historical data retention by 10 days resulted in tempdb growth of about 28GB.   Why this happens:   SCDWGroomJob starts by putting an exclusive lock on 'MOM.Datawarehousing.DTSPackageGenerator' to ensure no other grooming operations are taking place.   It then creates a pair #temp tables (in tempdb), one to store some class ID relationship information on source and target rows (#tmpRelationshipContraints), and another to hold the ordered list of classids in the delete order (#tmpDeleteList).   And in short, the more data out there to be groomed in the data warehouse, the bigger tempdb will grow (no chunking apparent in this job either). So once you get your Summary Reporting aggregations out of the way, it's probably a good idea to perform the data warehouse grooming in the same controlled manner, reducing retention a few days at a time, running the SCDWGroomJob again, rinse, repeat, until you're down to your target retention level.   When complete, you could at this point  shrink your SystemCenterReporting database and transaction logs, leaving the prescribed free space (40% in SCDW if you use the standard SCDW reindexing script).   That's all for now. Another post forthcoming on optimal job scheduling and database maintenance in the next few days. Props to Marcus Oh and B lake Mengetto for their shared thoughts on the topic as well.   Send any thoughts, comments or other retorts as the spirit moves you to do so.

MOM Report Import Failures after Upgrade to SQL 2005

A post SQL 2005 upgrade issue I ran into I thought worth mentioning for anyone unaware... A few weeks ago, right after I performed the upgrade to SQL 2005 (in test) and applied the 3 post-upgrade hotfixes, I ran into an issue that may likely prevent me from repeating the operation in production for now. The problem is presumably related to more stringent XML validation in SQL 2005. Specifically, when I attempt to import MOM Reports, the import fails for several MPs with an identical error: System.Web.Services.Protocols.SoapException: The table `table1' is in the report body but the report has no data set. Data regions are not allowed in reports without datasets. Affected MPs: I started with a clean slate and started importing MPs (about 10 on the first pass). The error has appeared when importing reports for the following MPs: Exchange, VirtualServerR2, PrintServerMP, FRS, DFS (AD, , SQL and several others imported successfully). Status: I'm told this is a known issue. I exchanged posts with Clive who reported the issue is on the radar and being addressed, but could offer no timeframe for resolution at present. This issue was mentioned near the bottom of KB 917615 in the notes section as follows: Microsoft has identified issues that occur when you import reports into a MOM 2005 Reporting Server installation that is using SQL Server 2005. These issues occur because of more rigorous XML validation requirements in SQL Server 2005 Reporting Services. Microsoft is currently working on a solution for these issues.

Script: Monitoring and Reporting MOM Console Connections for Multiple Mgmt Servers

If you want to skip the chat and get the script from the MOMResources.org script archive (in the MOM Administration section), click  here.   And for the rest of you, here's a quick runthrough of the scripts functionality. I've always thought it would be handy to have a way to track MOM console connections in a meaningful way. Specifically, I wanted a script that would Report Connections to Management Servers Individually Dynamically chart connections in a performance object per Management Server Raise a warning event if we get close to the max recommended of 15 on a given management server So, I started with a T-SQL String I found in a Tim Minter post:   master","SELECT program_name, count(*) FROM Master.DBO.sysprocesses WHERE ecid=0 and program_name='Microsoft Operations Manager - DAS Operations Console' GROUP BY program_name ORDER BY count(*) desc   And modified it slightly to expose connections for individual mgmt servers, like so.   SELECT hostname, program_name, count(*) as ConnCount FROM Master.DBO.sysprocesses WHERE ecid=0 and program_name='Microsoft Operations Manager - DAS Operations Console' GROUP BY hostname,program_name ORDER BY count(*) desc   Wrote a simple script and added sub routines for 1) Dynamically charting connections for any mgmt server added to your environment without any intervention on your part 2) A call to a sub routine to generate a warning in MOM if connections break a user-defined threshold.   In terms of shortcomings, there is the fact that the script doesn't account for 0 connections, but just leaves the datapoint blank in the performance charts and goes on. I suppose we could get creative and work around that, but I found I really didnt care, because I just wanted to watch the upper limit (and 0 connections doesn't happen in my environment much :) ). I am sure someone creative out there in the community will address that.     Cheers, Pete

HOW-TO: Track and Report MOM (and Other SQL Database) Growth : Part 1

I put together a short article on collecting and reporting quick-and-dirty database usage trends for MOM and other SQL databases. http://www.it-jedi.net/how-to/dbstats/Track_Report_SQL_Growth_MOM.htm 6/05/06 - NOTE: I updated the script to deal with some whitespace issues. Get an updated copy of DB_SizeWatch HERE: http://www.momresources.org/momscripts/DBSizeWatch_Create.txt

Moving the MOM Reporting Role

I've seen a few requests lately for instructions on how to move the MOM Reporting role. Here's the the high level fly-over.   1) If you have any custom reports, export them. 2) Uninstall MOM Reporting. This will not delete the SystemCenterReporting DB. 3) Detach the SystemCenterReporting DB using SQL Enterprise manager. 4) Install MOM Reporting on the new machine 5) Detach the SystemCenterReporting DB on the new machine, replace the DB file with the old file you detached in step 3. That way you keep your old data. 6) Import the reports for your existing MP's using the MP Import wizard in the MOM Admin Console. 7) Import your custom reports 8) Setup the SQL Reporting Services permissions to allow users to browse the new reports as before. Powered By Qumana

Understanding MOM Reporting DTS and Interdependencies

The often misunderstood processes surrounding the nightly DTS job that serves to move Onepoint data to the SystemCenterReporting data warehouse. Marcus Oh explained it’s functionality in detail several months ago, and I simply wanted to draw attention to it in light of a recent question. You can find Marcus’ article here.

Integrating MOM 2005 Reports with Sharepoint Portal

Been working more with the reporting aspects of MOM lately. On an interesting side note, by using the SQL Reporting Services SP2 web parts, you can integrate MOM reporting into a Sharepoint Portal site. MS has a helpful walkthrough of the installation and configuration here.

MOM 2005 Summary Reporting Pack Released

Microsoft released the MOM 2005 Summary Reporting Pack recently, which extends the MOM data warehouse to provide long term reports on performance metrics and alerts based on weekly and daily summarization of data related to Exchange, Active Directory and other data.