Command Shell Examples
Useful SQL Queries
Multiple Management Groups, Single Data Warehouse (part 2) - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

Multiple Management Groups, Single Data Warehouse (part 2)

Multiple Management Groups, Single Data Warehouse (part 2)

  • Comments 9
  • Likes

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.

image

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.

image

Scenario 3

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

image

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.

I do not moderate this blog anymore. If you have a question regarding this post, send me a message.

Comments
  • How do you make scenerio 1 possible?

    Multiple RportServer databases on 1 instance? with a different name? MG1ReportServer oppose to ReportServer. I realize I'm missing something.

  • Hi Steve,

    That is correct.  Look at the database name column in scenarion 1.  Each ReportServer and ReportServerTempDB database has a unique name.  We cannot use the same SRS databases across MG's.

    -Jonathan

  • Thank you for your response.

    What I'm not getting is how you get/install multiple Reporting Services databases.

    When you install RS the databases that are created are ReportServer and ReportServerTempDB. How did MG1ReportServer, MG1ReportServerTempDB, MG2ReportServer, MG2ReportServerTempDB, etc. get created?

    An instance of SQL can only have 1 RS installation. Does this scenario require multiple instances? If so the database name(s) will still be ReportServer and not MG1ReportServer.

    Thanks again for your help!

  • Hi Shalayda,

    While creating the ReportServer and ReportServerTempDB, you can name these whatever you want.  We're talking about two different things here; the SSRS instance and the databases supporting the SSRS instance.  The databases can reside on the same instance.  For SSRS installation needs to be installed on its own, dedicated computer in order for SCOM reporting to work correctly.  This is all explained in the two posts here (part 1 and part 2).  Read carefully. :)

    -Jonathan

  • Hi Jonathan,

    Further to these articles, do you know of any resources out there to assist in setting up a data warehouse shared by management groups in two separate forests? Documented complex scenarios seem few and far between.

    Our scenario includes a one way trust between forests.  MGs are connected with local MG and DW in the "trusted" forest.

    Appreciate anything you can point me at!

    Cheers,

    Alicia.

  • Alicia,

    I don't have any good reference material for this scenario.  But I can tell you that to make this work, from a security perspective, you'll just need to make sure your SDK, Data Reader and Data Warehouse Write Action accounts in each of your monitoring management groups domains can access and authenticate on the SQL instances hosting the Data Warehouse in the other domain.  If there is a trust, there should be no Kerberos boundaries to deal with, and a simple test could prove this.  For example, you can logon to the computer hosting the DW instance in the other domain, and attempt a "runas /user:MG_Domain\SDK notepad.exe", or something similar.  If notepad launches under the context of the SDK account, then you're in luck and already half way there.

    The one thing I would be concerned with is, since the DW is in another domain, does this mean it's on another network segment?  If so, you'll want to make sure there isn't network latency, because we write to the DW in parallel.  If there is latency here, this could potentially delay or stall write operations from the MS's and have ill effects to reporting runtime performance.

    -Jonathan

  • Jonathan thankyou for the explanation.  

    Unfortunately I'd already gotten that far by switching the services in both forests to run with the trusted forest's sdk account.

    Everything seemed to work ok up to the point where the system tried to perform discoveries and normal Opsmgr-DW activity (parallel writes), not sure if this is because the SDK account's SPN needs to reside in the same forest as the system? who knows :) MG and entity info seemed to populate the DW during the reporting install but no state data came across. It seems that my problems are primarily firewall based so I'm canning further attempts and going with sql accounts suggested in the "Using a firewall with Operations Manager 2007" documentation. This seems to have wiped out all but one issue and that shouldn't take too long to nut out I hope!

    I don't think I'm going to have an issue with latency particularly taking AD authentication out of the picture but your point has been noted and I'll keep an eye on it.

    Appreciate your time and expert advice!

    Alicia.

  • Alicia,

    I would have went the other way with the accounts, and seperated the SCOM service accounts between the two management groups (not shared).  So the SDK, DRA and DWWA accounts would reside in the same domain as the management group, and ACL those accounts accordingly on the DW SQL instance in the other domain.  Although it is possible to share the DRA and DWWA accounts in a shared DW scenario, throwing into the mix three seperate domains (and/or forests) will definitely complicate this scenario a bit more.  The SDK account should not be shared, though, because you need to register SPN's on that account, and those are unique to each management group.

    As far as ports are concerned, unless your RS role is also hosted in the same domain as your DW, you should only need port 1433 from both management group domains to DW domain.

    I wish you well in your endeavor.  For more feedback, I recommend taking a look at our SCOM forums here: http://social.technet.microsoft.com/Forums/en-US/category/systemcenteroperationsmanager.

    -Jonathan

  • Just revisiting this thread after a LONG time, and I can offer a definitive answer to this. There are specific account in SCOM that allow for cross-forest DW sharing. These accounts specifically use SQL accounts, so your DW will need to use mixed auth. Once your DW uses mixed auth, you setup SQL accounts and the associated run as accounts in SCOM, and associate them with the SQL profiles. This is how cross-forest sharing is implemented, even though the product group or nobody else has told the story.