Planning database sizes

  • Article

Applies To: Forefront Client Security

The average number of events and alerts per managed computer per day is the primary factor in your eventual database size. Because this value varies greatly among different organizations, you need to profile your existing organization to determine your sizing needs.

For example, an organization that hasn't fully updated computers and that is incurring many threats and malware detection events needs more database space than an organization that keeps their systems updated and has strict policies for sharing files and accessing Internet resources. It is likely that your organization contains a mix of the two extremes and a spectrum in between.

Client Security uses SQL Server 2005 to store information about the managed computers in the Client Security deployment.

Client Security uses three databases that you need to adjust appropriately:

  • Collection database (also known as the MOM OnePoint database)

  • Reporting database (also known as the MOM SystemCenterReporting database)

  • SQL Server 2005 tempdb database

Collection and reporting database sizes

You set the sizes of the collection and reporting database during Client Security setup.

Each managed computer sends data to the collection server. This data is stored in the collection database first and then is moved to the reporting database for long- term storage. These databases' growth rate depends on the following factors:

  • Number of managed computers

  • Frequency of malware occurrence

  • Number of security state assessment (SSA) vulnerabilities discovered

  • Number and types of scans performed

The following sections summarize planning considerations regarding the Client Security databases. For detailed database sizing information, see Database sizing in the Client Security Performance and Scalability Guide (https://go.microsoft.com/fwlink/?LinkId=89813).

SQL Server edition

An important factor in your database size planning is the edition of SQL Server you are using. Due to differences in the way the editions handle indexes, even with the same number of managed computers and similar activity, the Client Security reporting database is larger with SQL Server Standard Edition than with Enterprise Edition. You should take this into account when determining the database sizing as well as when choosing the SQL Server edition for your Client Security deployment.

Data retention

The amount of time you need to retain data for reporting purposes will also affect your database sizes. By default, the reporting database retains data for 395 days (one year and one month). You can modify the retention period by using a SQL Server stored procedure added by Microsoft Operations Manager (MOM) 2005.

You can reduce the need for database space at a cost to your ability to report and track long term data.

Note

When reducing the size of the reporting database, it is important to do so incrementally, in small steps; data is removed from the database at the next grooming job cycle, and removal of a large amount of data may cause the grooming job to time out.

You can also increase the retention period to lengthen your reporting capabilities. Increasing the retention period in the reporting database will lengthen the amount of time it takes to complete the transfer of data from the collection database to the reporting database (the DTS transaction). If it is necessary to retain Client Security data longer than 395 days, after increasing the data retention period, monitor the duration of the DTS transaction.

Important

Increasing the data retention period can adversely affect the performance of the SQL Server computer hosting the reporting database. You should not increase the data retention period unless that server has sufficient resources. For information about the system requirements for Client Security, see System requirements for Client Security.

For information about the DTS transaction performance and database sizing, see Database sizing in the Client Security Performance and Scalability Guide (https://go.microsoft.com/fwlink/?LinkId=89813).

For information about changing the data retention period for the reporting database, see Knowledge Base article 887016 (https://go.microsoft.com/fwlink/?LinkId=86925).

Autogrow

Depending on the retention settings and number of clients, enabling autogrow is supported for the following databases and transaction logs:

  • OnePoint transaction log

  • System Center Reporting database

  • System Center Reporting transaction log

Autogrow is not supported for the OnePoint database.

SQL Server tempdb database

The tempdb database is a SQL Server system database that is used for storage of temporary data structures during database maintenance tasks and queries. This database is used by all SQL Server databases on a server, and it is re-created dynamically each time you restart the SQL Server service. The tempdb database is configured to automatically grow to the maximum amount of available disk space, and its log file is configured to automatically grow to a maximum of 2 GB.

The tempdb database is used heavily during the DTS transaction that transfers data from the collection database to the reporting database. If there is not sufficient room for the tempdb database to automatically grow during this transaction, the DTS job will fail.