Database maintenance is a vital part of any application upkeep. For clients who host their own Softrip instance, maintenance is left up to their IT or DBA team.
This document describes Softrip's standard recommendations for database maintenance. These are the processes in place for Softrip's own hosting environment.
Note that these are Softrip recommendations that may or may not fit your own data retention policies and practices; feel free to adjust them as needed.
Regular Database Maintenance
Indexes
Softrip recommends rebuilding all table indexes once per week, generally on the weekend at night (for example 09:00 PM on Saturdays).
This can be done with a standard SQL maintenance task "Rebuild Indexes".
Statistics
Softrip recommends recalculating tables statistics once per week, generally on the weekend at night (for example 09:00 PM on Saturdays).
This can be done with a standard SQL maintenance task "Recalculate Statistics".
Database Backups
Transaction Logs
Softrip recommends backing up and truncating database transaction logs once every hour.
Full Backups
Softrip recommends running a full database backup once a day, during off-peak hours (e.g. at night).
These full backups should be available locally on the database server for 7 days. Keeping 7 days of full database backups can help quickly restore deleted data in case a user removes or changes records by mistake.
Once a day, after the local database backup, daily full backups should be pushed to offsite long-term storage (e.g. S3 or similar).
Differential Backups
Softrip recommends running differential database backups every hour. These differential backups should be available locally on the database server for 7 days. These differential backups are not typically pushed to offsite long-term storage.
Sample Maintenance Schedule
The Softrip standard database maintenance schedule is as follows:
Weekly - Sat - 21:00
Rebuild Indexes
Recalculate Statistics
Daily - 00:00
Full database backup
Delete local backup files older than 7 days
Daily - 01:00
Push local full database backups to long-term storage
Every 01:00 - Starting at 12:30 and ending at 23:30
Database differential backup
This schedule starts at 12:30 and ends at 23:30 to avoid having a differential backup run at the same time as a full backup
Every 01:00
Backup and truncate transaction logs