Estimating Database Sizes for OpsMgr 2007

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.

Advertisements
This entry was posted in Operations Manager 2007. Bookmark the permalink.

18 Responses to Estimating Database Sizes for OpsMgr 2007

  1. Operations says:

    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.
     
     

  2. william says:

    I´ve read that the recomended size for Operations Manager DB is 50GB, is it the same recomendation for the Operations ManagerDW?
     

  3. Operations says:

    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.

  4. william says:

     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.

  5. Operations says:

    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.

  6. Unknown says:

    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 

  7. Operations says:

    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.

  8. Unknown says:

    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

  9. william says:

    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?

  10. Roger says:

    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.
     
     

  11. Operations says:

    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!

  12. Luis says:

    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.

  13. Operations says:

    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!

  14. Frath says:

    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.

  15. Panagiotis says:

    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

  16. Frath says:

    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

  17. Operations says:

    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.
     
     

  18. Pingback: Cameron Fuller - Failure importing a management pack into Operations Manager (#SCOM)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s