Database Maintenance

In order to keep Security Controls operating at peak efficiency, it is important to perform periodic maintenance on your database. The database maintenance tool enables you to:

  • View and delete old results
  • Rebuild your SQL Server indexes
  • Create backups of your database

In this section:

You do this by selecting Manage > Database Maintenance and then specifying exactly when and how your database maintenance tasks should be performed.

Manual Maintenance

You can get a complete list of available prior scans, script executions, and patch deployments by selecting the Manual tab.

If you want to delete certain items in a list, select the desired items in the list and then click Deleted Selected. If you would like to remove all items in a list, click Delete All. Deleting an item here also deletes it from its associated list (Today's Items, Recent Items, or Archive Items) in the Results pane and permanently removes it from the database.

TIP: When deleting a large number of items it is smarter to schedule a database maintenance task. The scheduled task will be performed in the background and allow you to perform additional console tasks at the same time. Manual deletions are performed in the foreground and you must wait for the task to complete before performing additional console tasks.

Scheduled Maintenance

If the options on this dialog are unavailable it probably means that another administrator currently has control over the database maintenance operations. See the Take ownership option (below) for more details.

Field

Description

Enable weekly database maintenance

If enabled, will perform database maintenance tasks on the specified day and time. The scheduled job is managed by the Security Controls console service; the job cannot be tracked using the Scheduled Task Manager. Maintenance tasks should be performed after hours or on a weekend when database use is at a minimum.

If this check box is not enabled you can still configure the remaining database maintenance options on this dialog, but in order to run the maintenance task you must initiate it using the Run now button. The database maintenance tasks will not be performed on a regularly scheduled basis.

For each result type, choose at least one way to delete old results

There are two ways to delete old results:

  • Max results to keep: Enables you to specify the maximum number of patch scans, asset scans and script run records you want to store in the database. If the specified number is exceeded, scans will be deleted based on their age (the oldest scans are deleted first). Any patch deployments that are associated with the scans are also deleted. Valid values are 10 - 10,000 for each scan type.
  • Be careful if you are using Security Controls Agent on your machines. Agents report their results to the console and each result constitutes a scan. If you have many agents there is a chance of exceeding the threshold rather quickly. In this scenario you should consider using the Delete results older than (days) option.

  • Delete results older than (days): Enables you to specify the maximum number of days that patch results, asset results, event logs, and script run records are allowed to be stored in the database before being deleted. Any patch deployments that are associated with the scans are also deleted. Valid values are 1 - 10,000 days. As a general rule, results that are over 90 days old should be considered too old to accurately depict the current state of your organization.

If you choose to implement both methods for a result type, the method that deletes the least number of results is the one that will be used.

Example: Assume that for patch results you specify Max results to keep = 100 and Delete results older than (days) = 90. Also assume that there are 150 patch results currently stored in the database but only 10 of them have been there for more than 90 days. When the database maintenance task is run the oldest 10 results will be deleted; the 140 results that are less than 90 days old will be left alone.

About the Different Result Types

Each result type consists of the following:

  • Patch: Patch scans and any associated patch deployments
  • Asset: Asset scans
  • ITScripts: Script run records
  • Event history: Log entries for operational events such as database maintenance and synchronization activities
  • Hypervisor patch: ESXi hypervisor scans and bulletin deployments

Delete machines older than (days)

Enables you to specify the maximum number of days that inactive machines are allowed to be stored in the database before being deleted. An inactive machine can be a machine that has not checked in with the console, been assessed or been included in a patch deployment for the specified number of days. Valid values are 45 - 10,000 inactive days. The default value is 90. As a general rule, machine results that are over 90 days old should be considered too old to accurately depict the current state of your organization.

Another method for deleting inactive machines is to use the REST API. For details, see: Machines in the API help (opens a new window).

Security Controls automatically frees up one license seat for any machine that has been inactive for more than 45 days, but no machines are automatically deleted from the database unless specified here.

Rebuild indexes

If enabled, each time the database maintenance task is performed it will instruct SQL Server to rebuild the database indexes after the old result data are removed. Doing so will improve the performance of your database. This is particularly valuable when deleting large amounts of data.

This option will work on any of the supported editions of SQL Server but it is best suited for use with SQL Server Express editions. If you are using a full edition of SQL Server you might consider using the SQL Server Maintenance Wizard because it provides more control and functionality.

Backup database and transaction log

If enabled, each time the database maintenance task is performed it will instruct SQL Server to create backup copies of the database and the transaction log before removing any data.

You must specify where the backup files will be written. You can use either a UNC path (for example: \\server\backup) or a local path (for example: c:\backup) to specify the backup location. The recommendation is to use a UNC path format that specifies a location on a different machine than the one currently running SQL Server. The path name you specify here is simply passed along for use during the backup. No validation is performed on the name.

Notes:

  • If you are using a remote SQL Server and you specify a local path, the path you are specifying is located on the remote SQL Server and NOT on the console machine.
  • If you specify a UNC path to a location on SQL Server, your SQL Server account must have access to the path. If a built-in account is being used (such as Local System or Network Service) then the machine account needs access to the path.

Take ownership

This button is only displayed if you have two or more consoles that share one database.

If your organization uses multiple Security Controls consoles that share the same database, only one console will be authorized to use the Database Maintenance tool. If an administrator at another console wants to perform maintenance on the database, that administrator must take ownership of the task before the program will allow the administrator to continue. Any existing maintenance tasks will be allowed to complete before ownership is transferred to another administrator.

Run now

Immediately initiates the database maintenance task. The task is run in the background and requires no user intervention. The task is performed using the current configuration. The current configuration is saved for future use, and if the Enable weekly database maintenance check box is enabled this will also schedule the database maintenance task.

You can use the Event History log to track the progress of the maintenance task. In addition, after the task completes there should be fewer items in the Results list and in the Database Maintenance list. If you have access to SQL Server Management Studio you can also use its Database Properties feature to track the progress of the task.

Save scheduled changes

Saves the current database maintenance configuration. If the Enable weekly database maintenance check box is enabled this will also schedule the database maintenance task.

AC Events Maintenance

Select Enable daily maintenance of AC Events to run a scheduled maintenance job on the Application Control events in the database.

Time of daily run - specify the time you want the job to run each day.

Max events to keep - specify the maximum number of events to keep on the database, anything exceeding this number will be deleted when the maintenance job is run.

Max days to keep events - specify the maximum number of day to keep events on the database, any events older than the number of days specified will be deleted when the maintenance job is run.

Run now - select to run an ad hoc maintenance job on the database immediately.

Once all parameters have been set select Save scheduled changes.

It is recommended to use daily maintenance if central event logging is turned on. See Event Options for further details.