Event log vs. Repository vs. Database sizing in InTrust. (4291471)

When collecting from event logs, to find that the total data stored in the event log for a given time period is very different than the repository, which is also different than the database, e.g a day is 100 MB, the repository is only 5 MB and the audit database is 300 - 500 MB.

This is caused by the extreme compression built-in to the repository vs. the uncompressed event logs vs. database overhead depending on the size of events, i.e. number of insertion strings.

The best practice is to keep the bare minimum of data (based on days, servers and event ids - including event descriptions) needed for active reports by using specific import and cleanup jobs to populate the audit database.

The SQL database is larger because of the difference in the nature of these two storage types. The repository is for long-term archival and storage, while the database is for current reporting only, as it is just meant to keep as much event history as is need for current reports, and generally shouldn't be more than 70 GB (50 GB for InTrust for Events and InTrust Express) at any time. The repository stores data in a way that no redundant data is saved and then it is compressed with an efficient archive program (gzip, as of the current version). This makes it about 20 times smaller than the original event log. The SQL database has a considerable overhead in terms of storage space due to the technology it uses. This includes transaction logs, table indexes, and storage mechanics, i.e. the way data is written to virtual and physical disk files. This makes data in the SQL database usually about 3 to 5 times larger in size than the original event log.

If the repository is compared to the database, the size ratio can vary from 20*3=60 to 20*5=100. In reality, based on the specific event data, this ratio varies from below 60 to sometimes over 100 depending on the imported event descriptions, there is an option in Import Policy properties. The ratio depends greatly on the structure of events being imported, i.e number of insertion strings included in the events collected.

N.B. The 'InTrust_10.7_BestPractices.pdf' has the following to say on this subject:

Basing on your company's data retention policy and administrative needs, you need to plan for the following:

The number and locations of your repositories and databases
Data retention periods for repositories and databases

Typically, a SQL Server hosting the Audit database features a Quad 3GHz processor with plenty of RAM and hard disk space. In particular, it is recommended that database size should be kept under 100GB; data retention period depends on your reporting needs.

Solution applies to all versions of InTrust, InTrust for Events and InTrust Express.