Microsoft SQL Server Database Maintenance

If you are at a company that is running Ivanti products in a full SQL Server environment and have a database administrator on staff with SQL Server maintenance and backup policies already running against our databases, great! If you are running SQL Server Express or a full edition of SQL Server but don’t have a maintenance and backup plan in place, please keep reading.

Database instability and corruption is the single biggest cause of an upgrade issue that is encountered and the root cause of many GUI performance issues that can be mitigated and, in many cases, resolved by proactive maintenance on the database. Below are our recommendations for good regular maintenance on your database so you keep it running slim and clean for good performance and to reduce issues.

Keep in mind this is a starting point. If you have regulatory needs that require more data kept live you should adjust to keep more data live. If that is the case you may want to analyze how frequently you are scanning. 1000 agents scanning eight times a day will grow your database at a much more rapid rate than once per day or once per week. And in most cases, you don’t really need all of that data.

The following are our recommendations for regular database maintenance.

Data Retention

Determine the amount of data that needs be kept on hand for operational purposes. Typically 60-90 days is acceptable for operational purposes. Use the Ivanti Security Controls Database Maintenance tool to cleanup anything older than that number of days and schedule the task to run on a regular basis.

Reporting

Determine what report data is required for audit\regulatory requirements. Run monthly reports to fulfill these needs and keep the reports on file as far back as your policy requires. Typically, 13 months is an acceptable amount of time to save report data.

Database Backups

Ivanti recommends running weekly incremental and monthly full backups. The backup should be run just before your scheduled purge. Keep backups as far back as the reporting data.

Database Maintenance Schedule

Backups: Full monthly, just after patch maintenance for that month. Incremental weekly, end of each week (after weekend patch windows preferably).
Purge Data: After the full monthly backup is run
Reindex: After purge data is run
Integrity: After reindex is run

Database Maintenance Tool

SQL Server Express users who do not have access to the SQL Server Maintenance Plan Wizard can use the Ivanti Security Controls Database Maintenance tool. The Database Maintenance tool enables you to:

Delete old results
Rebuild your SQL Server indexes
Create backup copies of your database and your transaction log

You do this by selecting Manage > Database maintenance and then specifying exactly when and how your database maintenance tasks should be performed. For more information, see the Database Maintenance help topic.

SQL Server Maintenance Plan Wizard

If you are using a full edition of SQL Server, you should use the SQL Server Maintenance Plan Wizard to setup your maintenance plans. You could use the Ivanti Security Controls Database Maintenance tool, but the SQL Server Maintenance Plan Wizard is more robust and provides additional functionality.