SQL Server database maintenance strategies

You can maintain data integrity and improve performance by considering the following items when you configure the database and log files.

  1. Pre-allocating the space that is required for database files and log files can improve performance. These options are available on the Data Files tab and the Transaction Log tab of the Database Properties window in SQL Server Enterprise Manager.
  2. Allowing the log files to grow automatically (by 10%) is required to ensure that unexpected errors do not occur.
  3. Placing the data files and log files on separate physical disk drives can improve performance substantially. Make sure that these physical disk drives have enough free space to allow for database growth.

SQL server database backup and maintenance

Like any enterprise application, the database must be backed up regularly, and other database maintenance tasks must be conducted from time to time. Microsoft™ SQL Server Management Studio provides a Maintenance Plan wizard that allows these tasks to be automated. Use this wizard to create the required scheduled tasks.

Note: Backing up the database is different from copying the database file and saving it in another location. Use the Backup feature in Microsoft™ SQL Server Management Studio to back up the SQL Server database, and consult its documentation for instructions.

Ensure matching database and SQL server collation

When you upgrade to a newer AppScan Enterprise version or backup the database to move it to another SQL Server, ensure that the collation (such as case sensitivity) matches between the two. Otherwise, the AppScan Enterprise database won't work properly.

Backup strategy

Because the database log files can grow in size between SQL Server backups, back up the database daily. Depending on the frequency with which activities (such as report pack and dashboard generation, import jobs) are run, it might be possible to do incremental backups frequently and full SQL Server backups less frequently. It is not necessary to conduct backups while the database is quiet, but backup operations can be scheduled for times when the database is known to be less busy. If your organization employs a regular maintenance window for servers, then this time might be an ideal time to conduct the SQL Server backup.

For large organizations where the database is never, or rarely, quiet, consider using commercial backup software that is configured to back up SQL incrementally.

Database recovery

If there is a catastrophic hardware failure, the database can be restored from the last SQL Server backup by using the 'Restore database' command in Microsoft™ SQL Server Management Studio.

Shrinking the database

Database growth can become an issue, especially after large content scan jobs are deleted. The 'Shrink Database' command can be used to remove the empty space. The database is most effectively shrunk at the "File" level. Choose "Files" from the "Shrink Database" window.

Alternatively, use the Database Maintenance wizard to periodically shrink a database.

Database maintenance

After the application is installed, a database maintenance plan must be established. Use the 'Maintenance Plan wizard' to create the plan and schedule it. In the wizard, select these options:

  • Check database integrity
  • Shrink the database
  • Reorganize the index
  • Update statistics
  • Do a full backup of the database

Disk defragmentation

Disk fragmentation occurs over time as files are created, deleted, and change in size. Consider using the Windows™ tools to periodically defragment disks when the database is not being used and can be taken down for maintenance.

Maintenance plan to reorganize the index

Index fragmentation can cause slow database performance because of many page splits. This leads to high post processing times, report packs taking longer to generate, and slower web application performance.

The rebuild operation cannot be run while users are accessing the database. For this reason, it is necessary to stop users from accessing the database during the rebuild.

Here are some possible solutions:
  • For SQL Standard, a rebuild of the indexes is necessary, for this to occur the indexes must be taken offline.
  • Upgrade to SQL Enterprise, allowing for index rebuilds while keeping the index online.
  • Adjust the SQL server fill factor to try and reduce internal fragmentation from occurring in the first place.
Upgrading to SQL Enterprise would help administrators complete index rebuilds without stopping access to the database. Create a maintenance plan that:
  1. Stops all services;
  2. Stops IIS and related services;
  3. Kills any running Agent Host executables;
  4. Waits for all Agent Hosts to finish processing;
  5. Checks the level of index fragmentation, and logs it to a file;
  6. Rebuilds the indexes using a fill factor of 80 (20% free space per page);
  7. Checks the level of index fragmentation again, and appends it to the log file;
  8. Starts IIS and related services; and
  9. Starts all services.