How to use SEPM Database sizing tool
Every time I’m on a SEP project, the database size always is a big subject for discussion, especially for a large environment.
The SEP DB sizing tool as we know is not supported tool, we use it as a guide to estimate the required disk space for the DB. There is no document to explain how exactly the sizing tool works beside the few guidelines within the tool itself.
I thought this is the time to explain how exactly that tool work, with an example.
- SEPM 12.1
- 5,000 managed clients
- To find out the estimated size for SEPM DB
- To find out what DB settings should we add/change
- To keep log history for at least 30 days
Before I start, I want to mention that the way how the tool estimate the disk space is not the same how SEPM works, for example, you may calculate the required disk space within the tool based on number of Days or number of Rows, but not both. SEPM works in combination of both. For example, you may set the maximum number of rows as 1000 and 30 days in SEPM, SEPM will purge that table if the number of rows reached 1000 records or 30 days, which one is first.
The other point I want to talk about is SEP 12.1 DB schema. SEP12.1 DB schema has been changed, each type of logs now will have 2 tables now xxx_log_1 and xxx_log_2. The events will be stored on the first table, once the first table is full, SEPM will start filling the second table. Once the second table is full, SEPM will start again on the first table.
I opened the excel sheet sizing tool, and added the following:
- Number of clients 5000
- Number of SEPM in site 1
- Number of admins 3
- Number of SEP packages 3
- Content History 10
- Scan per week 1
- Content updates per day 6
- Client heart beat 60 minutes
- Number of days history to keep 30 for all, and left everything else as default
- Number of backup 0
The results came as 68.48GB
The 68GB came based on some estimated figures available in the tool under the “Metrics” sheet, those can be changed if you are familiar with your environment and have an idea how much events each client will generate per day, otherwise leave the defaults.
The “logic” sheet in the tool provide an overview of how many events your client will generate per day and per history period (30 days in our example), however you will notice that the numbers don’t match!
As an example, the total number of Security log events per day is 5,000 (based on 1 event per client per day x 5000 client), however the per 30 days history period is showing 300,000! I assume that this should be 5000 clients x 30 days = 150,000, but not double the number. If you also look into the formula behind it “=IF(VLOOKUP(Sizing!F15,Logic!B39:C40,2)=1,SUM(C16*Sizing!E15)*2,SUM(2*Sizing!D15))” it’s actually multiply by 2.
The reason behind this is that as I mentioned earlier, there is 2 tables for each log type, and SEPM will fill the first one, and then the second one (refer to SEP12.1 DB Schema), so I decided to re-confirm this behaviour.
To confirm this:
- I changed my SEPM settings to 5 entries and 1 day
- Truncate log tables
- Restarted SEPM server
- Start generating events by simply updating the client
And yes, SEPM filled the System_Log_1 with 5 events, and then started to fill the System_Log_2.
Based on all of the above investigation, I decided to use half of the numbers per history period for SEPM DB settings, because if I used the full number per period, then SEPM will also duplicate this number by filling the second table, hence it’s an extra un-considered disk space.
To confirm that my thinking is correct, I decide to use the tool as per Rows and half of the Logic sheet results to see if the DB size will match, and yes it does match.
The SEPM DB sizing tool is just a guide to be used to estimate the required disk space, based on the size of your environment you may need to consider an extra gigs, or different way to calculate the disk space.