I am doing some capacity planning for a FIM 2010 R2 deployment and trying to understand the hardware requirements for the Reporting feature. Based on the diagarm below, it looks like the performance is primarily driven by two factors: System Center and SQL. Even the performance of System Center depends on SQL. So it all comes down to SQL horsepower. The rest of the servers (FIM, SCSM, SCDW) may not need much muscle power (I might be wrong though). What is your experience when planning for Reporting? What kind of challenges one can experience? Btw, I have a userbase of 100K and the change rate (# of requests per day) I am expecting is about 6%. What kind of hardware capacity would you recommend?

Thanks!

For this scale we are just starting to rolling it out in a real environment but for some time with much less number of changes (functionality is rolled out for users in some batches), so I can't share true numbers for it.  Ask me again in a few months ;). 

You can try to ping Piotr Paczocha (active on this forum so maybe he will jump into this thread) who spent some time on getting it working for ~300k users. From what we have discussed biggest issue is Iops on spindles hosting databases for warehouse and they have ended up on some dedicated storage on SSD for these databases. I will ping him so maybe he will be willing to put more lights on it.

BTW - I'm glad you liked a picture :).

To start with, the picture is indeed really great but its missing (or not emphasizing enough) one important component SCSMs Service Manager database.

As for the capacity question, we've seen the biggest impact/load on the SCCM databases, both Service Manager and Data Warehouse (mainly DWStagingAndConfig) so yes, SQL is what drives all that. Also, we havent tried SP1 yet, so what we observed was still on SCCM 2010.

Anyway, here are few pointers from our experience:

1. SCSM Service Manager DBs and Data Warehouse DBs should be installed on different SQL instances (has to do with the nature of the DBs) and preferably on different SQL servers. And ideally separately from other FIM component DBs.

2. SCSM Service Manager DB can easily grow over twice the size of your FIMService DB (with indexes taking a lot of space), and its transaction log can quickly reach crazy sizes especially during initial load. This is the experience from running it just for few months, it might change after longer usage.

3. From SCSM Data Warehouse DBs the only one you need to worry about is the DWStagingAndConfig. In our environment size wise it was comparable to FIM Service DB, but the observed IO during ETL jobs was just crazy Ive seen graphs of it generating 300-400MB/s read IO on a 150GB DB for over 10h straight (with plenty of RAM on the SQL box).

4. Overall it seems like latency to the DB files is one of the main factors driving performance of FIM Reporting (not to mention IO) even to a point where SAN storage might not be the best option here. Like Tomasz wrote, we were looking at setting up both SCSM components on two dedicated physical servers with local SQL and DBs on SSD drives but didnt get that far yet.

When Im writing about FIM Reporting performance Im referring to a number of individual changes (queued in the ExportLog table in the FIM Service DB) that can be extracted from FIM Service to Service Manager in an hour initially we were able to process below 200k records per hour, and after several tweaks, SAN reconfiguration, etc, we were almost able to double it. There isnt any average correlation between number of requests and number of entries in the ExportLog table, it all depends on type of changes and how many are a part of individual requests. To just to give you an idea, we observed 20M records that were added over a 7 day period (avg 120k per hour), during which the only activity on FIM Service were approximately 20k SSPR registrations highly customized (with confirmation emails flying around and some custom attributes being set), but still just SSPR. As you can imagine, with that level of activity we needed to push the processing rate way over what we were seeing as FIM Reporting was barely being able to catch up in real time.

I hope this will be at least a bit helpful J.

Piotr

P.S. Oh, and by "we" I mean my previous company, I recently moved to Microsoft.

• Edited by Piotr PaczochaMicrosoft employee Saturday, February 02, 2013 7:50 AM
• Marked as answer by Just Another FIM Guy Sunday, February 03, 2013 8:37 AM

Thanks a lot Piotr! This is inded very helpful.

Hello Piotry,

Thank you for your valuable feedback.

can you please elaborate a bite more about your following sentence:

1. SCSM Service Manager DBs and Data Warehouse DBs should be installed on different SQL instances (has to do with the nature of the DBs) and preferably on different SQL servers. And ideally separately from other FIM component DBs.

What do you mean with the nature of the DBs?

Kind regrds

Fatih

Hello,

by different nature of the DBs I meant the differences between OLAP and OLTP. When it comes to optimizing SQL you would do contradicting things dependent on the type of DB and expected operations. In some cases you can see weird SQL behavior if you have those two types of DBs in one instance ... like for example the Temp DB acting crazy and growing a lot.

You can read a quick summary of the differences for example here: http://datawarehouse4u.info/OLTP-vs-OLAP.html

Piotr