Database sizing
Applies To: Forefront Client Security
Optimally, you should address initial database-sizing concerns prior to the installation of Client Security. Databases can be resized after the installation, if necessary.
Client Security uses four SQL Server databases:
SQL Server 2005 Reporting Services Report database
Collection database (or OnePoint database)
Reporting database (or System Center Reporting database)
(Optionally) WSUS database
After you have installed Client Security, decided on the deployment method for the Client Security agents, and deployed the Client Security agents, you will begin to accumulate data in the collection database and eventually in the reporting database. You should set the appropriate size for these databases based on the number of Client Security agents you will be deploying and the amount of time you want to keep the data in the reporting database.
The following table describes the values for the default sizing of these databases.
| Database | Default data size | Default log size | Autogrow |
|---|---|---|---|
Collection (OnePoint) |
15 gigabytes (GB) data |
20% of data |
Not supported |
Reporting (System Center Reporting) |
1 GB data |
50% of data |
Not supported |
ReportServer database |
Approximately 30 megabytes (MB) data |
Varies |
Yes |
ReportServerTempDB database |
Varies |
Varies |
Yes |
WSUS |
Varies |
1 MB |
Yes |
During server setup, you set the sizes of the collection and reporting databases. The Setup wizard sets the default log sizes. It is important to note that the Autogrow value for these two databases is set to None—Autogrow is not supported by MOM.
The collection database should be large enough to store collected data for 10 days, without filling. This allows the Data Transformation Services (DTS) procedure to run twice—data is groomed (deleted) from the collection database every four days, but not until it has been transferred to the reporting database via a DTS job. Therefore, data resides in the collection database for five days before it is groomed out of the database. Should the DTS job fail for any reason, having room for 10 days' data gives adequate backup space.
It is important to note that databases need free space to perform certain actions. The collection database (OnePoint database) needs to maintain 40 percent free space to enable maintenance jobs to complete successfully. If 40 percent free space is not maintained, maintenance jobs such as reindexing will fail. For information about approximate projected size of the collection database based on the number of managed computers, see the "Disk performance factors" section of Impact on server system resources.
The default size set by server Setup for the reporting database is 1 GB; you need to adjust this based on the number of managed computers in your organization and your desired data retention duration. For information about approximate projected size of the reporting database based on the number of managed computers and data retention values, see the "Disk performance factors" section of Impact on server system resources.
Database growth factors
The growth of the Client Security databases can be affected by a number of factors:
Number of Client Security agents deployed
Number of scans performed
Type of scan performed
Frequency of scans
Frequency of outbreak events
The following table describes the numbers of events and alerts generated for various standard situations.
| Action | Event count per Client Security agent |
|---|---|
Antimalware scan |
Two—one each for the start and completion of the scan |
Threat detected |
Two—one detection event and one action taken event for each detected threat |
Security state assessment (SSA) scan |
One |
SSA vulnerability detected |
One for each vulnerability scored medium or higher |
Definition update |
One |
Policy update |
One |
State summary |
One |
Clicking the Scan Now button in the Microsoft Forefront Client Security Management Console causes a total of at least four events per Client Security agent (two events for the antimalware scan, one event for the SSA scan, and one event for the definition update triggered by the Scan Now button). Additionally, each scheduled antimalware scan will cause three events by default (two events for the antimalware scan and one event for the default behavior of definition updating prior to scanning).
SSA scans have the potential to produce many more events than just the one that is triggered by the scan itself; each vulnerability scored medium or higher produces an event. A Client Security agent that is out of date with updates has the potential to cause a large number of events. Multiply this by the number of Client Security agents requiring updates, and you understand how quickly the collection and reporting databases can grow.
Alerts are raised based on the alert level configured for the managed computer via Client Security policy and the severity of the threat discovered. Each alert will be stored in the databases as well, and will cause the database to grow in size. Alerts are larger than events but occur less frequently. For more information about the relationship between alert level and threat severity, see Working with alerts in the Client Security Administrator's Guide (https://go.microsoft.com/fwlink/?LinkId=86813).
Data retention
The amount of time you keep the data in the reporting database depends on the number of Client Security agents you have deployed, the volume of events and alerts you have configured the Client Security agents to send, the amount of disk space you have available, and your tolerance for the duration of the data grooming.
You can calculate the approximate future growth of your reporting database by using the event counts provided in the preceding "Database growth factors" section and multiplying by the number of managed computers. Multiply the resulting value by the average event and alert size from the "Disk performance factors" section of Impact on server system resources.
The default value for data retention in the reporting database is 395 days. After data has been in the database for 395 days, it is deleted by a SQL Server stored procedure created by MOM. To modify the data retention value, you need to modify the parameters of the stored procedure. For more information about changing the data retention in the reporting (SystemCenterReporting) database, see Knowledge Base article 887016 (https://go.microsoft.com/fwlink/?LinkId=86925).
Note
Reducing the data retention value after data has been transferred to the reporting database causes data to be removed on the next run of the grooming job. This could cause this job to run longer than the normal duration and may cause it to time out. To prevent the grooming job from timing out due to the volume of data already in the database, it is recommended that you change the grooming value incrementally, by small steps, until the desired value is reached.
In the event that your reporting database grows too large for the disk it resides on, you can relocate it using the procedures outlined in Knowledge Base article 224071 (https://go.microsoft.com/fwlink/?LinkId=87257). The minimum required SQL Server 2005 version required for Client Security is SQL Server 2005 Service Pack 1 (SP1); there is a known issue with detaching and reattaching SQL Server 2005 Service Pack 1 databases. Knowledge Base article 922804 (https://go.microsoft.com/fwlink/?LinkId=87279) describes the problem and the fix for the issue.
DTS transactions
Data retention affects both database size and performance of the collection and reporting servers. Data is moved from the collection database (OnePoint database) to the reporting database (System Center Reporting) for long term storage via a SQL Server DTS job. This job runs at 01:00 (1:00 A.M.) every day and is more than a simple copy; to store the data for long term trend analysis, the data is read out of the collection database and inserted into the reporting database, record by record. The DTS job will take increasing amounts of time the more historical data you accumulate in the reporting database.
In general, with 2,000 managed computers, the DTS transaction with 395 days of data in the reporting database should take approximately two hours. With 5,000 managed computers, the DTS transaction should take approximately three hours, and with 10,000 managed computers, the DTS transaction should take from four to six hours.
Using SQL Server Enterprise Edition with 8 GB of RAM (or more) and the Address Windowing Extensions (AWE) option enabled can greatly increase the efficiency and speed of the DTS transaction. In testing, enabling the AWE option reduced the DTS transaction duration with 10,000 managed computers and 395 days of data by almost 50 percent. For more information about SQL Server Enterprise Edition and the AWE option, see Enabling AWE Memory for SQL Server (https://go.microsoft.com/fwlink/?LinkId=86918).
Important
AWE is not supported in SQL Server 2005 Standard Edition.
Important
Windows Server 2003 Standard Edition supports a maximum of 4 GB of RAM. To take full advantage of the AWE option in SQL Server 2005 with Client Security and more than 4 GB of RAM, you need to have Windows Server 2003 Enterprise Edition or Windows Server 2003 Datacenter Edition.
DTS transaction duration is heavily influenced by the hardware on which SQL Server is running. The following table offers some hardware guidance regarding data retention values and number of managed computers.
The values in the following table are only for the server housing the reporting database; it is highly recommended that you deploy the Client Security components in a supported topology other than single server when managing more than 2,500 client computers.
| Managed computers | SQL Server edition | Processor | Memory (in GB) | Grooming value |
|---|---|---|---|---|
Up to 1,000 |
Standard or Enterprise |
Single dual core |
1 |
395 days |
1,000–2,000 |
Standard or Enterprise |
Single dual core |
2 |
395 days |
2,000–5,000 |
Enterprise |
Two dual core |
4 |
395 days |
5,000–10,000 |
Enterprise |
Two dual core |
4 |
180 days |
5,000–10,000 |
Enterprise |
Two dual core |
8 |
395 days |
Note
For 5,000–10,000 managed computers with only 4 GB of memory on the computer running SQL Server, the grooming value is set at 180 days due to the limited memory available for use by SQL Server. This set of data is provided to illustrate the value of increasing the amount of memory available for use by SQL Server by enabling the AWE option.
Memory tuning
In addition to the SQL Server AWE option described in the previous section, with larger numbers of managed computers, the computers running SQL Server used by Client Security may benefit from a memory tuning option available to Windows Server 2003. The /3GB switch is an option added to the Boot.ini file of computers running Windows Server 2003. It changes the way virtual memory is allocated between the kernel-mode processes and the user-mode processes running on the server. Normally, Windows uses a 4 GB virtual address space, regardless of the amount of RAM on the system. Of this, 2 GB is allocated to kernel-mode processes, and 2 GB is allocated to user-mode processes. Using the /3GB switch changes this allocation, allocating 1 GB to kernel-mode processes and 3 GB to user-mode processes. For more information about the /3GB switch, see How to Set the /3GB Startup Switch in Windows (https://go.microsoft.com/fwlink/?LinkId=87335).