Estimating database requirements based on the data you collect

To determine how auditing will affect database capacity, you should monitor a pilot deployment of 20 to 25 agents with representative activity to see how much data is produced daily. For example, some audited computers might have few interactive user sessions or only short periods of activity. Other audited computers might have many interactive user sessions or long sessions of activity on average.

During the pilot deployment, you want to the following information:

  • How many interactive user sessions occur daily on each computer?
  • How long do sessions last on average?
  • What are the activities being captured, and what is the average size of each session being captured?
  • How long do you need to store the captured data to balance performance and storage?
  • What is the data retention period for audited data?

From the information you collect in the pilot deployment and the data retention policy for your organization, you can estimate the database size using the following guideline:

For example, if an average session generated 100 KB in the database and the installation had 250 agents, 10 sessions per agent, and a six-month retention period (about 130 working days), the storage requirement for the audit store database would be 36.9 GB:

250 agents x 10 sessions/agent each day x 100 KB/session x 130 days = 32,500,000 KB

The following table shows examples of the data storage requirement in an installation with Windows agents, typical levels of activity with an average of one session per day on each audited computer, and the recovery mode set to Simple:

Agents Average session length Average session size Daily Weekly 6 Months

100

20 minutes

806 KB - low activity

79 MB

394 MB

10 GB

50

25 minutes

11.56 MB - high activity

578 MB

2.81 GB

73.36 GB

100

20 minutes

9.05 MB - high activity

905 MB

4.42 GB

115 GB

In this example, an installation with 100 Windows agents with low activity would require approximately 10 GB for the audit store database to keep audit data for 6 months. An increase in the number of interactive sessions, session length, or average session size would increase the database storage required.

If SQL Server requires more space to accommodate the new data, it expands the database file immediately, which can cause degraded performance. To reduce the effect of database expansion on performance, allocate sufficient space to support database growth. In addition, monitor database space and when space is low, schedule a database expand operation for an off-peak time.