Every day in the IT field people are thrown to being the accidental DBA. While this is a mistake for most companies to make it happens, especially with the change in the healthcare field and the adoption of Electronic Medical Record (EMR) software. I wanted to make sure to get some knowledge out there on the subject of how to maintain a database and avoid a Resume Producing Event (RPE).
Before getting started on the maintenance it is important to understand and discuss about your Recovery point objectives(RPO) and recovery time objectives(RTO).
Core Maintenance Tasks – SQL server Jobs
Backups – One of the key things to remember is that backups should be ideally performed to a local disk for performance AND then copied to another location for storage. Far to often these are found on the same disk in my experience and is terrible idea for disaster recovery.
Recommended (mission critical)
- Full database backup 1 week
- Nightly differential
- Transaction logs every 15 minutes
Remember FULL recovery mode for mission critical databases where data loss is not acceptable as this allows for point-in-time recovery and log backups. For more information feel free to check out the Previous Post Here.
Consistency Checks – It is usually assumed that SQL server does consistency checks and identifies Database corruption immediately, but in reality, SQL server does not notice the corruption until the corrupt page is accessed from disk again. This could be weeks or even months away if you are unlucky and at that point you will likely not have the backups prior to the corruption.
Recommended – (Based on maintenance windows)
- Run at least one time a week
Please note to remove the dependency for a maintenance window it is possible restore a database to another server and run a system integrity check on the database from there. If corruption is found, you would have to identify if it is your backup or if it is in your live/production server. This process would then have to call a maintenance window in some circumstances.
Index Maintenance – Rebuilding your indexes is a key part of maintaining performance in a database. This can generate a high amount of log information and high amount of I/Os on the environment as all the tables are updated with a full table scan.
- Run one time a week
As a note to this, There is an option to “Reorganize” indexes, this should NOT be scheduled at the same time of the Rebuild. This task reorganizes the indexes but does not include an update of the statistics. In order to complete the same thing as a rebuild you would also need to run a update statistics task. Still, reorganizing indexes is a fully online operation and can be a good interim step between regular executions of the rebuild index task.
- Run one time a week – Halfway between your executions of the Rebuild.
For additional information, I would always recommend looking into the series from SQLskills.com Accedental DBA.