Ok, so you want to have an idea how large your Operations Manager databases are going to be. And you want it to be a little more exact than “larger than a gigabyte, smaller than a terabyte.” We have been working on this question for a while now and have developed the following methodology that provides high-level estimates for database sizing for the three databases in OpsMgr: Operations DB, Data Warehouse DB, and ACS DB.
First, the caveats. These are estimates based upon our testing results and may not take into considerations specific aspects of your environment such as large numbers of management packs, customized management packs, extremely chatty management packs,or other factors which may impact your database size. With that said however, this is how we came up with our numbers.
OpsDB: We installed a new version of each database and determined what amount of space was used when it had been installed but prior to when agents began reporting data. (This means it had configuration data with minimal operational data.) We monitored what tables were increasing to validate that it was directly related to both the retention period of the data being held and the number of agents providing information. From these we were able to determine an approximate impact on a per-agent basis to the database size, which was 5 MB/day. We added an approximately 40% contingency factor to come up with the estimated sizing. The following is the resulting formula:
(5 MB/day x Number of Agents x Retention Days) + 510 MB = Operations Manager Database Estimate
| Grooming Interval (days) | 3000 Agents | 2000 Agents | 1000 Agents | 500 Agents |
100 Agents | 50 Agents | 10 Agents |
| 1 | 15510 | 10510 | 5510 | 3010 | 1010 | 760 | 560 |
| 2 | 30510 | 20510 | 10510 | 5510 | 1510 | 1010 | 610 |
| 3 | 45510 | 30510 | 15510 | 8010 | 2010 | 1260 | 660 |
| 4 | 60510 | 40510 | 20510 | 10510 | 2510 | 1510 | 710 |
| 5 | 75510 | 50510 | 25510 | 13010 | 3010 | 1750 | 760 |
| 6 | 90510 | 60510 | 30510 | 15510 | 3510 | 2010 | 810 |
| 7 | 105510 | 70510 | 35510 | 18010 | 4010 | 2260 | 860 |
| 8 | 120510 | 80510 | 40510 | 20510 | 4510 | 2510 | 910 |
| 9 | 135510 | 90510 | 45510 | 23010 | 5010 | 2760 | 960 |
| 10 | 150510 | 100510 | 50510 | 25510 | 5510 | 3010 | 1010 |
Data Warehouse DB: This was done using the same concept as the OpsDb with a similar level of contingency. The following is the resulting formula:
(3 MB/day x Number of Agents x Retention Days) + 570 MB = Data Warehouse size estimate
| Retention Period | 3000 Agents | 2000 Agents | 1000 Agents | 500 Agents |
100 Agents | 50 Agents | 10 Agents |
| 1 month | 270570 | 180570 | 90570 | 45570 | 9570 | 5070 | 1470 |
| 2 month | 540570 | 360570 | 180570 | 90570 | 18570 | 9570 | 2370 |
| 1 qtr | 810570 | 540570 | 270570 | 135570 | 27570 | 14070 | 3270 |
| 2 qtr | 1620570 | 1080570 | 540570 | 270570 | 54570 | 27570 | 5970 |
| 3 qtr | 2430570 | 1620570 | 810570 | 405570 | 81570 | 41070 | 8670 |
| 1 yr | 3285750 | 2190570 | 1095570 | 548070 | 110070 | 55320 | 11520 |
| 5 qtr | 4095570 | 2730570 | 1365570 | 683070 | 137070 | 68820 | 14220 |
| 6 qtr | 4905570 | 3270570 | 1635570 | 818070 | 164070 | 82320 | 16920 |
| 7 qtr | 5715570 | 3810570 | 1905570 | 953070 | 191070 | 95820 | 19620 |
| 2 yr | 6570570 | 4380570 | 2190570 | 109570 | 219570 | 110070 | 22470 |
ACS DB: This one was more complex due to the factors involved with different types of systems reporting to ACS (workstations, servers, domain controllers). To factor in these differences we put an approximate weight to each of these (1 to a workstation, 5 to a server, 100 to a domain controller). The following is the resulting formula:
(8 MB/day x (Number of Workstations) x Retention Days) +
(8 MB/day x (Number of Servers * 5) x Retention Days) +
(8 MB/day x (Number of Domain Controllers * 100) x Retention Days) + 8 MB = ACS Database Size Estimate
| Grooming Interval (days) | 3000 WS, 600 Server, 14 DC | 2000 WS, 400 Server, 10 DC | 1000 WS, 200 Server, 8 DC | 500 WS, 100 Server, 5 DC |
100 WS, 20 Server, 2 DC | 50 WS, 10 Server, 2 DC | 10 WS, 2 Server, 1 DC |
| 1 | 59208 | 40008 | 22408 | 12008 | 3208 | 2408 | 968 |
| 2 | 118408 | 80008 | 44808 | 24008 | 6408 | 4808 | 1928 |
| 3 | 177608 | 120008 | 67208 | 36008 | 9608 | 7208 | 2888 |
| 4 | 236808 | 160008 | 89608 | 48008 | 12808 | 9608 | 3848 |
| 5 | 296008 | 200008 | 112008 | 60008 | 16008 | 12008 | 4808 |
| 6 | 355208 | 240008 | 134408 | 72008 | 19208 | 14408 | 5768 |
| 7 | 414408 | 280008 | 156808 | 84008 | 22408 | 16808 | 6728 |
| 8 | 473608 | 320008 | 179208 | 96008 | 25608 | 19208 | 7688 |
| 9 | 532808 | 360008 | 201608 | 108008 | 28808 | 21608 | 8648 |
| 10 | 592008 | 400008 | 224008 | 120008 | 32008 | 24008 | 9608 |
| 11 | 651208 | 440008 | 246408 | 132008 | 35208 | 26408 | 10568 |
| 12 | 710408 | 480008 | 268808 | 144008 | 38408 | 28808 | 11528 |
| 13 | 769608 | 520008 | 291208 | 156008 | 41608 | 31208 | 12488 |
| 14 | 828808 | 560008 | 313608 | 168008 | 44808 | 33608 | 13448 |
From what we have seen so far, these equations match well with results we have seen published. Hopefully, this will help you provide more accurate estimates of the database storage sizes required for OpsMgr 2007.
Operations Manager 
The following question was asked regarding sizing of the data warehouse, but the person did not leave an email address so we could not reply directly:
In your estimates for the DB size for the data warehouse is the retention time based on the raw, non-agreggated data?
Answer:
We used the default retention periods and based the sizing on a combination of raw and aggregated data (which was what was being stored when we validated our numbers against installed data warehouses). By default, raw data is not saved for as long a period as aggregated.
I´ve read that the recomended size for Operations Manager DB is 50GB, is it the same recomendation for the Operations ManagerDW?
The question was:
I´ve read that the recomended size for Operations Manager DB is 50GB, is it the same recomendation for the Operations ManagerDW?
In MOM 2005, the recommended upper limit (and supported limit) was 30GB. Although Microsoft now will support an "unlimited size," they have suggested keeping to 50GB if possible, for performance reasons (30 GB would probably be even better). Obviously your database size will be impacted by your grooming settings, the number of agents, and the management packs installed.
There are no theoretical limits on the data warehouse either (nor were there in the previous version of Operations Manager). We recommend keeping it as small as you can (based on your requirements) that will work, in order to maximize its performance. To minimize the size you can either decrease the grooming interval, or decrease the number of agents (probably not an option) or the data being collected (management packs, potentially not an option either).
While the data warehouse is initially smaller than the operational database because much of the data is agggregated, over time it can grow to be much larger because the retention period is longer. Reasons to watch that – besides performance – are for backup and recovery purposes. A terabyte database, for example, can take a long time to backup and restore. so you may need to partition very large data warehouses; this of course adds some complexity to your environment.
Comming back about the size of the DW DB:
You said you the size was calculated using the default retention periods, but the retention periods are different for each type of data, for instance:
– Alert / Raw Data / 400 days
– State / Raw Data / 180 days
– State /Hourly aggreagations / 400 days
– Sate /Daily aggregations / 400 days
– Event / Raw Data / 100 days
– AEM / Raw Data / 30 days
– AEM / Daily aggregations / 400 days
– Perf / Raw Data / 10 days
– Perf /Hourly aggregations / 400 days
– Perf / Daily aggregations / 400 days
Did you change all this settings to the ones specified in the table?
(1 Month, 2 Month, 1qtr, 2qtr….)
And one more question:
When view the performance of a server on the Montoring Pane for a range a of week….When I\’m querying the DW DB or the OpsMngrDB?
Thanks in advance for your expert comments.
WG.
Hi William
Regarding your 1st question:
No, we did not change the settings for the various data types. We left them at the defaults and took our estimates from time periods, which were actually lower than the number of days that the data would be groomed out. For an example, we took data for 10 days and determined the increase in size on the entire database (which encompasses all the different types of data) over that period of time to develop the calculation. To have data retained for 400 days would have required 400 days of time – which is more days than OpsMgr 2007 RTM has been available! The estimates are therefore drawn from a shorter period of time, and from that we calculate an estimate on a per-day basis – which would apply if all data retentions were set to the period specified in the table.
So, if the data grows by x MB per day, and the grooming does not occur for 30 days, this states that x times the 30 days is the estimated size (plus the initial database size).
With all that said and done, please note that these are not intended to be exact measurements, but are designed to provide a better measurement than "greater than a megabye and less than a terabyte."
For your 2nd question:
We believe the performance data you are viewing is from the Operational database. This is because even if you set the window to show more than one week of data, it still only shows one week – which is the retention period for the Operations database.
Hi all,
So what about log file sizing? I know that for smaller installations SCOM automatically creates a log file that is 1/2 the size of the database, but what about for large installations. i.e. did calculations that will require 450GB for the datawarehouse database. Do I now need 225GB for log files? That seems exceptionally large and a daily full backup (or worst case weekly) will be performed.
Thanks in advance for any insight.
Mike
Hi Mike,
Thought we had previously replied before but don’t see it here – so just in case ….
The calculations include the log files sizes so you don’t need to add anything else to the sizes you determine from the article.
We will be installing the databases on a cluster. So we need to get space for the disk resources.
It would seem a best practice is seperating the database files and transaction log files on different drives.
That being said, will this provide better performance and if so, out of those allocations, how big should the drive resource supporting the transaction log files be?
I am looking at:
50GB OpsDB
200GB ACS DB
600GB DW DB
So how big should the drive resources for the data files be and how big should the drive resources for the transaction log files be,…if you recomment splitting them onto seperate resources. I believe we are using EMC DMX-8500 for our cluster disk resources.
This is on a Windows Server 2003 X64 SP2 Enterprise 2 node cluster running SQL Server 2005 X64 SP2 Enterprise with 32GB RAM.
I need to request these soon for our OpsManager 2007 install.
Ron Sexton
I have the same question as Mike asked regarding the size of the Transaction log files for the Operations Manager DW….
The default sets the size of the log to 1/2 of the Database…. Is there any recomendation on changing this setting for large Datawarehouses?
The standard answer should be, "depends on your log backup frequency"…could you confirm?
I\’m trying to figureout where the 5mb come from in (5 MB/day x Number of Agents x Retention Days) + 510 MB = Operations Manager Database Estimate
I have something around 30 Mps\’ to install, It will definitly have an impact on the 5mb
I there any rule of the thumb to calculate the MP\’s overhead on the Operational DB and DW db?
Thanks\’!
An answer to my hm adresse would be appreciated.
Replying to questions from Ron (11/9), William G (11/12), and rbarbeau (11/27)
Ron,
You are correct – for performance AND fault tolerance reasons, it is definitely best to separate the database and transaction log files to separate disk drives.
You will want to allow 40% minimum additional space for automated database maintenance such as reindexing operations. In addition, if you will be backing up to disk, you will need to leave room for disk backups as well (and the amount of space for that will depend on your backup plans and strategy).
William,
Yes the standard answer would be "depends on your log backup frequency" and how fast the transaction log grows.
RBarbeau,
The 5 MB per day of growth is a standard growth rate we observed per agent. This was based on observations at multiple installations using Microsoft\’s management packs. It is, of course, an average.
Hope this helps!
SCCP doesnt have SCOM sizing tool, does it now ? (I know it will) If not, where can I find the most up-to-date "excel sheet" that can help me size the databases for an ongoing project ?
For lots of reasons customer wants to keep raw data for as long as 1 year or 18 months. Is there any parameter possible to change to achieve that ? Doesnt matter DB size, they have lot of storage available.
Tks in advance.
Hi Luis,
You are correct, the SCOM sizing tool is not in the currently released version of the SCCP. Ian Blyth created one based on our formulas which you can find on various blogs, one being System Center Forum (see http://www.systemcenterforum.org/downloads/#185). Our book, which will be out around the end of February (http://tinyurl/27mqnm) includes our original spreadsheet on the companion CD.
The grooming settings for the three OpsMgr databases are discussed in another article in this blog, see http://ops-mgr.spaces.live.com/default.aspx?_c01_BlogPart=blogentry&_c=BlogPart&handle=cns!3D3B8489FCAA9B51!176. We also discuss this extensively in the book.
Hope this helps!
What about TempDB? It is on a disk with 33GB of space available; it has run out of space twice now in a week. We installed SCOM about 5 weeks ago. The OperationsManager DB is currently about 22GB on a 50GB disk, DW is 117GB on a 500GB disk. The message we\’ve gotten is "Could not allocate space for object \’dbo.WORKFILE GROUP large record overflow storage: 141278215798784\’ in database \’tempdb\’ because the \’PRIMARY\’ filegroup is full." From the size, I presume this is from DW grooming. We want to give TempDB a bigger disk but don\’t know how big to go.
Greedings to everyone.
Well I\’m reading this really interesting blog and I think that I will get some answers for the DBAs. Well, my first question is which are the best options to choose on the operations Manager DB properties? And the second question is about the Backups. Well in my system the person who set up the Operations Manager set the recovery model on the opsDB as "Simple recovery model" as a result in simple recovery mode, the transaction log is not backed up so you can only recover to the most recent full or differential backup. However, with the full recovery model after each Log backup you have a Checkpoint to the log file and more space for new data modification operations on the SQL Server database.
I know that I will get some professional answers.
Thanks all for your time.
Panagiotis
Update: this post from the OM team says that TempDB should be 20% of the size of the OM and Data Warehouse combined. http://blogs.technet.com/momteam/archive/2008/04/10/opsmgr-2007-hardware-guidance-what-hardware-do-i-buy.aspx
In response to http://lepeniotis.spaces.live.com:
The OpsMgr setup sets the recovery mode as simple on the Operations database. OpsMgr 2007 does support log shipping for redundancyh on the Operations and Data Warehouse databases. If you want to implement log shipping, set the database to full recovery mode. We do not recommend this for the ACS database, as it already has high processing requirements. If you are looking for fault tolerance though, clustering is a less resource-intensive approach.
As far as the options to choose for the Operations DB properties, do not set it to autogrow.
Pingback: Cameron Fuller - Failure importing a management pack into Operations Manager (#SCOM)