White Papers

BP1014 Enhancing SQL Server Protection using Dell EqualLogic Snapshot Smart Copies

2 SQL Server backup considerations

A copy of data that can be used to restore and recover the data is called a backup. Backups let you

restore data after a failure. Microsoft® SQL Server® enables you to back up and restore your

databases. The SQL Server® native backup and restore utility provides an important safeguard for

protecting critical data stored in SQL Server® databases. A well-planned backup and restore strategy

helps protect databases against data loss or corruption caused by a variety of failures and meets

organizations Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO). With good

backups, you can recover from many different kinds of failures, such as:

 User/administrator errors such as dropping a table by mistake.

 Hardware failures such as a storage controller failure, damaged disk drives or permanent loss

of a server.

 Software problems such as driver or firmware bugs that cause SQL data corruption.

 Natural disasters, etc.

As a best practice, you should regularly test your backups and check if your backup/restore strategy

meets your RPO and RTO. It is also important to periodically reevaluate your business SLAs to validate

your backup strategy.

The following factors can impact achievable RPO and RTO goals in a SQL Server® backup strategy:

Backup Window The time during which the backup is created. SQL Server® databases are

generally backed up on a daily basis. Backups are typically scheduled during

lean periods to minimize impact of backup on database performance and time

to complete the backup. As we’ll see later in this paper, backup processing

increases CPU utilization and storage I/O on production servers. By keeping

the length of the backup window (time it takes to create the backup) as short

as possible you minimize the processing impact on production servers.

Backup Type and Frequency The frequency and type of backup will impact how far or close

to the failure point you can restore to (RPO) and the time it’ll take to bring the

database online (RTO). To minimize performance impact of database backups

on the production system and increase the frequency or granularity of

backups, database administrators typically use either differential or more

frequent Transaction Log backups (incremental) to recover SQL databases to

specific points in time. To restore using transaction log backups, you need to

restore the last full and differential database backup (if any) and then apply all

transaction logs up to desired recovery point.

Backup Media The type of media, such as disk or tape that used for storing backup data sets.

The type of media used to backup data will impact how fast you can create

and restore from backups. Restoring from a disk based backup target is

typically much faster than from a tape based target.