Tuning Client Security database components

  • Article

Applies To: Forefront Client Security

Client Security uses SQL Server for the collection and reporting databases. When you are choosing the SQL Server edition and the hardware on which it will run, take the following into consideration.

For more information about tuning SQL Server performance, see Monitoring and Tuning for Performance (https://go.microsoft.com/fwlink/?LinkID=85444) and Storage Top 10 Best Practices (https://go.microsoft.com/fwlink/?LinkId=85451).

SQL Server editions

Client Security supports both SQL Server 2005 Standard Edition and SQL Server 2005 Enterprise Edition.

The relevant difference between these editions is that SQL Server 2005 Enterprise Edition supports more data. SQL Sever 2005 Enterprise Edition has the ability to increase performance on very large databases by implementing table and index partitioning.

For a comparison of the editions, see SQL Server 2005 Features Comparison (https://go.microsoft.com/fwlink/?LinkId=85452).

For more information about partitioning, see Partitioned Tables and Indexes in SQL Server 2005 (https://go.microsoft.com/fwlink/?LinkId=85453).

Client Security supports SQL Server Standard Edition for up to 3,000 managed computers. To manage more than 3,000 client computers, Client Security requires the partitioning capabilities of Enterprise Edition.

SQL Server performance considerations

Because Client Security substantially relies on SQL Server, it is important to follow recommendations for SQL Server performance tuning.

Disk considerations

To maximize read and write performance and reduce contention between database transactions and accessing the system files, it is a common recommendation that SQL Server data files and transaction log files be placed on separate disk spindles. For Client Security, it is recommended you do the same; when installing SQL Server, specify the default locations for the data and log files on the server. Client Security Setup uses these paths to install the collection and reporting databases. If necessary, the default value of the SQL Server databases and log files can be changed after the installation of SQL Server.

To change the default location for SQL Server databases and log files

  1. Start Microsoft SQL Server Management Studio.

  2. In Object Explorer, right-click SERVERNAME, and then click Properties.

  3. In the Server Properties dialog box, under Select a page, click Database Settings.

  4. Under Database default locations, enter the paths for the data and log locations, and then click OK.

Another factor in disk performance is the rate at which data can be transferred to and from the disk. It is recommended that you install the data files for the SQL Server databases on fast disk types, such as small computer system interface (SCSI) or Serial Advanced Technology Attachment (SATA), as opposed to a slow disk type, such as integrated device electronics (IDE). If possible, a redundant array of independent disks (RAID) is recommended for this purpose. RAID configurations offer not only performance gains but also data redundancy.

The SQL Server transaction logs are used to store data transactions before they are written to the database files. Transaction log files are typically write-only; they are only read in the event a recovery is necessary. For this reason, RAID configurations that optimize writes are preferred; transaction logs are best placed on RAID 1 disk arrays.

Optimally, data files for the SQL Server databases should be stored on a disk array that provides both redundancy and speed on both read and write operations. Preferably, this would be a RAID 10 configuration.

For performance reasons, it is also recommended that you place the tempdb data file and log files on separate disk resources from the other databases. For more information, see Storage Top 10 Best Practices (https://go.microsoft.com/fwlink/?LinkId=87536).

For more information about SQL Server 2005 disk configuration, see Appendix A of Physical Database Storage Design (https://go.microsoft.com/fwlink/?LinkId=85457).

MOM 2005 and SQL Server

Client Security uses MOM 2005 for the collection database (OnePoint database) and the reporting (SystemCenterReporting) database. These databases are managed by SQL Server. When data is transferred from the collection database to the reporting database, data that is moved is stored temporarily in the SQL Server system database tempdb.

The tempdb database is a SQL Server system database that is used globally by all SQL Server databases on a single server. It is used as the temporary storage location for database operations such as temporary tables, work tables, and indexes. The tempdb database needs to be large enough to hold the data transferred during the MOM DTS job from the collection database to the reporting database. By default, tempdb is set to autogrow to the maximum of the available disk space; its log is configured to autogrow to 2 GB in size.

To enable the DTS data transfer to be successful, you need to verify that tempdb is located on a disk that has adequate free space for its growth during these transactions. If tempdb does not have adequate disk space to house the temporary data from the DTS transactions, the DTS will fail.

If necessary, tempdb can be relocated to another disk resource. For more information about moving tempdb, see Moving System Databases (https://go.microsoft.com/fwlink/?LinkId=87540).

For more information about MOM and its use of tempdb, see Results for Reporting Server (https://go.microsoft.com/fwlink/?LinkId=88044).

OnePoint-Reindex SQL Server job

The OnePoint-Reindex SQL Server job, a standard MOM database maintenance job, runs every Sunday at 03:00 (3:00 A.M). It requires that the collection database (OnePoint database) maintain 40 percent free space or reindexing will fail. Reindexing the collection database causes the disk that the collection database resides on to be heavily used. This may cause alert and event latency, because these alerts and events cannot be written into the database during the reindex procedure. This job typically lasts for 30 minutes.

For more information, see Default Database Jobs (https://go.microsoft.com/fwlink/?LinkId=87244).

Report delivery

When a Client Security administrator requests a report from the reporting server by clicking the report name in the Client Security console, a request is sent to the reporting server. The result of this request is the report is delivered to the management server and the report is displayed to the Client Security administrator.

The amount of time it takes to generate Client Security reports is based primarily on the configuration of the reporting server and the amount of data that needs to be analyzed for the particular report. For example, in testing, the Security Summary report took approximately 1.5–2 minutes to display with 72 hours of data from 10,000 Client Security agents. On a smaller scale with approximately 2,500 Client Security agents, the Security Summary report took less than a minute to display.

Historical reports beyond four days need to analyze data from the reporting (SystemCenterReporting) database. The more data that needs to be analyzed, the longer the report takes to display.