Skip to content

SQL Server Database Maintenance

Welcome to the Steel Ridge Partners database maintenance documentation! In this document, we will go over the most relevant database maintenance procedures for SQL Server 2019. This document does not cover database security.

NOTE: These documents are a work in progress, expect them to change often!

Contents:

SRP Database Maintenance Standards

Recovery Model

A recovery model is a database property that controls how transactions are logged, whther the transaction log requires backing up, and what restore options are available. The two most important considerations when discussing recovery models are RPO and RTO. RPO is Recovery Point Objective which is the amount of data loss that is acceptable in the event of a disaster. RTO is Recovery Time Objective which is the amount of time a system can be down during a disaster. Each database has its own unique RPO and RTO standards. Be sure to discuss and confirm the specific RPO and RTO standards before creating maintenance plans.

Recovery Model Considerations Description
Recovery Point Objective (RPO) The maximum amount of data - as measured by time - that can be lost after recovery from a disaster before data loss will exceed what is acceptable to an organization
Recovery Time Objective (RTO) The overall length of time an information system's components can be in the recovery phase before egatively impacting the organization's mission or business process

Backup Scheduling

Backup Scheduling is another maintenance standard that must be discussed and documented before creating maintenance plans. Backup scheduling standards involves the time of day of the backup and the frequency of the backup. The time of day of the backups must be specified to ensure that the databases are not highly active during the backup. The frequency of the backup depends on the type of backup and how sensitive the databases are.

Backup Scheduling Considerations Description
Time of Day The time of day of the backup is when the backup job will run. Database insight must be known to backup the database without error. A database facing employee would be wise to run the backups during the least active time in the day to ensure as much relevant data is backed up as possible.
Frequency The frequency of the backups should be discussed with SRP managers. Insight of the databases being backed up are also essential to creating good frequency standards. Also note that different types of backups require different frequencies and any database facing employee should keep this in consideration

Backup Location

Backup Location is a standard that specifies where the backups should be stored. Having a good backup location in place will assist the accessibility for restores, the backup speed, and tolerance for hardware failure. The most common types of backup locations are on a Local Drive, File share/UNC, or Cloud Storage. Discuss with SRP managers which backup location is the most reasonable.

Types of Backup Locations
Local Drive
File Share/UNC
Cloud Storage

Backup Retention

Backup Retention Considerations are another important maintenance standard that must be considered. The amount of days that a backup needs to be restored must be specified and any backups past this point can be properly archived. Legal and SLA considerations must also be considered when dealing with backup retention standards.

Backup Retention Considerations Description
Restore Speed The amount of days that a backup should be saved and stored must be discussed. If a backup is past the amount of days specified, the backup should be archived to save time and storage in the restoration process
Legal / SLA Database facing employees must be aware of any laws regarding the backups for SRP activities. Database facing employees should also be aware of any Service Level Agreements established with clients to ensure that databases conform
Hot, Warm, or Cold? This considers how accessible the backup are. A local drive or server that holds the backups would be considered hot. A file share with a fast network would be considered warm. A Cloud storage vendor such as Azure or AWS would be considered cold storage.

Database Backup Timelines

Backing up a database is the most important maintenance task that can be completed to ensure data security, reliability, and integrity. There are three different backups that are usually performed; log backups, differential backups, and full backups. Log backups occur many times per day (usually twice an hour depending on how busy a database is) and they backup the individual transactions that are not already backed up to save the most accurate copy of the database. Thus if a disaster occurs, only about 30 minutes of data will be unrecoverable. Differential backups occur once a day at the end of the day and these backups cover the entire days transactions that are not stored in a full backup. Full backups occur usually once a week and these backups save the entire database even if it is already backed up or not. Full backups usually take the longest to perform and require the most amount of storage as they encompass all of the data in a database. These backup timelines are general and are subject to change based on the database uses and needs. It is important to consider the RPO and RTO of any specific database before creating backup schedules.

Which type of backup to use should be discussed and agreed upon with SRP managers!