Database Maintenance

Database backup and restoration policies are essential to ensure data retention and database resilience, including recovery from:

• Data loss

• Accidental deletion

• Corrupt data (for example: from viruses)

• Infrastructure issues (for example: failed discs)

• System outages (for example: power failure)

• Disaster recovery (for example: fire, flooding and so on)

Your backup strategy should include a regular backup schedule that is implemented out of hours to minimise the impact on database performance. Backups must be encrypted to ensure data protection and stored off-site in the event of an outage at your primary site.

Note: Your database backup policy must meet your Recovery Point Object (RPO), Recovery Time Objective (RTO) and Recovery Level Objectives (RLO) requirements.

Database backup and restoration policies ensure that you can:

• Access database backups (for example: off-site tape rotation or cloud access and so on)

• Restore your database from your backup media

• Ensure that you are retaining the relevant data

• Test your end-to-end recovery process

Index management must be performed on a regular basis to ensure that your database is optimised for performance.

Index management tasks include:

• Index rebuilding

• Reorganisation

• Statistical updates

Back to top

Database Integrity checks must be performed to identify database corruption in:

• System databases

• User databases

Back to top

Database monitoring and alerting must be used to ensure that hardware and software issues are reported in a timely manner.

Hardware monitoring includes:

• Server shut down or reboot

• Disk capacity

• Disk failures

• Memory failures

• Memory usage

Software monitoring includes:

• Database error codes (for example: 823, 824, 825 and so on)

• Expensive or performance degrading queries

• Deadlocks

• Long duration locks or long running jobs

• Missing indexes

• Cluster software failures or fail-overs

Back to top

A regular update (patching) process is essential to ensure that your applications (for example: SQL Server) and infrastructure benefit from the latest security updates, bug fixes and new features. Updates must be performed out of hours to prevent system downtime.

Back to top