Jonathan Almquist on Operations Manager

Multiple Management Groups, Single Data Warehouse (part 2)

In this series, I’m going to talk about multiple Management Groups sharing a single Data Warehouse.  I’ll try to clarify two common questions that come out of this scenario.

Part 1 – Operations Manager Reporting Instance
Where do these components get installed?

Part 2 – ReportServer and ReportServerTempDB
Where do these databases reside?

Here we go with part 2…

While planning a consolidated reporting deployment, some thought needs to be given to where the reporting databases will be hosted.  However, the restrictions set forth by the SCOM Reporting Instance do not apply for the reporting databases.  As described in part 1, the SCOM Reporting Instance needs to be unique for each Management Group.  The reporting databases can be hosted by any SQL DB Engine, which is not dependent at all on any Management Group.

Before I get into some common scenario’s for the reporting databases, I want to mention one caveat.  The Product Group does not officially support the reporting databases in a clustered configuration.  Although I have seen this configuration work well in some cases, I cannot recommend this configuration for obvious reasons.  This article from the MOM Team has served as an official statement of this configuration.

Scenario 1

I commonly see all reporting databases for each Management Group hosted on the Data Warehouse SQL DB Engine.  For example, a Data Warehouse shared between three Management Groups looks similar to the following.

Scenario 2

Another option is to host the reporting databases on another SQL Server.  I can see this as an option for a situation where a Data Warehouse is shared amongst more than two Management Groups, and performance may be of concern.  More specifically, if the Data Warehouse is currently being shared between two Management Groups, sharing this Data Warehouse to an additional Management Group may push these hardware resources over the top.  If this happens, there may be data insertion issues, especially during times of heavy report usage in either of the Management Groups.

Scenario 3

Another option is to host the reporting databases on the same machine that is hosting the SCOM Reporting Instance, as follows.

Conclusion

As outlined here, the reporting database can be hosted by virtually any SQL DB Engine.  The scenario’s don’t end with what is listed above.  These are just some of the more common scenario’s.

The most important thing to consider is performance.  If your initial design was to host all reporting databases on the same server hosting the share Data Warehouse, and your report users start observing latencies and performance degradation, there are options to move these reporting databases to another SQL DB Engine.  Feel free to mix and match these databases to optimize your reporting experience.

In which Management Pack is this Group Stored?

Ever wonder which Management Pack you stored a particular group in?  Here’s the answer.

ForEach ($Group in get-MonitoringObjectGroup) {If ($Group.DisplayName -eq "<group_name>") {get-MonitoringClass | where {$_.Id -eq $Group.Id.ToString()} | Foreach-Object {$_.getManagementPack()} | Select @{Name="Group Name";Expression={$Group.DisplayName}},@{Name="MP Name";Expression={$_.Name}},@{Name="MP DisplayName";Expression={$_.DisplayName}} | fl}}

Command Shell Main Menu

Multiple Management Groups, Single Data Warehouse (part 1)

In this series, I’m going to talk about multiple Management Groups sharing a single Data Warehouse.  I’ll try to clarify two common questions that come out of this scenario.

Part 1 – Operations Manager Reporting Instance
Where do these components get installed?

Part 2 – ReportServer and ReportServerTempDB
Where do these databases reside?

Before I get started, I want to make one thing clear about clustered configurations.  The SCOM Report Server role is not cluster-aware, so the Report Server role cannot be installed in a clustered configuration.  The SSRS Instance cannot participate in a scaled-out deployment.  Nor is the reporting databases hosted in a clustered configuration officially supported.  This article by the MOM Team has served as the official statement on these configurations.

Here we go with part 1…

Throughout this post, I’ll talk about the SCOM Reporting Instance.  When I mention the SCOM Reporting Instance, I am referring to three components that make up SCOM Reporting.

Components of a SCOM Reporting Instance

· SCOM Report Server role

· SQL Server Report Server (SSRS) instance

· Computer

You might be wondering why I include “computer” as a component.  No kidding, we need a computer?  I added this as a component to help the reader visualize the concept, which you’ll understand in just a moment.

There is a dependency between each of these components. We must think of the composition of the SCOM Reporting Instance as a single package that cannot be split. Also, a SCOM Reporting Instance cannot coexist with another SCOM Reporting Instance serving another Management Group.

During the installation of the SCOM Report Server role, SSRS security is reconfigured to make use of SCOM security features (User Roles) which are implemented in the SDK. Because of this, only one installation of the SCOM Report Server role can exist on a computer.

Since the SCOM Report Server role installation has a dependency on a local installation of a SSRS instance, we can deduce that there is a 1:1:1 relationship between the SCOM Report Server role, the SSRS instance and the computer in which these components are installed.

We can also determine from these rules that the 1:1:1 relationship is strict, and that these components can neither be split, nor can these components be mixed and matched with other SCOM Reporting Instance components serving other Management Groups.

Of course, we need not be concerned about these rules in single Management Group scenarios that are not sharing a Data Warehouse.  But if you are sharing a Data Warehouse amongst two or more Management Groups, these rules apply.

Customers often get hung up on the “unique computer” for each SCOM Reporting Instance rule. Often I hear customers asking if they can combine SCOM Reporting Instances in a multiple Management Group scenario. This isn’t possible, as a SCOM Reporting Instance cannot coexist with another SCOM Reporting Instance serving another Management Group.

Another point of confusion for customers is they have installed a SCOM Reporting Instance for the first Management Group on the server hosting the Data Warehouse. Later, they have deployed additional Management Groups and want to share the Data Warehouse. This is fine. But, again, the rules state that we cannot install another SCOM Reporting Instance on the server hosting the Data Warehouse. We must find another server to install the SCOM Reporting Instance. Additionally, each subsequent Management Group deployed that will share the Data Warehouse will require yet another unique server to install the SCOM Reporting Instance.

In case there are any lingering questions, I provided a simple table that should solidify these rules.

Even though this seems like a simple concept, I get these questions on a regular basis.  I hope this clarifies the SCOM Reporting Instance and where these components are installed.  Next I’ll be talking about where the report server databases can be created, and describing a caveat if these databases are located on a Data Warehouse in a clustered configuration.

DBCreateWizard Fails – CLR20r3

Came across an issue today that was really just a matter of me not reading the fine print.  I figured a quick post on this could help someone with the same issue.

The problem

While attempting to install a database using DBCreateWizard, I was receiving the following error.

The Cause

I was installing an operational database for a second Management Group in an instance already containing an operational database, so I attempted to name the database OperationsManager-MG2.

Note: It’s not recommended to host more than one OperationsManager database on a single instance in most environments.  Usually this would be done in a lab or development environment.

The Solution

Name the new database OperationsManagerMG2.

After research most every avenue, I popped open the deployment guide to see if there was anything I was missing about DBCreateWizard.  I didn’t find anything directly pertaining to DBCreateWizard, but after further reading I had a memory refresh.

Page 103, number 12, of the R2 Deployment Guide states, “…do not insert a ‘-‘ in the database name…”.  Doh!

Console sluggish or frozen during agent approval process

I was working with a large customer last week, and found some very interesting performance metrics which impact console sessions.  This particular customer pushes the limits of Operations Manager 2007 in every way.  They have 6000+ agents in a single Management Group, and have an average of 50 concurrent console sessions at any given point in time.  Achieving these limits was a work in progress over several months, but eventually we got there.

However, there was one very curious point of failure, where connected console sessions would become extremely sluggish or even freeze for a period of ~10 minutes.  It was something that eluded us for quite some time, until last week.

If you are familiar with KB956240, you know that this hotfix updates the Operations Manager 2007 Data Abstraction Layer component (Microsoft.mom.dataaccesslayer.dll).  Although KB956240 helps reduce the performance impact that the types configuration changes mentioned in the article will produce, there has been no change to the underlying configuration update process in Operations Manager 2007.

The good news

This configuration update process has been changed in Operations Manager 2007 R2.  This change will also be included in the Post SP1 Rollup.  In R2 and Post SP1 Rollup, Management Group configuration updates that occur during certain types of configuration changes will not produce such a performance impact.

Am I affected by this issue?

It depends on the number of agents in your Management Group, how heavily your company employs the console (concurrent connections), and how long it takes for configuration updates to reach agents in your environment.

If you’ve got 6000 agents in your MG, you likely have a dynamic environment in which new servers (agents) are deployed regularly, and expired or failed servers (agents) are decommissioned on a regular basis.  You are likely affected by this issue.

If your administrators are always in the console, you’ll likely have some complaints that the console became extremely slow or maybe even froze for a duration of ~10 minutes.  You are likely affected by this issue.

If your Root Management Server and/or OperationsManager database server cannot handle the “bursty” type of traffic and transactions that configuration updates produce, or if there is significant network latency effecting the time it take configuration updates to reach your agent population, you are likely affected by this issue.

Although each of the abovementioned conditions are true, every Management Group is affected by this issue.  It’s really a matter of how frequently you are affected, and to what degree your environment will be impacted.

What can I do now?

From what I’ve observed in the field, the biggest performance impact is produced from approving agents.  The only guidance I could give to help reduce the impact, at least during peak times where having poor console performance isn’t an option, is to schedule agent approval during times in which you know will not impact your console users quite as much.

One thing to note

Whether a single agent is approved, or a batch of 200 agents are approved, the same configuration update process is initiated.  If there are multiple agents waiting to be approved, select each agent and approve all at the same time.  Or, use an agent approval script that batches agent approval.

If you have auto-approval turned on (indicated below in yellow), unfortunately there is no way to control the approval process.

Which MS are my agents currently connected to?

UPDATE:  04-26-2009

While using this script at a customer site, outside of my small lab environment, I realized I could have made this script perform more efficiently.  My customer had ~500 agents, and this script took about 10 minutes to complete.

I figured this was an important enough update to warrant a new post, so those that subscribe can grab the updated version.

This is a very good question that has come on occasion.  However, this question cannot be answered by means of looking in the Operations Console or querying the Operations Manager databases.

We can easily find out which Management Server is acting as a Primary for an agent.  We can also set Failover Management Server using AD Integration and using Powershell scripting methods.

Everyone wants to know, after configuring failover servers (or not), which Management Server their agents are currently talking to.  We all want to know if our agents are reporting to the Management Servers we configured them to report to.  And, if they failover, are they failing over to the appropriate MS configured in the failover list?

Here’s one way to find out!

Simply copy the script below into the Command Shell on any Management Server, including the Root Management Server.  The results will show you a list of agents currently connected to that Management Servers Health Service.  It will also output whether an agent is currently in a Failed Over state.

Screenshot of script output 
 

Here’s the script.  Copy everything between the first ## and the last ##, and paste directly into the Command Shell on each of your Management Servers.

##--Start copy here, include this line

$AgentArray=@()
$Space = " "
foreach ($agent in get-agent | select Computername,@{name='IpAddress';expression={$_.IpAddress.split(",")[0].ToString()}},@{name='PrimaryMS';expression={$_.PrimaryManagementServerName.Split(".")[0]}})
    {$AgentArray+=$agent}
foreach ($RemoteEndPoint in [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties().GetActiveTcpConnections() | where {$_.LocalEndPoint -match "5723"})
    {
    $i=0
    while ($i -ne $AgentArray.count)
        {
        if ($AgentArray[$i].IpAddress.ToString() -contains $RemoteEndPoint.RemoteEndPoint.Address.IPAddressToString)
            {
            $SpaceCount = 30 - $AgentArray[$i].Computername.length
            $FAgent = $AgentArray[$i].Computername + $Space*$SpaceCount
            if ($AgentArray[$i].PrimaryMS -eq $env:ComputerName)
                {
                $FStatus = "OK"
                }
            else
                {
                $FStatus = "Currently Failed Over"
                }
            Write-Host $FAgent $FStatus
            $i=$AgentArray.count
            }
        else
            {
            $i+=1
            }
        }
    }

##--End copy here, include this line

Caveats

*Any agent that has more than one IP Address, only the first IP Address discovered will be used.  If the first IP Address discovered does not match the Active TCP Connections list on the MS, this agent will not appear in the list.

**I’ve had problems in some network adapter configurations, where the results were not accurate.  This is immediately evident by spot checking the results.  I haven’t identified the exact cause of these rare cases.

command shell main menu

All disk sizes (GB)

/*Get each logical disk size, for each agent computer, by OS version.
This helps in calculating the Logical Disk Free Space Monitor from my
earlier post.  You can copy results into Excel, sort by system and
non-system drives, and perform an average disk size formula.  Then
plug Min, Max and Avg sizes into my Logical Disk Free Space Calculator
to find your unique MB and % thresholds for your company's unique
requirements.*/

SELECT     PrincipalName AS 'Windows 2000', DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C AS 'Drive', CONVERT(bigint,
                      Size_486ADDDB_2EB8_819A_FA24_8F6AB3E29543) / 1024000000 AS 'Size'
FROM         MTV_LogicalDisk
ORDER BY 'Windows 2000', 'Drive'

SELECT     PrincipalName AS 'Windows 2003', DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C AS 'Drive', CONVERT(bigint,
                      Size_486ADDDB_2EB8_819A_FA24_8F6AB3E29543) / 1024000000 AS 'Size'
FROM         MTV_LogicalDisk_0
ORDER BY 'Windows 2003', 'Drive'

SELECT     PrincipalName AS 'Windows 2008', DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C AS 'Drive', CONVERT(bigint,
                      Size_486ADDDB_2EB8_819A_FA24_8F6AB3E29543) / 1024000000 AS 'Size'
FROM         MTV_LogicalDisk_1
ORDER BY 'Windows 2008', 'Drive'

Operations Manager 2007 SQL Queries

I’m starting a new post here, similar to my Command Shell reference.  This will include some useful SQL queries that I happen to need and direct my customers to on a daily basis.  If you don’t see something here, check Kevin Holman’s blog.  He’s already got a library of useful queries posted, in which I will not duplicate here.

I will continue to update this table periodically.  If you subscribe to my blog, you will receive each new example as I post it.

Note: The queries in each link are a direct paste from the SQL query editor.  They do not look pretty.  Simply copy the content into your query editor to see proper formatting.

Replace any text in red in each example with your specific parameters or criteria.

All groups and their contained instances

SELECT     SourceMonitoringObjectDisplayName AS 'Group', TargetMonitoringObjectDisplayName AS 'Member'
FROM         RelationshipGenericView
WHERE     (SourceMonitoringObjectDisplayName IN
                          (SELECT     ManagedEntityGenericView.DisplayName
                            FROM          ManagedEntityGenericView INNER JOIN
                                                       (SELECT     BaseManagedEntityId
                                                         FROM          BaseManagedEntity WITH (NOLOCK)
                                                         WHERE      (BaseManagedEntityId = TopLevelHostEntityId) AND (BaseManagedEntityId NOT IN
                                                                                    (SELECT     R.TargetEntityId
                                                                                      FROM          Relationship AS R WITH (NOLOCK) INNER JOIN
                                                                                                             dbo.fn_ContainmentRelationshipTypes() AS CRT ON R.RelationshipTypeId = CRT.RelationshipTypeId
                                                                                      WHERE      (R.IsDeleted = 0)))) AS GetTopLevelEntities ON
                                                   GetTopLevelEntities.BaseManagedEntityId = ManagedEntityGenericView.Id INNER JOIN
                                                       (SELECT DISTINCT BaseManagedEntityId
                                                         FROM          TypedManagedEntity WITH (NOLOCK)
                                                         WHERE      (ManagedTypeId IN
                                                                                    (SELECT     DerivedManagedTypeId
                                                                                      FROM          dbo.fn_DerivedManagedTypes(dbo.fn_ManagedTypeId_Group()) AS fn_DerivedManagedTypes_1))) AS GetOnlyGroups ON
                                                   GetOnlyGroups.BaseManagedEntityId = ManagedEntityGenericView.Id))
ORDER BY 'Group'

All groups

SELECT     ManagedEntityGenericView.DisplayName, ManagedEntityGenericView.FullName
FROM         ManagedEntityGenericView INNER JOIN
                          (SELECT     BaseManagedEntityId
                            FROM          BaseManagedEntity WITH (NOLOCK)
                            WHERE      (BaseManagedEntityId = TopLevelHostEntityId) AND (BaseManagedEntityId NOT IN
                                                       (SELECT     R.TargetEntityId
                                                         FROM          Relationship AS R WITH (NOLOCK) INNER JOIN
                                                                                dbo.fn_ContainmentRelationshipTypes() AS CRT ON R.RelationshipTypeId = CRT.RelationshipTypeId
                                                         WHERE      (R.IsDeleted = 0)))) AS GetTopLevelEntities ON GetTopLevelEntities.BaseManagedEntityId = ManagedEntityGenericView.Id INNER JOIN
                          (SELECT DISTINCT BaseManagedEntityId
                            FROM          TypedManagedEntity WITH (NOLOCK)
                            WHERE      (ManagedTypeId IN
                                                       (SELECT     DerivedManagedTypeId
                                                         FROM          dbo.fn_DerivedManagedTypes(dbo.fn_ManagedTypeId_Group()) AS fn_DerivedManagedTypes_1))) AS GetOnlyGroups ON
                      GetOnlyGroups.BaseManagedEntityId = ManagedEntityGenericView.Id
ORDER BY DisplayName

Get Alert History by Alert Name

get-alert | where {$_.name -match "Alert Name"} | get-AlertHistory | select Time*

command shell main menu

Get Failover MS List for Agent Computer

This will return the list of Failover Management Servers for specified agent.

get-agent | where {$_.computername -eq "netbios computername"} | Get-FailoverManagementServer | select computername

command shell main menu

Top 10 Events

This will return the top ten events collected.

FYI – Given the sheer number of events collected and stored in the OperationsManager database, this query may take a minute to return results.

$array = @();foreach ($number in Get-Event | foreach-object {$_.get_number()}) {$array += $number};$array | Group-Object | select-object -first 10 count,@{name="Event Number";expression={foreach-object {$_.name}}} | sort-object count –desc

command shell main menu

Switch to another MG in Command Shell session

If you have multiple Management Groups, this can be handy to switch between them while in the same Command Shell session.  Always be aware of which Management Group you’re connected to while in the Command Shell!

This example assumes your session is currently connected to MG1, and you want to establish a session to MG2.

Connect to MG2

New-ManagementGroupConnection MG2_RMS_Server_Name
Set-Location monitoring:\MG2_RMS_Server_Name

Switch back to MG1

Set-Location monitoring:\MG1_RMS_Server_Name

command shell main menu

Which Management Servers are Agents Currently Connected To?

UPDATE:  04-26-2009 - I rewrote this script so that it performs much faster.  See new post here.  I’ll take this post down in a couple weeks, after search engines are updated with new post.